to_sql pandas方法更改sqlite表的方案 [英] to_sql pandas method changes the scheme of sqlite tables

问题描述

当我使用 to_sql 方法，即使我使用if_exists='append'，它也会更改表的.schema.例如执行后

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')

原始.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
);

更改为:

CREATE TABLE Resolved (
                  [Name] TEXT,
  [Count] INTEGER,
  [Obs_Date] TEXT,
  [Bessel_year] REAL,
  [Filter] TEXT,
  [Comments] TEXT

                  );

如何保存表格的原始方案?我使用pandas 0.14.0，Python 2.7.5

How to save the original scheme of my table? I use pandas 0.14.0, Python 2.7.5

推荐答案

从0.14(您使用的是什么)开始，将sql函数重构为使用sqlalchemy来改善功能.请参见新内容和

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).

使用sqlalchemy应该可以解决此问题.为此，您可以只创建一个sqlalchemy引擎，而不是直接的sqlite连接cnx:

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')

但是我使用sqlite cnx后备选项提出了一个问题: https://github .com/pydata/pandas/issues/7355

But I filed an issue for the case with the sqlite cnx fallback option: https://github.com/pydata/pandas/issues/7355

这篇关于to_sql pandas方法更改sqlite表的方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！