To use dtype http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html,传递一个键控到每个数据框列的字典以及相应的sqlalchemy 类型 https://docs.sqlalchemy.org/en/13/core/types.html。将键更改为实际数据框列名称:
import sqlalchemy
import pandas as pd
...
column_errors.to_sql('load_errors',push_conn,
if_exists = 'append',
index = False,
dtype={'datefld': sqlalchemy.DateTime(),
'intfld': sqlalchemy.types.INTEGER(),
'strfld': sqlalchemy.types.NVARCHAR(length=255)
'floatfld': sqlalchemy.types.Float(precision=3, asdecimal=True)
'booleanfld': sqlalchemy.types.Boolean})
您甚至可以动态创建这个dtype
假设您事先不知道列名称或类型的字典:
def sqlcol(dfparam):
dtypedict = {}
for i,j in zip(dfparam.columns, dfparam.dtypes):
if "object" in str(j):
dtypedict.update({i: sqlalchemy.types.NVARCHAR(length=255)})
if "datetime" in str(j):
dtypedict.update({i: sqlalchemy.types.DateTime()})
if "float" in str(j):
dtypedict.update({i: sqlalchemy.types.Float(precision=3, asdecimal=True)})
if "int" in str(j):
dtypedict.update({i: sqlalchemy.types.INT()})
return dtypedict
outputdict = sqlcol(df)
column_errors.to_sql('load_errors',
push_conn,
if_exists = 'append',
index = False,
dtype = outputdict)