使用 df.to_sql 将 pandas 数据帧写入 sqlite 数据库表时如何设置主键

Question

我已经使用 pandas df.to_sql 创建了一个 sqlite 数据库，但是访问它似乎比仅读取 500mb csv 文件要慢得多。

我需要：

1. 使用 df.to_sql 方法为每个表设置主键
2. 告诉 sqlite 数据库我的 3.dataframe 中每一列的数据类型是什么？ - 我可以传递一个像 [整数,整数,文本,文本] 这样的列表吗

代码….（格式代码按钮不起作用）

 if ext == ".csv":
df = pd.read_csv("/Users/data/" +filename)
columns = df.columns columns = [i.replace(' ', '_') for i in columns]

df.columns = columns
df.to_sql(name,con,flavor='sqlite',schema=None,if_exists='replace',index=True,index_label=None, chunksize=None, dtype=None)

原文由 yoshiserry 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

不幸的是，目前无法在 pandas df.to_sql() 方法中设置主键。此外，为了让事情变得更加痛苦，在创建表后无法在 sqlite 中的列上设置主键。

但是，目前的解决方法是使用 pandas df.to_sql() 方法在 sqlite 中创建表。然后你可以创建一个复制表并设置你的主键，然后复制你的数据。然后放下旧桌子进行清理。

这将是类似的事情。

 import pandas as pd
import sqlite3

df = pd.read_csv("/Users/data/" +filename)
columns = df.columns columns = [i.replace(' ', '_') for i in columns]

#write the pandas dataframe to a sqlite table
df.columns = columns
df.to_sql(name,con,flavor='sqlite',schema=None,if_exists='replace',index=True,index_label=None, chunksize=None, dtype=None)

#connect to the database
conn = sqlite3.connect('database')
c = conn.curser()

c.executescript('''
    PRAGMA foreign_keys=off;

    BEGIN TRANSACTION;
    ALTER TABLE table RENAME TO old_table;

    /*create a new table with the same column names and types while
    defining a primary key for the desired column*/
    CREATE TABLE new_table (col_1 TEXT PRIMARY KEY NOT NULL,
                            col_2 TEXT);

    INSERT INTO new_table SELECT * FROM old_table;

    DROP TABLE old_table;
    COMMIT TRANSACTION;

    PRAGMA foreign_keys=on;''')

#close out the connection
c.close()
conn.close()

过去，我遇到过这个问题时就这样做过。只是将整个东西包装成一个函数，以使其更方便……

在我使用 sqlite 的有限经验中，我发现在创建表后无法添加主键，无法执行更新插入或 UPSERTS，以及更新连接导致了很多挫折和一些非常规的解决方法。

最后，在 pandas df.to_sql() 方法中有一个 dtype 关键字参数，它可以采用列名字典：类型。即：dtype = {col_1: TEXT}

原文由 Fury 发布，翻译遵循 CC BY-SA 3.0 许可协议