Python 使用 SQLAlchemy 用 Pandas 写入 MySQL 数据库，to_sql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30631325/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Writing to MySQL database with pandas using SQLAlchemy, to_sql
提问by AsAP_Sherb
trying to write pandas dataframe to MySQL table using to_sql. Previously been using flavor='mysql', however it will be depreciated in the future and wanted to start the transition to using SQLAlchemy engine.
尝试使用to_sql. 以前一直在使用flavor='mysql'，但是将来会折旧，并希望开始过渡到使用 SQLAlchemy 引擎。
sample code:
示例代码：
import pandas as pd
import mysql.connector
from sqlalchemy import create_engine
engine = create_engine('mysql+mysqlconnector://[user]:[pass]@[host]:[port]/[schema]', echo=False)
cnx = engine.raw_connection()
data = pd.read_sql('SELECT * FROM sample_table', cnx)
data.to_sql(name='sample_table2', con=cnx, if_exists = 'append', index=False)
The read works fine but the to_sqlhas an error:
读取工作正常，但to_sql有一个错误：
DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': Wrong number of arguments during string formatting
DatabaseError: sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;'上的执行失败：字符串格式化期间的参数数量错误
Why does it look like it is trying to use sqlite? What is the correct use of a sqlalchemy connection with mysql and specifically mysql.connector?
为什么它看起来像是在尝试使用 sqlite？sqlalchemy 与 mysql 的连接的正确用法是什么，特别是 mysql.connector？
I also tried passing the engine in as the connection as well, and that gave me an error referencing no cursor object.
我也尝试将引擎作为连接传入，这给了我一个错误，引用了没有游标对象。
data.to_sql(name='sample_table2', con=engine, if_exists = 'append', index=False)
>>AttributeError: 'Engine' object has no attribute 'cursor'
采纳答案by AsAP_Sherb
Using the engine in place of the raw_connection()worked:
使用引擎代替raw_connection()工作：
import pandas as pd
import mysql.connector
from sqlalchemy import create_engine
engine = create_engine('mysql+mysqlconnector://[user]:[pass]@[host]:[port]/[schema]', echo=False)
data.to_sql(name='sample_table2', con=engine, if_exists = 'append', index=False)
Not clear on why when I tried this yesterday it gave me the earlier error.
不清楚为什么当我昨天尝试这个时它给了我早期的错误。
回答by openwonk
Alternatively, use pymysqlpackage...
或者，使用pymysql包...
import pymysql
from sqlalchemy import create_engine
cnx = create_engine('mysql+pymysql://[user]:[pass]@[host]:[port]/[schema]', echo=False)
data = pd.read_sql('SELECT * FROM sample_table', cnx)
data.to_sql(name='sample_table2', con=cnx, if_exists = 'append', index=False)
回答by Rafael Valero
I know in the title of the question is included the word SQLAlchemy, however I see in the questions and answers the need to import pymysql or mysql.connector, and also is possible to do the job with pymysql, withouth calling SQLAlchemy.
我知道问题的标题中包含 SQLAlchemy 一词，但是我在问题和答案中看到需要导入 pymysql 或 mysql.connector，并且也可以使用 pymysql 完成这项工作，而无需调用 SQLAlchemy。
import pymysql
user = 'root'
passw = 'my-secret-pw-for-mysql-12ud' # In previous posts variable "pass"
host = '172.17.0.2'
port = 3306
database = 'sample_table' # In previous posts similar to "schema"
conn = pymysql.connect(host=host,
port=port,
user=user,
passwd=passw,
db=database)
data.to_sql(name=database, con=conn, if_exists = 'append', index=False, flavor = 'mysql')
I think this solution could be good althought it is not using SQLAlchemy.
我认为这个解决方案可能很好，尽管它没有使用 SQLAlchemy。
回答by DougR
Using pymysql and sqlalchemy, this works for Pandas v0.22:
使用 pymysql 和 sqlalchemy，这适用于 Pandas v0.22：
import pandas as pd
import pymysql
from sqlalchemy import create_engine
user = 'yourUserName'
passw = 'password'
host = 'hostName' # either localhost or ip e.g. '172.17.0.2' or hostname address
port = 3306
database = 'dataBaseName'
mydb = create_engine('mysql+pymysql://' + user + ':' + passw + '@' + host + ':' + str(port) + '/' + database , echo=False)
directory = r'directoryLocation' # path of csv file
csvFileName = 'something.csv'
df = pd.read_csv(os.path.join(directory, csvFileName ))
df.to_sql(name=csvFileName[:-4], con=mydb, if_exists = 'replace', index=False)
"""
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.
"""
