Python pandas to_sql 'append'

Question

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')

I have also tried using:

from sqlalchemy import create_engine

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?

Thanks!

Solution

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')

Comments

[j riot]

Thank you. Also @joris thank you. I updated pandas 'sudo pip install --upgrade pandas', between both of these fixes, everything worked. However, I believe the main fix was updating pandas as I tried combinations of connecting to MySQL with no avail.

[joris]

It is good possible that updating pandas fixed your issue, but I should note that it is however recommended to use the sqlalchemy in any case, as using MySQL connection object directly is deprecated and will be removed in a future pandas version.