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屋!