英文:
Insert panda's timestamps without miliseconds into SQLite
问题
I pull data from an Oracle database into a DataFrame with pd.read_sql_query. The resulting columns are of type Timestamp and contain only date+time. I then insert them with DataFrame.to_sql and sqlalchemy into an SQLite database and this step adds miliseconds when serialized to SQLite's TEXT column that I'd like to get rid of.
Is there an easy way to instruct sqlalchemy or pandas' to_sql to serialize only date+time without adding 0 milliseconds?
英文:
I pull data from an Oracle database into a DataFrame with pd.read_sql_query. The resulting columns are of type Timestamp and contain only date+time. I then insert them with DataFrame.to_sql and sqlarchemy into an SQLite database and this step adds miliseconds when serialized to SQLite's TEXT column that I'd like to get rid of.
Is there an easy way to instruct sqlalchemy or panda's to_sql to serialize only date+time without adding 0 miliseconds?
答案1
得分: 1
我已经弄清楚了！您需要使用to_sql的dtype参数，并使用适当的方言类型指定每个列的格式：
from sqlalchemy.dialects.sqlite import DATETIME
dtype={
"DATETIME_COLUMN": DATETIME(storage_format="%(year)04d/%(month)02d/%(day)02d %(hour)02d:%(minute)02d:%(second)02d")
}
data.to_sql("table_name", session.connection(), if_exists="append", index=False, dtype=dtype)
其中 session: sqlalchemy.orm.Session
https://docs.sqlalchemy.org/en/20/dialects/sqlite.html#sqlalchemy.dialects.sqlite.DATETIME
相似的问题与相反的问题有关Pandas: 写入数据到 MySQL 时毫秒丢失 帮助我找到了正确的解决方法。
英文:
I've figured it out! You need to use to_sql's dtype parameter and specify the format for each column by using the appropriate dialect type:
from sqlalchemy.dialects.sqlite import DATETIME
dtype={
"DATETIME_COLUMN": DATETIME(storage_format="%(year)04d/%(month)02d/%(day)02d %(hour)02d:%(minute)02d:%(second)02d")
}
data.to_sql("table_name", session.connection(), if_exists="append", index=False, dtype=dtype)
where session: sqlalchemy.orm.Session
https://docs.sqlalchemy.org/en/20/dialects/sqlite.html#sqlalchemy.dialects.sqlite.DATETIME
A similar question with the opposite issue Pandas: milliseconds dropped when writing data to MySQL put me on the right track.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论