Pandas to_sql not working with SQL Alchemy connection

Question

I'm connecting to a MySQL database with SQL alchemy using the following code.

from sqlalchemy import create_engine
import pandas as pd

query = "SELECT * FROM hello"

engine = create_engine("mysql+pymysql://root:new_pass@localhost:3306/toronto_analytics")
engine = engine.raw_connection()

df = pd.DataFrame({"bob":"hello", "joe":14}, index=[0])
df.to_sql('new_table', engine)

After connecting to the database I'm trying to create a new table with the values in my dataframe.

I connect to the database fine, but then df.to_sql throws the following error:

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1377             else:
-> 1378                 cur.execute(*args)
   1379             return cur

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pymysql/cursors.py in execute(self, query, args)
    167 
--> 168         query = self.mogrify(query, args)
    169 

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pymysql/cursors.py in mogrify(self, query, args)
    146         if args is not None:
--> 147             query = query % self._escape_args(args, conn)
    148 

TypeError: not all arguments converted during string formatting

During handling of the above exception, another exception occurred:

DatabaseError                             Traceback (most recent call last)
<ipython-input-100-03413ab65608> in <module>
      8 
      9 df = pd.DataFrame({"bob":"hedawo", "joe":14}, index=[0])
---> 10 df.to_sql('newawa', engine)

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/core/generic.py in to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype)
   2128         sql.to_sql(self, name, con, schema=schema, if_exists=if_exists,
   2129                    index=index, index_label=index_label, chunksize=chunksize,
-> 2130                    dtype=dtype)
   2131 
   2132     def to_pickle(self, path, compression='infer',

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/io/sql.py in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype)
    448     pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
    449                       index_label=index_label, schema=schema,
--> 450                       chunksize=chunksize, dtype=dtype)
    451 
    452 

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/io/sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype)
   1478                             if_exists=if_exists, index_label=index_label,
   1479                             dtype=dtype)
-> 1480         table.create()
   1481         table.insert(chunksize)
   1482 

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/io/sql.py in create(self)
    559 
    560     def create(self):
--> 561         if self.exists():
    562             if self.if_exists == 'fail':
    563                 raise ValueError("Table '%s' already exists." % self.name)

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/io/sql.py in exists(self)
    547 
    548     def exists(self):
--> 549         return self.pd_sql.has_table(self.name, self.schema)
    550 
    551     def sql_schema(self):

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/io/sql.py in has_table(self, name, schema)
   1490                  "WHERE type='table' AND name=%s;") % wld
   1491 
-> 1492         return len(self.execute(query, [name, ]).fetchall()) > 0
   1493 
   1494     def get_table(self, table_name, schema=None):

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1388             ex = DatabaseError(
   1389                 "Execution failed on sql '%s': %s" % (args[0], exc))
-> 1390             raise_with_traceback(ex)
   1391 
   1392     @staticmethod

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/compat/__init__.py in raise_with_traceback(exc, traceback)
    402         if traceback == Ellipsis:
    403             _, _, traceback = sys.exc_info()
--> 404         raise exc.with_traceback(traceback)
    405 else:
    406     # this version of raise is a syntax error in Python 3

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1376                 cur.execute(*args, **kwargs)
   1377             else:
-> 1378                 cur.execute(*args)
   1379             return cur
   1380         except Exception as exc:

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pymysql/cursors.py in execute(self, query, args)
    166             pass
    167 
--> 168         query = self.mogrify(query, args)
    169 
    170         result = self._query(query)

/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pymysql/cursors.py in mogrify(self, query, args)
    145 
    146         if args is not None:
--> 147             query = query % self._escape_args(args, conn)
    148 
    149         return query

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during string formatting

Any help with this error would be much appreciated. Thank you

try using flavor='mysql' df.to_sql(name='new_table', con=engine, flavor ='mysql', if_exists = 'append', index=False) — user2510479
– user2510479, Commented Mar 2, 2019 at 0:45
@user2510479, sorry what? Flavor isn't a parameter in pd.to_sql am I missing something? — Matt
– Matt, Commented Mar 2, 2019 at 0:46
Output: TypeError: to_sql() got an unexpected keyword argument 'flavor' — Matt
– Matt, Commented Mar 2, 2019 at 0:47
pandas version 0.23.4 and just upgraded to 0.24.1, same errors persisting — Matt
– Matt, Commented Mar 2, 2019 at 0:51

Ilja Everilä · Accepted Answer · 2019-03-02 07:11:35Z

4

Pass the engine itself, not a DB-API connection (the raw connection). Pandas supports only SQLite, if using DB-API directly:

con : sqlalchemy.engine.Engine or sqlite3.Connection Using SQLAlchemy makes it possible to use any DB supported by that library. Legacy support is provided for sqlite3.Connection objects.

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html

edited Mar 2, 2019 at 7:11

answered Mar 2, 2019 at 6:54

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Matt Over a year ago

Ah, great, thank you, I removed the raw connection line and it worked. One follow up question: it now seems to work the first time I send the command but if I re-run the query again the connection hangs. Do you know why this might be?

Matt Over a year ago

Update: it seems that if I write df.to_sql, then query that table, then try to overwrite that table with df.to_sql again, it will hang

Ilja Everilä Over a year ago

Could sound like some locking issue, but with this information it is a bit hard to say.

Matt Over a year ago

Yes, that would make sense. Regardless, it's an issue I can work around. Thank you for your help.

Collectives™ on Stack Overflow

Pandas to_sql not working with SQL Alchemy connection

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related