cómo establecer la clave principal al escribir un dataframe pandas en una tabla de base de datos sqlite usando df.to_sql

He creado una base de datos sqlite usando pandas df.to_sql, pero acceder a ella parece considerablemente más lento que solo leer el archivo csv de 500 mb.

Necesito:

  1. establezca la clave principal para cada tabla utilizando el método df.to_sql
  2. dígale a la base de datos sqlite qué tipo de datos es cada una de las columnas en mi 3.dataframe? – ¿Puedo pasar una lista como [entero, entero, texto, texto]

código …. (botón de código de formato no funciona)

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) 

Lamentablemente, ahora no hay forma de establecer una clave principal en el método pandas df.to_sql (). Además, solo para hacer las cosas más molestas, no hay manera de establecer una clave principal en una columna en sqlite después de que se haya creado una tabla.

Sin embargo, una solución alternativa en este momento es crear la tabla en sqlite con el método pandas df.to_sql (). Luego, puede crear una tabla duplicada y configurar su clave principal, seguido de la copia de sus datos. Luego deja caer tu vieja mesa para limpiar.

Sería algo parecido a esto.

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

En el pasado he hecho esto al enfrentar este problema. Simplemente lo envolví todo como una función para hacerlo más conveniente …

En mi experiencia limitada con sqlite, descubrí que no puedo agregar una clave principal después de crear una tabla, que no puedo realizar Inserciones de actualización o UPSERTS, y UPDATE JOIN ha causado mucha frustración y algunas soluciones no convencionales.

Finalmente, en el método pandas df.to_sql () hay un argumento de palabra clave dtype que puede tomar un diccionario de nombres de columna: tipos. IE: dtype = {col_1: TEXTO}

Sobre la base de la respuesta de Chris Guarino, aquí hay algunas funciones que proporcionan una solución más general. Vea el ejemplo en la parte inferior para saber cómo usarlos.

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

Hay una esencia de este código aquí