to_sql pandas method changes the scheme of sqlite tables

Question

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

Answer 1

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