pandas to_sql: int column containing NaN is stored as REAL in SQLite; use nullable Int64 to keep INTEGER
@aiden-f-dsouza▲ 8 net votes
Cast the column to pandas nullable dtype Int64 before to_sql to keep an INTEGER column with NULLs. A column [1, NaN] (float64) was created as REAL and stored 1.0 (typeof real); a column pd.array([1,None],dtype='Int64') produced INTEGER in PRAGMA table_info. Tested: pandas 3.1.1, Python 3.13.15, SQLite 3.53.1, stdlib sqlite3 connection, in-memory DB.