具有参数化数据类型的 Pandas to_sql，如 NUMERIC(10,2)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27766283/
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 to_sql with parameterized data types like NUMERIC(10,2)
提问by Idan Gazit
Pandas has a lovely to_sqlmethod for writing dataframes to any RDBMS supported by SQLAlchemy.
Pandas 有一种可爱的to_sql方法可以将数据帧写入 SQLAlchemy 支持的任何 RDBMS。
Say I have a dataframe generated thusly:
假设我有一个这样生成的数据框：
df = pd.DataFrame([-1.04, 0.70, 0.11, -0.43, 1.0], columns=['value'])
If I try to write it to the database without any special behavior, I get a column type of double precision:
如果我尝试在没有任何特殊行为的情况下将其写入数据库，则会得到一个双精度列类型：
df.to_sql('foo_test', an_engine)
If I wanted a different datatype, I could specify it (this works fine):
如果我想要不同的数据类型，我可以指定它（这很好用）：
df.to_sql('foo_test', an_engine, dtype={'value': sqlalchemy.types.NUMERIC})
But if I want to set the precision and scale of the NUMERICcolumn, it blows up in my face:
但是如果我想设置列的精度和比例NUMERIC，它会在我面前炸开：
df.to_sql('foo_test', an_engine, dtype={'value': sqlalchemy.types.NUMERIC(10,2)})
---------------------------------------------------------------------------
TypeError Traceback (most recent call last)
<ipython-input-77-dc008463fbfc> in <module>()
1 df = pd.DataFrame([-1.04, 0.70, 0.11, -0.43, 1.0], columns=['value'])
----> 2 df.to_sql('foo_test', cosd_engine, dtype={'value': sqlalchemy.types.NUMERIC(10,2)})
/Users/igazit/.virtualenvs/myproject/lib/python2.7/site-packages/pandas/core/generic.pyc in to_sql(self, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
964 self, name, con, flavor=flavor, schema=schema, if_exists=if_exists,
965 index=index, index_label=index_label, chunksize=chunksize,
--> 966 dtype=dtype)
967
968 def to_pickle(self, path):
/Users/igazit/.virtualenvs/myproject/lib/python2.7/site-packages/pandas/io/sql.pyc in to_sql(frame, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
536 pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
537 index_label=index_label, schema=schema,
--> 538 chunksize=chunksize, dtype=dtype)
539
540
/Users/igazit/.virtualenvs/myproject/lib/python2.7/site-packages/pandas/io/sql.pyc in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype)
1162 import sqlalchemy.sql.type_api as type_api
1163 for col, my_type in dtype.items():
-> 1164 if not issubclass(my_type, type_api.TypeEngine):
1165 raise ValueError('The type of %s is not a SQLAlchemy '
1166 'type ' % col)
TypeError: issubclass() arg 1 must be a class
I'm trying to dig into why the type for sqlalchemy.types.NUMERICpasses the test on 1164, while sqlalchemy.types.NUMERIC(10,2)doesn't. They do have different types (sqlalchemy.sql.visitors.VisitableTypevs sqlalchemy.sql.sqltypes.NUMERIC).
我试图深入研究为什么 for 类型sqlalchemy.types.NUMERIC通过了 1164 的测试，而sqlalchemy.types.NUMERIC(10,2)没有。他们确实有不同的类型（sqlalchemy.sql.visitors.VisitableTypevs sqlalchemy.sql.sqltypes.NUMERIC）。
Any clues would be much appreciated!
任何线索将不胜感激！
回答by Bob Haffner
Update: this bug is fixed for pandas >= 0.16.0
更新：此错误已针对 Pandas >= 0.16.0 修复
This is post about a recent pandas bug with the same error with 0.15.2.
这是关于最近的一个Pandas错误的帖子，与 0.15.2 有相同的错误。
https://github.com/pydata/pandas/issues/9083
https://github.com/pydata/pandas/issues/9083
A Collaborator suggests a to_sql monkey patch as a way to solve it
协作者建议使用 to_sql 猴子补丁作为解决此问题的方法
from pandas.io.sql import SQLTable
def to_sql(self, frame, name, if_exists='fail', index=True,
index_label=None, schema=None, chunksize=None, dtype=None):
"""
patched version of https://github.com/pydata/pandas/blob/v0.15.2/pandas/io/sql.py#L1129
"""
if dtype is not None:
from sqlalchemy.types import to_instance, TypeEngine
for col, my_type in dtype.items():
if not isinstance(to_instance(my_type), TypeEngine):
raise ValueError('The type of %s is not a SQLAlchemy '
'type ' % col)
table = SQLTable(name, self, frame=frame, index=index,
if_exists=if_exists, index_label=index_label,
schema=schema, dtype=dtype)
table.create()
table.insert(chunksize)
# check for potentially case sensitivity issues (GH7815)
if name not in self.engine.table_names(schema=schema or self.meta.schema):
warnings.warn("The provided table name '{0}' is not found exactly "
"as such in the database after writing the table, "
"possibly due to case sensitivity issues. Consider "
"using lower case table names.".format(name), UserWarning)
pd.io.sql.SQLDatabase.to_sql = to_sql
