文章目录
一、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_data