使用SQLAlchemy和pandas将数据写入MySQL数据库，使用to_sql函数

Question

尝试使用to_sql将Pandas数据帧写入MySQL表格。先前一直使用flavor='mysql'，但它将在未来被弃用，因此希望开始过渡到使用SQLAlchemy引擎。

示例代码：

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有误：

DatabaseError: 执行SQL时出错'SELECT name FROM sqlite_master WHERE type='table' AND name=?;'：字符串格式化期间参数数量错误

为什么看起来它似乎要使用sqlite？如何正确使用sqlalchemy连接mysql以及特别是mysql.connector？

我还尝试将engine作为连接传递，但这给了我一个错误，引用了没有游标对象。

data.to_sql(name='sample_table2', con=engine, if_exists = 'append', index=False)
>>AttributeError: 'Engine' object has no attribute 'cursor'

- AsAP_Sherb

---

你必须传递引擎本身，而不是一个原始连接（将来可能也会支持 sqlalchemy 连接，但不是原始连接）。你能看看这是否解决了问题吗？ - joris

啊，我明白你已经尝试过了 :-) 你能展示一下在那种情况下你得到的错误吗？ - joris

在编程中使用引擎出现了一个错误：AttributeError: 'Engine' 对象没有 'cursor' 属性。 - AsAP_Sherb

你能展示一下 pd.__versions__ 的输出吗？你确定没有使用旧版的 pandas 吗？ - joris

是的，这个错误真的很奇怪。我重新启动了我的环境，并在今天早上再次进行了该过程。我使用了一个SQLAlchemy引擎，它起作用了。我对于不同之处感到困惑。 - AsAP_Sherb

Answer 1

使用引擎代替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)

昨天我尝试过这个操作，不清楚为什么会出现之前那个错误。

- AsAP_Sherb

---

很高兴现在它能正常工作了！无论如何，这是正确的方法。 - joris

顺便提一下，您可以接受自己的答案来表示问题已经解决了！ - joris

如果你在安装 mysql.connector 时遇到问题或不知道如何安装它，请参考此链接 https://dev59.com/g1wY5IYBdhLWcg3wU2bA 他们建议使用 pip install mysql-connector==2.1.4。这对我也解决了这个问题。 - cheevahagadog

Python安装没有SSL支持，一直出现错误。使用新的Anaconda安装和Python 3.7运行。 - alex

我在密码字段中有@符号，这会导致问题。我们该如何解决呢？engine = create_engine("mysql+pymysql://dbuser:DBuser@12345@xyz/table")这里的DBuser@12345是密码。但是脚本无法正确解释它。 它使用12345@xyz作为主机名，而不是xyz。 - niraj pandey

[更快速的建议] 在使用SQLAlchemy>=1.3时，在创建engine对象时，设置fast_executemany=True。参考链接 - Vishal Gupta

Answer 2

或者，使用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)

- openwonk

---

openwonk - 你能详细说明一下这段代码中的[port]/[schema]部分吗？我不太确定应该填什么。谢谢！ - elPastor

我来回答自己的问题，schema = 数据库。 - elPastor

很高兴能帮忙，@pshep123 - openwonk

虽然我在这里仍然遇到了麻烦：https://dev59.com/06Pia4cB1Zd3GeqPuSy8。不确定你是否有任何见解，谢谢。 - elPastor

我花了很长时间才找到一个像这样可行的答案！ - alpastor

Answer 3

使用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.
"""

- DougR

Answer 4

虽然问题标题中包含了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')

我认为这个解决方案可能不错，尽管它没有使用SQLAlchemy。

- Rafael Valero

---

不再允许使用flavor关键字参数。 - Steve Byrne

请将“database”重命名为“table”。这样会非常误导和错误。 - Ivailo Bardarov

一直在寻找这样的答案。 - Matt