使用df.to_sql将 pandas 数据框写入sqlite数据库表时如何设置主键 [英] how to set the primary key when writing a pandas dataframe to a sqlite database table using df.to_sql

问题描述

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

I have created a sqlite database using pandas df.to_sql however accessing it seems considerably slower than just reading in the 500mb csv file.

我需要:

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

1. set the primary key for each table using the df.to_sql method
2. tell the sqlite database what datatype each of the columns in my 3.dataframe are? - can I pass a list like [integer,integer,text,text]

代码....(格式代码按钮不起作用)

code.... (format code button not working)

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)

推荐答案

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

Unfortunately there is no way right now to set a primary key in the pandas df.to_sql() method. Additionally, just to make things more of a pain there is no way to set a primary key on a column in sqlite after a table has been created.

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

However, a work around at the moment is to create the table in sqlite with the pandas df.to_sql() method. Then you could create a duplicate table and set your primary key followed by copying your data over. Then drop your old table to clean up.

可能与此类似.

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

过去，当我遇到此问题时，我会这样做.只是将整个内容包装为一个函数，以使其更加方便...

In the past I have done this as I have faced this issue. Just wrapped the whole thing as a function to make it more convenient...

在我使用sqlite的有限经验中，我发现创建表后无法添加主键，无法执行更新插入或UPSERTS，并且UPDATE JOIN造成了很多挫败感和一些非常规的感觉解决方法.

In my limited experience with sqlite I have found that not being able to add a primary key after a table has been created, not being able to perform Update Inserts or UPSERTS, and UPDATE JOIN has caused a lot of frustration and some unconventional workarounds.

最后，在熊猫df.to_sql()方法中，有一个dtype关键字参数，该参数可以采用列名:类型的字典. IE:dtype = {col_1:TEXT}

Lastly, in the pandas df.to_sql() method there is a a dtype keyword argument that can take a dictionary of column names:types. IE: dtype = {col_1: TEXT}

这篇关于使用df.to_sql将 pandas 数据框写入sqlite数据库表时如何设置主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！