SQLAlchemy MySQL df.to_sql() creates 32 bit int MySQL columns for int64 df cols

[davidljung]

Using SQLAlchemy engine for MySQL connection and DataFrame.to_sql(), MySQLdb throws a DataError as a result of 64 bit integer out-of-range as a 32bit integer was created for the table schema.

engine = create_engine('mysql://mysql@localhost/db')
df = pd.DataFrame(data={'i64':2**62},index=[1])
df.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 1 entries, 1 to 1
Data columns (total 1 columns):
i64    1 non-null int64
dtypes: int64(1)

df.to_sql('itest',engine,index=False)

yields:

DataError: (DataError) (1264, "Out of range value for column 'i64' at row 1") 'INSERT INTO itest (i64) VALUES (%s)' (4611686018427387904L,) 

and inspection of the MySQL table schema shows the i64 column has MySQL datatype int(11), which is only 32bits wide. It should be a bigint instead.

[davidljung]

I notice in pandas/io/sql.py line 721 it notes # TODO: Refine integer size. (in class PandasSQLTable._sqlalchemy_type() )

[jorisvandenbossche]

Relevant code is here: https://github.com/pydata/pandas/blob/v0.14.0/pandas/io/sql.py#L709, so we use the Integer sqlalchemy type. Should this be BigInteger?

[jorisvandenbossche]

Ah, yes, you found the relevant code. I suppose for mysql, the standard integer type is only 32 bit? (http://dev.mysql.com/doc/refman/5.0/en/integer-types.html) so we always should use bigint as we use int64 as the standard int in pandas?

Do you want to do a PR to change this and add a test?

@mangecoeur did you put in that comment?