Pandas DataFrame.to_sql() error - not all arguments converted during string formatting
Parameters:
con : SQLAlchemy engine or DBAPI2 connection (legacy mode)
Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported.
flavor : ‘sqlite’, default None
Deprecated since version 0.19.0: ‘sqlite’ is the only supported option if SQLAlchemy is not used.
It will be fine if you use SQLAlchemy instead of MySQLdb.
Yogesh Yadav
Updated on July 04, 2022Comments
-
Yogesh Yadav almost 2 years
Python Version - 2.7.6
Pandas Version - 0.17.1
MySQLdb Version - 1.2.5
DataFrame.to_sql()
is throwingpandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during string formatting
Python Code Snippet
con = MySQLdb.connect('localhost', 'root', '', 'product_feed') cur = con.cursor() cur.execute("SELECT VERSION()") connection_result = cur.fetchall() print connection_result[0][0] #It prints 5.5.44-0ubuntu0.14.04.1 table_column = ['A', 'B', 'C'] created_data = numpy.array([numpy.arange(10)]*3).T df = pandas.DataFrame(data=created_data ,columns=table_column) df.to_sql('test_table', con)
The error comes at the execution of
df.to_sql('test_table', con)
line.Error Details
File "/home/yogi/yogi/mlPython/product_feed/etl_pf/process_data.py", line 298, in render_df df.to_sql('test_table', con) File "/home/yogi/yogi/mlenv/local/lib/python2.7/site-packages/pandas/core/generic.py", line 1003, in to_sql dtype=dtype) File "/home/yogi/yogi/mlenv/local/lib/python2.7/site-packages/pandas/io/sql.py", line 569, in to_sql chunksize=chunksize, dtype=dtype) File "/home/yogi/yogi/mlenv/local/lib/python2.7/site-packages/pandas/io/sql.py", line 1640, in to_sql table.create() File "/home/yogi/yogi/mlenv/local/lib/python2.7/site-packages/pandas/io/sql.py", line 685, in create if self.exists(): File "/home/yogi/yogi/mlenv/local/lib/python2.7/site-packages/pandas/io/sql.py", line 673, in exists return self.pd_sql.has_table(self.name, self.schema) File "/home/yogi/yogi/mlenv/local/lib/python2.7/site-packages/pandas/io/sql.py", line 1653, in has_table return len(self.execute(query, [name,]).fetchall()) > 0 File "/home/yogi/yogi/mlenv/local/lib/python2.7/site-packages/pandas/io/sql.py", line 1554, in execute raise_with_traceback(ex) File "/home/yogi/yogi/mlenv/local/lib/python2.7/site-packages/pandas/io/sql.py", line 1543, in execute cur.execute(*args) File "/home/yogi/yogi/mlenv/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 187, in execute query = query % tuple([db.literal(item) for item in args]) pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during string formatting
It would be great help if someone could suggest some work around. I do not want to try this with
cursor.execute()