Python pandas使用to_sql进行追加操作。

Question

我正在尝试使用Python的pandas to_sql命令将每月数据发送到MySQL数据库。 我的程序每次运行一个月的数据，我想将新数据附加到现有数据库中。 然而，Python给了我一个错误：

_mysql_exceptions.OperationalError: (1050, "Table 'cps_basic_tabulation' already exists")

这是我连接和导出的代码：

conn = MySQLdb.connect(host     = config.get('db', 'host'),
                       user     = config.get('db', 'user'),
                       passwd   = config.get('db', 'password'),
                       db       = 'cps_raw') 

combined.to_sql(name            = "cps_raw.cps_basic_tabulation",
               con              = conn,
               flavor           = 'mysql', 
               if_exists        = 'append')

我也尝试使用以下方法：

from sqlalchemy import create_engine

将 conn = MySQLdb.connect... 替换为：

engine = mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

conn   = engine.connect().connection

有什么想法，为什么我不能向数据库追加内容？

谢谢！

- j riot

---

你正在使用哪个版本的pandas？ - joris

你尝试过传递 con=engine 而不是 con=conn 吗？ - unutbu

Answer 1

从pandas 0.14开始，您需要直接提供sqlalchemy engine，而不是连接对象：

engine = create_engine("mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>")
combined.to_sql("cps_raw.cps_basic_tabulation", engine, if_exists='append')

- joris

---

谢谢。还有@joris，谢谢你。我更新了pandas 'sudo pip install --upgrade pandas'，在这两个修复之间，一切都正常工作了。然而，我相信主要的修复是更新pandas，因为我尝试了连接到MySQL的各种组合，但都没有成功。 - j riot

更新pandas可能解决了您的问题，但是需要注意的是，在任何情况下都建议使用sqlalchemy，因为直接使用MySQL连接对象已被弃用，并将在未来的pandas版本中删除。 - joris

Answer 2

由于我遇到了相同的错误信息并偶然发现了这篇文章，所以我在此留下它，让其他人也能找到。

我找到了两种解决重复表创建的方法，尽管我不知道为什么这样做可以解决它：

1. 在创建连接时通过url传递数据库名称
2. 或者在 pd.to_sql 中将数据库名称作为模式传递。

两者都做不会有影响。另外，几年后（再次？）可以将纯连接传递给pandas。我猜测，在joris之前的答案中，我的解决方案中的第一个可能已经隐含地解决了问题。

```
#create connection to MySQL DB via sqlalchemy & pymysql
user = credentials['user']
password = credentials['password']
port = credentials['port']
host = credentials['hostname']
dialect = 'mysql'
driver = 'pymysql'
db_name = 'test_db'

# setup SQLAlchemy   
from sqlalchemy import create_engine 
cnx = f'{dialect}+{driver}://{user}:{password}@{host}:{port}/' 
engine = create_engine(cnx) 

# create database
with engine.begin() as con:
    con.execute(f"CREATE DATABASE {db_name}")

############################################################
# either pass the db_name  vvvv - HERE- vvvv after creating a database
cnx = f'{dialect}+{driver}://{user}:{password}@{host}:{port}/{db_name}'      
############################################################
engine = create_engine(cnx) 

table = 'test_table'
col = 'test_col'
with engine.begin() as con:
    # this would work here instead of creating a new engine with a new link
    # con.execute(f"USE {db_name}")
    con.execute(f"CREATE TABLE {table} ({col} CHAR(1));")

# insert into database
import pandas as pd
df = pd.DataFrame({col : ['a','b','c']})

with engine.begin() as con:
    # this has no effect here
    # con.execute(f"USE {db_name}")
    df.to_sql(
        name= table,
        if_exists='append',
# passing con = cnx here would equally work
        con=con, 
############################################################
# or pass it as a schema vvvv - HERE - vvvv
        #schema=db_name,
############################################################
        index=False
    )```

已测试通过 Python 版本 3.8.13，SQLAlchemy 版本 1.4.32 和 Pandas 版本 1.4.2。 相同的问题可能已经出现在 这里 和 这里。

- circle-ish