to_sql pandas method changes the scheme of sqlite tables
pandas, python
Solution
Starting from 0.14 (what you are using), the sql functions are refactored to use `sqlalchemy` to improve the functionality`. See the whatsnew and docs on this. The raw sqlite3 connection is still supported as a fallback (but that is the only sql flavor that is supported without sqlalchemy).
Using sqlalchemy should solve the issue. For this you can just create a sqlalchemy engine instead of the direct sqlite connection `cnx`:
engine = sqlalchemy.create_engine('sqlite:///my_db.sqlite')
df.to_sql('Resolved', engine, if_exists='append')
But I filed an issue for the case with the sqlite cnx fallback option: https://github.com/pydata/pandas/issues/7355
Problem
When I write Pandas DataFrame to my SQLite database using to_sql method it changes the `.schema` of my table even if I use `if_exists='append'`. For example after execution ``` with sqlite3.connect('my_db.sqlite') as cnx: df.to_sql('Resolved', cnx, if_exists='append') ``` original `.schema`: ``` CREATE TABLE `Resolved` ( `Name` TEXT NOT NULL COLLATE NOCASE, `Count` INTEGER NOT NULL, `Obs_Date` TEXT NOT NULL, `Bessel_year` REAL NOT NULL, `Filter` TEXT NOT NULL, `Comments` TEXT COLLATE NOCASE ); ``` changes to: ``` CREATE TABLE Resolved ( [Name] TEXT, [Count] INTEGER, [Obs_Date] TEXT, [Bessel_year] REAL, [Filter] TEXT, [Comments] TEXT ); ``` How to save the original scheme of my table? I use pandas 0.14.0, Python 2.7.5