Importowanie pliku CSV do tabeli bazy danych sqlite3 przy użyciu języka Python

Mam plik CSV i chcę zaimportować ten plik do mojej bazy danych sqlite3 za pomocą Pythona. polecenie to „.import .....”. ale wydaje się, że nie może tak działać. Czy ktoś może mi dać przykład, jak to zrobić w sqlite3? Na wszelki wypadek używam okien. Dzięki

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

Tworzenie połączenia sqlite do pliku na dysku jest pozostawione jako ćwiczenie dla czytelnika ... ale teraz jest możliwe dwuwierszowe połączenie możliwe dzięki bibliotece pandas

df = pandas.read_csv(csvfile)
df.to_sql(table_name, conn, if_exists='append', index=False)

Moje 2 centy (bardziej ogólne):

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

.importKomenda jest cechą narzędzia wiersza polecenia sqlite3. Aby to zrobić w Pythonie, należy po prostu załadować dane przy użyciu dowolnych funkcji, które ma Python, takich jak moduł csv , i wstawić dane w zwykły sposób.

W ten sposób masz również kontrolę nad tym, jakie typy są wstawiane, zamiast polegać na pozornie nieudokumentowanym zachowaniu sqlite3.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sys, csv, sqlite3

def main():
    con = sqlite3.connect(sys.argv[1]) # database file input
    cur = con.cursor()
    cur.executescript("""
        DROP TABLE IF EXISTS t;
        CREATE TABLE t (COL1 TEXT, COL2 TEXT);
        """) # checks to see if table exists and makes a fresh table.

    with open(sys.argv[2], "rb") as f: # CSV file input
        reader = csv.reader(f, delimiter=',') # no header information with delimiter
        for row in reader:
            to_db = [unicode(row[0], "utf8"), unicode(row[1], "utf8")] # Appends data from CSV file representing and handling of text
            cur.execute("INSERT INTO neto (COL1, COL2) VALUES(?, ?);", to_db)
            con.commit()
    con.close() # closes connection to database

if __name__=='__main__':
    main()

Wielkie dzięki za odpowiedź Berniego ! Musiałem to trochę poprawić - oto, co zadziałało dla mnie:

import csv, sqlite3
conn = sqlite3.connect("pcfc.sl3")
curs = conn.cursor()
curs.execute("CREATE TABLE PCFC (id INTEGER PRIMARY KEY, type INTEGER, term TEXT, definition TEXT);")
reader = csv.reader(open('PC.txt', 'r'), delimiter='|')
for row in reader:
    to_db = [unicode(row[0], "utf8"), unicode(row[1], "utf8"), unicode(row[2], "utf8")]
    curs.execute("INSERT INTO PCFC (type, term, definition) VALUES (?, ?, ?);", to_db)
conn.commit()

Mój plik tekstowy (PC.txt) wygląda następująco:

1 | Term 1 | Definition 1
2 | Term 2 | Definition 2
3 | Term 3 | Definition 3

Masz rację, .importto jest właściwy sposób, ale to polecenie z powłoki SQLite3.exe. Wiele najpopularniejszych odpowiedzi na to pytanie dotyczy natywnych pętli Pythona, ale jeśli twoje pliki są duże (moje to 10 ^ 6 do 10 ^ 7 rekordów), nie chcesz czytać wszystkiego w pandach lub używać natywnego rozumienia / pętli list Pythona (chociaż nie czas na ich porównanie).

W przypadku dużych plików uważam, że najlepszą opcją jest wcześniejsze utworzenie pustej tabeli za pomocą sqlite3.execute("CREATE TABLE..."), usunięcie nagłówków z plików CSV, a następnie użycie subprocess.run()do wykonania instrukcji importu sqlite. Ponieważ ostatnia część jest, moim zdaniem, najbardziej trafna, zacznę od tego.

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 '+str(csv_file).replace('\\','\\\\')
                                 +' <table_name>'],
                        capture_output=True)

Wyjaśnienie
W wierszu poleceń szukane polecenie to sqlite3 my.db -cmd ".mode csv" ".import file.csv table". subprocess.run()uruchamia proces wiersza poleceń. Argument do subprocess.run()jest sekwencją ciągów, które są interpretowane jako polecenie, po którym następują wszystkie jego argumenty.

