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

Answer 1

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