如何通过mysqldb将pandas数据框插入到数据库中?
我可以从 python 连接到我的本地 mysql 数据库，我可以创建、选择和插入单个行.
I can connect to my local mysql database from python, and I can create, select from, and insert individual rows.
我的问题是:我可以直接指示 mysqldb 获取整个数据帧并将其插入现有表中，还是需要遍历行?
My question is: can I directly instruct mysqldb to take an entire dataframe and insert it into an existing table, or do I need to iterate over the rows?
在这两种情况下，对于具有 ID 和两个数据列以及匹配数据框的非常简单的表，python 脚本会是什么样子?
In either case, what would the python script look like for a very simple table with ID and two data columns, and a matching dataframe?
推荐答案
更新:
现在有一个 to_sql 方法，这是执行此操作的首选方法，而不是 write_frame:
df.to_sql(con=con, name='table_name_for_df', if_exists='replace', flavor='mysql')
另请注意:pandas 0.14 中的语法可能会发生变化...
您可以设置与MySQLdb的连接:
from pandas.io import sql
import MySQLdb
con = MySQLdb.connect() # may need to add some other options to connect
将write_frame的flavor设置为'mysql'表示可以写入mysql:
Setting the flavor of write_frame to 'mysql' means you can write to mysql:
sql.write_frame(df, con=con, name='table_name_for_df',
if_exists='replace', flavor='mysql')
参数if_exists告诉pandas如果表已经存在如何处理:
The argument if_exists tells pandas how to deal if the table already exists:
if_exists: {'fail', 'replace', 'append'}，默认 'fail'
fail:如果表存在，则什么都不做.
replace:如果表存在，删除它，重新创建它，然后插入数据.
append:如果表存在，插入数据.不存在则创建.
if_exists: {'fail', 'replace', 'append'}, default'fail'
fail: If table exists, do nothing.
replace: If table exists, drop it, recreate it, and insert data.
append: If table exists, insert data. Create if does not exist.
虽然 write_frame docs 目前建议它只适用于 sqlite，mysql 似乎受支持，实际上有相当多的 代码库中的mysql测试.
Although the write_frame docs currently suggest it only works on sqlite, mysql appears to be supported and in fact there is quite a bit of mysql testing in the codebase.
相关文章