Как установить первичный ключ при записи фрейма данных pandas в таблицу базы данных sqlite с помощью df.to_sql

Ответ 1

К сожалению, сейчас нет способа установить первичный ключ в методе pandas df.to_sql(). Кроме того, просто для того, чтобы больше болеть, нет способа установить первичный ключ в столбце в sqlite после создания таблицы.

Однако работа на данный момент заключается в создании таблицы в sqlite с помощью метода pandas df.to_sql(). Затем вы можете создать дубликатную таблицу и задать свой первичный ключ, а затем скопировать данные. Затем отпустите старый стол, чтобы очистить его.

Это будет что-то вроде этого.

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, а UPDATE JOIN вызвал много разочарования и некоторые нетрадиционные обходные пути.

Наконец, в методе pandas df.to_sql() существует аргумент ключевого слова dtype, который может принимать словарь имен столбцов: types. IE: dtype = {col_1: TEXT}

Ответ 2

Основываясь на Крисе Гуарино, ответьте на некоторые функции, которые обеспечивают более общее решение. См. Пример внизу для того, как их использовать.

import re

def get_create_table_string(tablename, connection):
    sql = """
    select * from sqlite_master where name = "{}" and type = "table"
    """.format(tablename) 
    result = connection.execute(sql)

    create_table_string = result.fetchmany()[0][4]
    return create_table_string

def add_pk_to_create_table_string(create_table_string, colname):
    regex = "(\n.+{}[^,]+)(,)".format(colname)
    return re.sub(regex, "\\1 PRIMARY KEY,",  create_table_string, count=1)

def add_pk_to_sqlite_table(tablename, index_column, connection):
    cts = get_create_table_string(tablename, connection)
    cts = add_pk_to_create_table_string(cts, index_column)
    template = """
    BEGIN TRANSACTION;
        ALTER TABLE {tablename} RENAME TO {tablename}_old_;

        {cts};

        INSERT INTO {tablename} SELECT * FROM {tablename}_old_;

        DROP TABLE {tablename}_old_;

    COMMIT TRANSACTION;
    """

    create_and_drop_sql = template.format(tablename = tablename, cts = cts)
    connection.executescript(create_and_drop_sql)

# Example:

# import pandas as pd 
# import sqlite3

# df = pd.DataFrame({"a": [1,2,3], "b": [2,3,4]})
# con = sqlite3.connect("deleteme.db")
# df.to_sql("df", con, if_exists="replace")

# add_pk_to_sqlite_table("df", "index", con)
# r = con.execute("select sql from sqlite_master where name = 'df' and type = 'table'")
# print(r.fetchone()[0])

Существует суть этого кода здесь

Ответ 3

В Sqlite с обычной таблицей rowid, если первичный ключ не является единственным столбцом INTEGER (см. ROWIDs и INTEGER PRIMARY KEY в документации), он эквивалентен индексу UNIQUE (поскольку реальный PK нормальной таблицы - это rowid),

Примечания из документации для таблиц rowid:

PRIMARY KEY таблицы rowid (если она есть) обычно не является истинным первичным ключом таблицы, в том смысле, что он не является уникальным ключом, используемым базовым механизмом хранения B-дерева. Исключением из этого правила является случай, когда таблица rowid объявляет INTEGER PRIMARY KEY. В исключении INTEGER PRIMARY KEY становится псевдонимом для rowid.

Истинный первичный ключ для таблицы rowid (значение, которое используется в качестве ключа для поиска строк в базовом механизме хранения B-дерева) - это rowid.

Ограничение PRIMARY KEY для таблицы rowid (если оно не является истинным первичным ключом или INTEGER PRIMARY KEY) действительно то же самое, что и ограничение UNIQUE. Поскольку это не настоящий первичный ключ, столбцы PRIMARY KEY могут иметь значение NULL, что является нарушением всех стандартов SQL.

Таким образом, вы можете легко подделать первичный ключ после создания таблицы с помощью:

CREATE UNIQUE INDEX mytable_fake_pk ON mytable(pk_column)

Помимо значения NULL, вы не получите преимуществ от INTEGER PRIMARY KEY если предполагается, что ваш столбец содержит целые числа, например, занимает меньше места и автоматически генерирует значения при вставке, если не учитывается, но в противном случае он будет работать для большинства цели.