python - pandas to_sql 截断了我的数据

Question

我正在使用 df.to_sql(con=con_mysql, name='testdata', if_exists='replace', flavor='mysql') 将数据框导出到 mysql。但是，我发现长字符串内容(例如url)的列被截断为63位。导出时，我从 ipython notebook 收到以下警告:

/usr/local/lib/python2.7/site-packages/pandas/io/sql.py:248: Warning: Data truncated for column 'url' at row 3 cur.executemany(insert_query, data)

对于不同的行，还有其他相同样式的警告。

有什么我可以调整以正确导出完整数据的吗？我可以在 mysql 中设置正确的数据模式，然后导出到它。但我希望一个调整可以让它直接从 python 运行。

Answer 1

如果您使用的是 pandas 0.13.1 或更早版本，这个 63 位的限制确实是硬编码的，因为代码中的这一行:https://github.com/pydata/pandas/blob/v0.13.1/pandas/io/sql.py#L278

作为变通方法，您可以对函数 get_sqltype 进行 monkeypatch:

from pandas.io import sql

def get_sqltype(pytype, flavor):
    sqltype = {'mysql': 'VARCHAR (63)',    # <-- change this value to something sufficient higher
               'sqlite': 'TEXT'}

    if issubclass(pytype, np.floating):
        sqltype['mysql'] = 'FLOAT'
        sqltype['sqlite'] = 'REAL'
    if issubclass(pytype, np.integer):
        sqltype['mysql'] = 'BIGINT'
        sqltype['sqlite'] = 'INTEGER'
    if issubclass(pytype, np.datetime64) or pytype is datetime:
        sqltype['mysql'] = 'DATETIME'
        sqltype['sqlite'] = 'TIMESTAMP'
    if pytype is datetime.date:
        sqltype['mysql'] = 'DATE'
        sqltype['sqlite'] = 'TIMESTAMP'
    if issubclass(pytype, np.bool_):
        sqltype['sqlite'] = 'INTEGER'

    return sqltype[flavor]

sql.get_sqltype = get_sqltype

然后只需使用您的代码即可:

df.to_sql(con=con_mysql, name='testdata', if_exists='replace', flavor='mysql')

---

从pandas 0.14开始，sql模块底层使用了sqlalchemy，字符串转换为sqlalchemy TEXT类型，再转换为mysql TEXT 类型(而不是 VARCHAR)，这也将允许您存储大于 63 位的字符串:

engine = sqlalchemy.create_engine('mysql://scott:tiger@localhost/foo')
df.to_sql('testdata', engine, if_exists='replace')

仅当您仍然使用 DBAPI 连接而不是 sqlalchemy 引擎时，问题仍然存在，但此选项已弃用，建议为 to_sql 提供 sqlalchemy 引擎。