问题描述
尝试使用to_sql
将熊猫数据帧写入MySQL表.以前一直使用flavor='mysql'
,但是将来会贬值,并希望开始过渡到使用SQLAlchemy引擎.
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.
示例代码:
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)
读取正常,但to_sql
出现错误:
The read works fine but the to_sql
has an error:
为什么看起来要使用sqlite?将sqlalchemy连接与mysql特别是mysql.connector正确使用是什么?
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?
我也尝试将引擎作为连接传递,这给了我一个错误,该错误引用了任何游标对象.
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'
推荐答案
使用引擎代替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.
这篇关于使用SQLAlchemy,to_sql用 pandas 写入MySQL数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!