我使用以下代码通过 SQL alchemy 连接到 MySQL 数据库。
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)
连接到数据库后，我尝试使用数据框中的值创建一个新表。
我可以很好地连接到数据库，但是随后df.to_sql抛出以下错误：
---------------------------------------------------------------------------
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
任何有关此错误的帮助将不胜感激。谢谢
