SQLalchemy连接数据库

1、连接命令

from sqlalchemy import create_engine
from sqlite3 import dbapi2 as sqlite
engine = create_engine('mysql+mysqldb://username:password@hostname:3306/database?charset=utf8')
engine = create_engine('mysql+pymysql://username:password@hostname:3306/database?charset=utf8')
engine = create_engine('sqlite+pysqlite:///file.db', module=sqlite)


#连接posgresql数据库
engine = create_engine('postgres://user:password@hostname/database')


#扩展
当passwd包含"@"的时候这串url就没法正常解析了
python2
from urllib import quote_plus as urlquote
from sqlalchemy.engine import create_engine
engine = create_engine('mysql+pymysql://username:%s@hostname:3306/database?charset=utf8' % urlquote('bad@pass'))


python3
from urllib import parse
from sqlalchemy.engine import create_engine
engine = create_engine('mysql+pymysql://username:%s@hostname:3306/database?charset=utf8' % parse.unquote_plus('bad@pass'))

更多详情请查看官网：https://docs.sqlalchemy.org/en/13/dialects/mysql.html

2、连接超时和断开

MySQL有一个自动的连接关闭行为，用于固定时间空闲的连接，默认为8小时。要避免出现此问题，请使用create_engine.pool_recycle选项，该选项确保如果连接在池中存在固定的秒数，则将丢弃该连接并替换为新连接

from sqlalchemy.engine import create_engine
engine = create_engine('mysql+pymysql://username:password@hostname:3306/database?charset=utf8',pool_recycle=3600)

3、处理二进制数据警告和Unicode

当试图将二进制数据传递到数据库时，MySQL 5.6、5.7及更高版本（撰写本文时不是Mariadb）现在发出警告，而当二进制数据本身对该编码无效时，字符集编码也就位

from sqlalchemy.engine import create_engine
# mysqlclient
engine = create_engine("mysql+mysqldb://username:password@hostname:3306/database?charset=utf8mb4&binary_prefix=true")

# PyMySQL
engine = create_engine("mysql+pymysql://username:password@hostname:3306/database?charset=utf8mb4&binary_prefix=true")

备注：其他MySQL驱动程序可能支持或不支持binary_prefix标志。

4、pandas批量将数据库数据保存到excel或者csv文件中

import pandas as pd
import re
from sqlalchemy import create_engine
name = '大王叫我来巡山'

engine = create_engine('mysql+mysqldb://username:password@hostname:3306/database?charset=utf8', echo=False)
data = engine.execute('select d.id,d.store_name,d.create_time,d.type,d.status,item.product_name,item.retail_price,item.sale_price,item.amount from ds_order d left join order_item item on d.id = item.order_id where d.company_name="%s";' %name)
df = pd.DataFrame(data=data,columns=['订单号','门店名称','订单创建时间','订单类型','订单状态','商品名称','商品现价','商品零售价','销售数量'])

#替换
df['订单状态']=df['订单状态'].map(lambda s:re.sub('PAID','已支付',s))
df['订单状态']=df['订单状态'].map(lambda s:re.sub('REFUND','已退款',s))

#保存
df.to_excel(f'{ name }.xls',index=False)

5、pandas批量将excel或者csv文件数据导入至数据库

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('mysql+mysqldb://username:password@hostname:3306/database?charset=utf8', echo=False)
df = pd.read_excel('大王叫我来巡山.xlsx',encoding='utf-8',sheet_name='sheet1')
pd.DataFrame(df,
             columns=['订单号','门店名称','订单创建时间','订单类型','订单状态','商品名称','商品现价','商品零售价','销售数量']
             ).to_sql('ds_new',con=engine,if_exists='append',index=False)

#if_exists 值有 append,replace,fail，默认是fail