python: pd.io.sql.to_sql的用法

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
import pandas as pd
from sqlalchemy.types import NVARCHAR, Float, Integer, SmallInteger

'''
目的：使用create_engine与DataFrame进行快速建表
'''
# mysql+mysqldb://用户名:密码@localhost:端口/数据库名?编码)
df = pd.read_csv('./XXX.csv')

def mapping_df_types(df):
    dtypedict = {}
    for i, j in zip(df.columns, df.dtypes):
        if "object" in str(j):
            dtypedict.update({i: NVARCHAR(length=255)})
        if "float" in str(j):
            dtypedict.update({i: Float(precision=2, asdecimal=True)})
        if "int" in str(j):
            dtypedict.update({i: SmallInteger()})
    return dtypedict
dtypedict = mapping_df_types(df)
conn = create_engine('mysql+mysqldb://user:password@host:port/database?charset=utf8')

try:
    pd.io.sql.to_sql(df, 'tablename', con=conn, schema='database', if_exists='append', dtype=dtypedict, chunksize=100000)
    print('done')
except Exception as e:
    print(e)

解决办法

# 在导入模块处添加语句
import pymysql
pymysql.install_as_MySQLdb()