Importar un archivo CSV a una tabla de base de datos sqlite3 usando Python
Tengo un archivo CSV y quiero importarlo de forma masiva a mi base de datos sqlite3 usando Python. el comando es ".importar....". pero parece que no puede funcionar así. ¿Alguien puede darme un ejemplo de cómo hacerlo en sqlite3? Estoy usando Windows por si acaso. Gracias
import csv, sqlite3
con = sqlite3.connect(":memory:") # change to 'sqlite:///your_filename.db'
cur = con.cursor()
cur.execute("CREATE TABLE t (col1, col2);") # use your column names here
with open('data.csv','r') as fin: # `with` statement available in 2.5+
# csv.DictReader uses first line in file for column headings by default
dr = csv.DictReader(fin) # comma is default delimiter
to_db = [(i['col1'], i['col2']) for i in dr]
cur.executemany("INSERT INTO t (col1, col2) VALUES (?, ?);", to_db)
con.commit()
con.close()
La creación de una conexión sqlite a un archivo en el disco se deja como ejercicio para el lector... pero ahora hay una doble línea posible gracias a la biblioteca pandas.
df = pandas.read_csv(csvfile)
df.to_sql(table_name, conn, if_exists='append', index=False)
Tienes razón, ese .importes el camino a seguir, pero es un comando del programa de línea de comandos SQLite3. Muchas de las respuestas principales a esta pregunta involucran bucles nativos de Python, pero si sus archivos son grandes (los míos tienen entre 10^6 y 10^7 registros), debe evitar leer todo en pandas o usar una lista de comprensión/bucle nativo de Python. (aunque no los cronometré para compararlos).
Para archivos grandes, creo que la mejor opción es utilizarlos subprocess.run()para ejecutar el comando de importación de sqlite. En el siguiente ejemplo, asumo que la tabla ya existe, pero el archivo csv tiene encabezados en la primera fila. Consulte .importlos documentos para obtener más información.
subprocess.run()
from pathlib import Path
db_name = Path('my.db').resolve()
csv_file = Path('file.csv').resolve()
result = subprocess.run(['sqlite3',
str(db_name),
'-cmd',
'.mode csv',
'.import --skip 1 ' + str(csv_file).replace('\\','\\\\')
+' <table_name>'],
capture_output=True)
nota de edición: el comando de sqlite3 .importha mejorado para que pueda tratar la primera fila como nombres de encabezado o incluso omitir las primeras x filas (requiere la versión >=3.32, como se indica en esta respuesta . Si tiene una versión anterior de sqlite3, es posible que necesite para crear primero la tabla, luego quitar la primera fila del csv antes de importar. El --skip 1argumento dará un error anterior a 3.32
Explicación
Desde la línea de comando, el comando que estás buscando es sqlite3 my.db -cmd ".mode csv" ".import file.csv table". subprocess.run()ejecuta un proceso de línea de comando. El argumento subprocess.run()es una secuencia de cadenas que se interpretan como un comando seguido de todos sus argumentos.
sqlite3 my.dbabre la base de datos-cmdLa bandera después de la base de datos le permite pasar múltiples comandos de seguimiento al programa sqlite. En el shell, cada comando debe estar entre comillas, pero aquí solo deben ser su propio elemento de la secuencia.'.mode csv'hace lo que esperarías'.import --skip 1'+str(csv_file).replace('\\','\\\\')+' <table_name>'es el comando de importación.
Desafortunadamente, dado que el subproceso pasa todos los siguientes-cmdcomo cadenas entre comillas, debe duplicar las barras invertidas si tiene una ruta de directorio de Windows.
Decapado de encabezados
Realmente no es el punto principal de la pregunta, pero esto es lo que usé. Nuevamente, no quería leer todos los archivos en la memoria en ningún momento:
with open(csv, "r") as source:
source.readline()
with open(str(csv)+"_nohead", "w") as target:
shutil.copyfileobj(source, target)
Mis 2 centavos (más genéricos):
import csv, sqlite3
import logging
def _get_col_datatypes(fin):
dr = csv.DictReader(fin) # comma is default delimiter
fieldTypes = {}
for entry in dr:
feildslLeft = [f for f in dr.fieldnames if f not in fieldTypes.keys()]
if not feildslLeft: break # We're done
for field in feildslLeft:
data = entry[field]
# Need data to decide
if len(data) == 0:
continue
if data.isdigit():
fieldTypes[field] = "INTEGER"
else:
fieldTypes[field] = "TEXT"
# TODO: Currently there's no support for DATE in sqllite
if len(feildslLeft) > 0:
raise Exception("Failed to find all the columns data types - Maybe some are empty?")
return fieldTypes
def escapingGenerator(f):
for line in f:
yield line.encode("ascii", "xmlcharrefreplace").decode("ascii")
def csvToDb(csvFile, outputToFile = False):
# TODO: implement output to file
with open(csvFile,mode='r', encoding="ISO-8859-1") as fin:
dt = _get_col_datatypes(fin)
fin.seek(0)
reader = csv.DictReader(fin)
# Keep the order of the columns name just as in the CSV
fields = reader.fieldnames
cols = []
# Set field and type
for f in fields:
cols.append("%s %s" % (f, dt[f]))
# Generate create table statement:
stmt = "CREATE TABLE ads (%s)" % ",".join(cols)
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute(stmt)
fin.seek(0)
reader = csv.reader(escapingGenerator(fin))
# Generate insert statement:
stmt = "INSERT INTO ads VALUES(%s);" % ','.join('?' * len(cols))
cur.executemany(stmt, reader)
con.commit()
return con
