Python读写数据库的几种方式

一、SQLAlchemy driver

SQLAlchemy 通过create_engine 创建驱动

1. mysql

from sqlalchemy import create_engine

dbtype='mysql+pymysql'
dbname = 'localhost:3306/dbname'
user='user'
password='password'

#engine = create_engine("mysql+pymysql://user:password@localhost:3306/dbname?charset='utf8'")
#engine = create_engine("mysql+pymysql://user:password@localhost:3306/dbname")
engine = create_engine("{}://{}:{}@{}".format(dbtype, user, password, dbname))  #创建驱动
con = engine.connect()  # 创建连接

2. redshift

from sqlalchemy import create_engine
import pandas as pd

dbtype='postgresql'
dbname = 'hostname:port/dbname'
user='user'
password='password'

engine = create_engine("{}://{}:{}@{}".format(dbtype, user, password, dbname))  #创建驱动

# 读取数据
sql = 'select * from xxx limit 10'
data = pd.read_sql(sql, engine)

# 写入数据
data = pd.DataFrame(np.array(range(12)).reshape(3,4))
con = engine.connect()  # 创建连接
data.to_sql(name=target_table_name, con=con, if_exists="replace", index=False, chunksize=10)  # index = False 不插入索引

其他数据库

数据库	连接方式
redshift	postgresql://user:password@hostname:5439/dbname
mysql	mysql+pymysql://user:password@hostname:6001/dbname
sql server	mssql+pymssql://user:password@hostname:port/dbname

二、common driver

1. pymysql

import pymysql

db = pymysql.connect('localhost', 'user', 'password', 'dbname', charset='utf8')
cursor = db.cursor()  # 使用cursor方法 获取操作游标

cursor.execute('select version()')  # 使用execute方法 执行sql语句
data = cursor.fetchone()  # 使用fetchone()方法 获取一条数据
db.close()  # 关闭数据库连接

2. pymongo

import pymongo

db_client = pymongo.MongoClient('mongodb://localhost:27017/')
test_db = db_client['test_db']  # 切换到testdb测试数据库
sites_obj = test_db['sites']  # 切换到sites文档(表)
first_data = sites_obj.find_one()  # find_one()方法 查询集合中的一条数据
print(first_