Python Pandas to_sql'追加' [英] Python pandas to_sql 'append'

问题描述

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

I am trying to send monthly data to a MySQL database using Python's pandas to_sql command. My program runs one month of data at a time and I want to append the new data onto the existing database. However, Python gives me an error:

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

这是我用于连接和导出的代码:

Here is my code for connecting and exporting:

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 ...

Replacing conn = MySQLdb.connect... with:

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

conn   = engine.connect().connection

关于为什么我无法附加到数据库的任何想法?

Any ideas on why I cannot append to a database?

谢谢！

推荐答案

从熊猫0.14开始，您必须直接提供sqlalchemy engine，而不是连接对象:

Starting from pandas 0.14, you have to provide directly the sqlalchemy engine, and not the connection object:

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

这篇关于Python Pandas to_sql'追加'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！