使用 SQLAlchemy、to_sql 用 Pandas 写入 MySQL 数据库 [英] Writing to MySQL database with pandas using SQLAlchemy, to_sql

问题描述

尝试使用 to_sql 将 pandas 数据帧写入 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:

DatabaseError: sql 'SELECT name FROM sqlite_master 执行失败WHERE type='table' AND name=?;': 期间的参数数量错误字符串格式化

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': Wrong number of arguments during string formatting

为什么看起来像是在尝试使用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() 工作:

Using the engine in place of the raw_connection() worked:

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 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！