Pandas DataFrame.to_sql() 错误 - 并非所有参数都在字符串格式化期间转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34734058/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pandas DataFrame.to_sql() error - not all arguments converted during string formatting
提问by Yogesh Yadav
Python Version - 2.7.6
Python 版本 - 2.7.6
Pandas Version - 0.17.1
Pandas版本 - 0.17.1
MySQLdb Version - 1.2.5
MySQLdb 版本 - 1.2.5
DataFrame.to_sql()is throwing 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
DataFrame.to_sql()正在投掷 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
Python Code Snippet
Python 代码片段
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.
错误出现在行的执行中df.to_sql('test_table', con)。
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
我检查了 Pandas 0.17.1 主要使用 .format() 所以这个错误不应该因为 % 格式而出现。
It would be great help if someone could suggest some work around. I do not want to try this with cursor.execute()
如果有人可以建议一些解决方法，那将是非常有帮助的。我不想尝试这个cursor.execute()
回答by Riemann
Parameters:
参数：
con : SQLAlchemy engine or DBAPI2 connection (legacy mode)
con ：SQLAlchemy 引擎或 DBAPI2 连接（传统模式）
Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported.
使用 SQLAlchemy 可以使用该库支持的任何数据库。如果是 DBAPI2 对象，则仅支持 sqlite3。
flavor : ‘sqlite', default None
风味：'sqlite'，默认无
Deprecated since version 0.19.0: ‘sqlite' is the only supported option if SQLAlchemy is not used.
0.19.0 版后已弃用：如果不使用 SQLAlchemy，'sqlite' 是唯一受支持的选项。
It will be fine if you use SQLAlchemy instead of MySQLdb.
如果您使用 SQLAlchemy 而不是 MySQLdb，那就没问题了。