• sqlite3 my.db otwiera bazę danych
• -cmdFlaga po bazie danych pozwala na przekazywanie wielu poleceń do programu sqlite. W powłoce każde polecenie musi być w cudzysłowach, ale tutaj wystarczy, że będzie to ich własny element sekwencji
• '.mode csv' robi to, czego można się spodziewać
• '.import '+str(csv_file).replace('\\','\\\\')+' <table_name>'to polecenie importu.
  Niestety, ponieważ podproces przekazuje wszystkie następstwa -cmdjako łańcuchy cytowane w cudzysłowie, musisz podwoić ukośniki odwrotne, jeśli masz ścieżkę do katalogu systemu Windows.

Usuwanie nagłówków

Właściwie nie jest to główny punkt pytania, ale oto, czego użyłem. Ponownie, w żadnym momencie nie chciałem wczytywać całych plików do pamięci:

with open(csv, "r") as source:
    source.readline()
    with open(str(csv)+"_nohead", "w") as target:
        shutil.copyfileobj(source, target)

Oparty na rozwiązaniu Guy L (Love it), ale radzi sobie z uciekłymi polami.

import csv, sqlite3

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,dbFile,tablename, 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 if not exists \"" + tablename + "\" (%s)" % ",".join(cols)
        print(stmt)
        con = sqlite3.connect(dbFile)
        cur = con.cursor()
        cur.execute(stmt)

        fin.seek(0)


        reader = csv.reader(escapingGenerator(fin))

        # Generate insert statement:
        stmt = "INSERT INTO \"" + tablename + "\" VALUES(%s);" % ','.join('?' * len(cols))

        cur.executemany(stmt, reader)
        con.commit()
        con.close()

Możesz to zrobić za pomocą blaze& odoefektywnie

import blaze as bz
csv_path = 'data.csv'
bz.odo(csv_path, 'sqlite:///data.db::data')

Odo będzie przechowywać plik csv w data.db(bazie danych sqlite) w ramach schematudata

Lub używasz odobezpośrednio, bez blaze. Każdy sposób jest w porządku. Przeczytaj tę dokumentację

Jeśli plik CSV musi zostać zaimportowany jako część programu w języku Python, to dla uproszczenia i wydajności można użyć os.systemnastępujących wskazówek:

import os

cmd = """sqlite3 database.db <<< ".import input.csv mytable" """

rc = os.system(cmd)

print(rc)

Chodzi o to, że podając nazwę pliku bazy danych, dane zostaną automatycznie zapisane, przy założeniu, że nie ma błędów w ich odczytaniu.

import csv, sqlite3

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,dbFile,tablename, 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 if not exists \"" + tablename + "\" (%s)" % ",".join(cols)
        print(stmt)
        con = sqlite3.connect(dbFile)
        cur = con.cursor()
        cur.execute(stmt)

        fin.seek(0)


        reader = csv.reader(escapingGenerator(fin))

        # Generate insert statement:
        stmt = "INSERT INTO \"" + tablename + "\" VALUES(%s);" % ','.join('?' * len(cols))

        cur.executemany(stmt, reader)
        con.commit()
        con.close()

ze względu na prostotę możesz użyć narzędzia wiersza poleceń sqlite3 z pliku Makefile twojego projektu.

%.sql3: %.csv
    rm -f $@
    sqlite3 $@ -echo -cmd ".mode csv" ".import $< $*"
%.dump: %.sql3
    sqlite3 $< "select * from $*"

make test.sql3następnie tworzy bazę danych sqlite z istniejącego pliku test.csv z pojedynczą tabelą „test”. możesz następnie make test.dumpzweryfikować zawartość.

Odkryłem, że może być konieczne podzielenie transferu danych z csv do bazy danych na fragmenty, aby nie zabrakło pamięci. Można to zrobić w następujący sposób:

import csv
import sqlite3
from operator import itemgetter

# Establish connection
conn = sqlite3.connect("mydb.db")

# Create the table 
conn.execute(
    """
    CREATE TABLE persons(
        person_id INTEGER,
        last_name TEXT, 
        first_name TEXT, 
        address TEXT
    )
    """
)

# These are the columns from the csv that we want
cols = ["person_id", "last_name", "first_name", "address"]

# If the csv file is huge, we instead add the data in chunks
chunksize = 10000

# Parse csv file and populate db in chunks
with conn, open("persons.csv") as f:
    reader = csv.DictReader(f)

    chunk = []
    for i, row in reader: 

        if i % chunksize == 0 and i > 0:
            conn.executemany(
                """
                INSERT INTO persons
                    VALUES(?, ?, ?, ?)
                """, chunk
            )
            chunk = []

        items = itemgetter(*cols)(row)
        chunk.append(items)