python - Unsigned int for dataframe to_sql using sqlalchemy types -

i unable assign unsigned int type when using .to_sql() write dataframe mysql database. can use other int types, unable unsigned. small representative sample of trying looks this:

import pandas pd sqlalchemy import create_engine import sqlalchemy.types sql_types  db_engine = create_engine('mysql://db_user:db_pass@db_host:db_port/db_schema')  d = {'id': [100,101,102], 'items': [6,10,20000], 'problems': [50,72,2147483649]} # representative sample dictionary df = pd.dataframe(d).set_index('id') 

this gives:

>>> df          items    problems id 100          6          50 101         10          72 102      20000  2147483649 

i write database follows:

df.to_sql('my_table',           db_engine,           flavor='mysql',           if_exists='replace',           index_label=['id'],           dtype={'id': sql_types.smallint,                  'items': sql_types.int,                  'problems': sql_types.int} 

but happens value of problems in last row (id==102) gets truncated 2147483647 (which 2^31-1) when written db.

there no other issues in connection or when writing other standard data types, including int. could away using sql_types.bigint option instead (making maximum 2^63-1), unnecessary know values fall below 4294967296 (2^32-1), unsigned int maximum.

so question is: how can assign unsigned int field using .to_sql() approach above?

i have used sqlalchemy types here. mysql types see here. have seen question here unsigned int mysql, not using .to_sql() approach use. if can create table single .to_sql() statement, ideal.

to unsigned int, can specify in sqlalchemy constructor of integer type mysql (see the docs on mysql types of sqlalchemy):

in [23]: sqlalchemy.dialects import mysql  in [24]: mysql.integer(unsigned=true) out[24]: integer(unsigned=true) 

so can provide dtype argument in to_sql instead of more general sql_types.int:

dtype={'problems': mysql.integer(unsigned=true), ...} 

note: need @ least pandas 0.16.0 have working.