我正在使用熊猫分析json文件:https://data.cityofnewyork.us/api/api/views/kpav-sd4t/rows.json?accesstype= download
一切顺利,直到我到达尽头将信息从熊猫传输到SQL。
我放置:
df.to_sql('table', con, chunksize=20000)
但导致
---------------------------------------------------------------------------
TypeError Traceback (most recent call last)
/usr/local/lib/python3.5/dist-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
1399 else:
-> 1400 cur.execute(*args)
1401 return cur
/usr/lib/python3/dist-packages/MySQLdb/cursors.py in execute(self, query, args)
209 query = query.decode(db.unicode_literal.charset)
--> 210 query = query % args
211
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-25-a485028ed4c0> in <module>()
----> 1 df.to_sql('table', con, chunksize=20000)
/usr/local/lib/python3.5/dist-packages/pandas/core/generic.py in to_sql(self, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
1199 sql.to_sql(self, name, con, flavor=flavor, schema=schema,
1200 if_exists=if_exists, index=index, index_label=index_label,
-> 1201 chunksize=chunksize, dtype=dtype)
1202
1203 def to_pickle(self, path):
/usr/local/lib/python3.5/dist-packages/pandas/io/sql.py in to_sql(frame, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
468 pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
469 index_label=index_label, schema=schema,
--> 470 chunksize=chunksize, dtype=dtype)
471
472
/usr/local/lib/python3.5/dist-packages/pandas/io/sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype)
1499 if_exists=if_exists, index_label=index_label,
1500 dtype=dtype)
-> 1501 table.create()
1502 table.insert(chunksize)
1503
/usr/local/lib/python3.5/dist-packages/pandas/io/sql.py in create(self)
581
582 def create(self):
--> 583 if self.exists():
584 if self.if_exists == 'fail':
585 raise ValueError("Table '%s' already exists." % self.name)
/usr/local/lib/python3.5/dist-packages/pandas/io/sql.py in exists(self)
569
570 def exists(self):
--> 571 return self.pd_sql.has_table(self.name, self.schema)
572
573 def sql_schema(self):
/usr/local/lib/python3.5/dist-packages/pandas/io/sql.py in has_table(self, name, schema)
1511 "WHERE type='table' AND name=%s;") % wld
1512
-> 1513 return len(self.execute(query, [name, ]).fetchall()) > 0
1514
1515 def get_table(self, table_name, schema=None):
/usr/local/lib/python3.5/dist-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
1410 ex = DatabaseError(
1411 "Execution failed on sql '%s': %s" % (args[0], exc))
-> 1412 raise_with_traceback(ex)
1413
1414 @staticmethod
/usr/local/lib/python3.5/dist-packages/pandas/compat/__init__.py in raise_with_traceback(exc, traceback)
337 if traceback == Ellipsis:
338 _, _, traceback = sys.exc_info()
--> 339 raise exc.with_traceback(traceback)
340 else:
341 # this version of raise is a syntax error in Python 3
/usr/local/lib/python3.5/dist-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
1398 cur.execute(*args, **kwargs)
1399 else:
-> 1400 cur.execute(*args)
1401 return cur
1402 except Exception as exc:
/usr/lib/python3/dist-packages/MySQLdb/cursors.py in execute(self, query, args)
208 if not PY2 and isinstance(query, bytes):
209 query = query.decode(db.unicode_literal.charset)
--> 210 query = query % args
211
212 if isinstance(query, unicode):
DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during string formatting
我使用
连接到服务器con = mdb.connect(host = 'localhost',
user = 'root',
passwd = 'dwdstudent2015',
charset = 'utf8', use_unicode=True)
和
engine = con
我不明白为什么它不起作用
我看过其他示例,但它们没有翻译
DataFrame.to_sql
的con
参数可以是Sqlalchemy引擎或sqlite连接。
如果您使用的是mySQL(和mysqldb python适配器),则必须连接使用SQLalchemy引擎:
import sqlalchemy as SA
engine = SA.create_engine('mysql+mysqldb://{u}:{p}@{h}/{d}'.format(
u=USER, p=PASSWORD, h=HOST, d=DATABASE'))
df.to_sql('table', engine, chunksize=20000)
请注意,错误说
DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during string formatting
此SQL语句引用sqlite_master
,因为Pandas假设连接是与SQLite数据库的连接。如果熊猫将生成以Sqlite为中心的SQL通过了连接。如果通过Sqlalchemy引擎。