将pandas的DataFrame数据写入MySQL + sqlalchemy
python强大的处理数据的能力很大一部分来自Pandas，pandas不仅限于读取本地的离线文件，也可以在线读取数据库的数据，处理后再写回数据库中。pandas主要是以sqlalchemy方式与数据库建立链接，支持Mysql、postgresql、Oracle、MS SQLServer、SQLite等主流数据库。
一：创建链接数据库引擎
from sqlalchemy import create_engine
engine= create_engine('mysql+pymysql://root:password@localhost:3306/databasename?charset=utf8',echo = True)
echo = True ，会显示在加载数据库所执行的SQL语句。
pd.io.sql.to_sql(thedataframe,'tablename', yconnect, schema='databasename', if_exists='append')
to_sql中，
第一个参数thedataframe是需要导入的pd dataframe,
第二个参数tablename是将导入的数据库中的表名
第三个参数yconnect是启动数据库的接口，pd 1.9以后的版本，除了sqllite，均需要通过sqlalchemy来设置
第四个参数databasename是将导入的数据库名字
第五个参数if_exists='append'的意思是，如果表tablename存在，则将数据添加到这个表的后面
sqlalchemy.create_engine是数据库引擎
('mysql+pymysql://root:password@localhost:3306/databasename?charset=utf8')的解释
mysql是要用的数据库
pymysql是需要用的接口程序
root是数据库账户
password是数据库密码
localhost是数据库所在服务器的地址，这里是本机
3306是mysql占用的端口
databasename是数据库的名字
charset=utf8是设置数据库的编码方式，这样可以防止latin字符不识别而报错
二：读取数据库数据，存储为DataFrame格式
1：读取自定义数据（通过SQL语句）
pandas.read_sql_query(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None,chunksize=None)
例如：data = pd.read_sql_query('select * from t_line ',con = engine)，会返回一个数据库t_line表的DataFrame格式。如有有时间列可以parse_dates = [time_column]用于解析时间，并把此列作为索引index_col = [time_column]
read_sql_query（）中可以接受SQL语句，包括增删改查。但是DELETE语句不会返回值（但是会在数据库中执行），UPDATE，SELECT，等会返回结果.
例如：data = pd.read_sql_query('delete from test_cjk where f_intime = 1309',con = engine)，这条语句会执行，删除 test_cjk表中f_intime=1309的值，但不会返回data。
其他例子：
'''插入操作''' pd.read_sql_query("insert into cjk_test h values %(data)s",params={'data':v_split[11]},con = engine)
'''更新操作''' pd.read_sql_query("update cjk_test set a='粤11111' WHERE a='粤B30738'",con = engine)
'''删除操作''' pd.read_sql_query("delete from cjk_test where c='1'",con=engine)
删除插入更新操作没有返回值，程序会抛出SourceCodeCloseError，并终止程序。如果想继续运行，可以try捕捉此异常。
2：读取整张表于DataFrame格式
pandas.read_sql_table(table_name, con, schema=None, index_col=None, coerce_float=True, parse_dates=None, columns=None, chunksize=None)
例如：data = pd.read_sql_table(table_name = 't_line',con = engine,parse_dates = 'time',index_col = 'time',columns = ['a','b','c'])
3:读数据库
pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)
三：数据写入于数据库
DataFrame.to_sql(name, con, flavor='sqlite', schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None)
例如：data.to_sql('test_cjk',engine,if_exists='replace',index = False)，把数据写入test_cjk表中。
if_exists='replace'，如果数据库中有test_cjk表，则替换。
if_exists='append'，如果数据库中有test_cjk表，则在表后面添加。
if_exists='fail'，如果数据库中有test_cjk表，则在写入失败。
chunksize，如果data的数据量太大，数据库无法响应可能会报错，这时候就可以设置chunksize，比如chunksize = 1000，data就会一次1000的循环写入数据库。
网友评论