Importer un fichier CSV dans une table de base de données sqlite3 en utilisant Python

Question

J'ai un fichier CSV et je veux l'importer en masse dans ma base de données sqlite3 en utilisant Python. La commande est ".import .....". Mais il semble que cela ne puisse pas fonctionner ainsi. Quelqu'un peut-il me donner un exemple de comment le faire dans sqlite3 ? J'utilise Windows, au cas où. Merci

Answer 1

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

Answer 2

La création d'une connexion sqlite à un fichier sur le disque est laissée comme un exercice pour le lecteur ... mais il y a maintenant une double ligne rendue possible par la bibliothèque pandas.

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

Answer 3

Vous avez raison. .import est la meilleure solution, mais il s'agit d'une commande du programme de ligne de commande SQLite3. Un grand nombre des meilleures réponses à cette question impliquent des boucles python natives, mais si vos fichiers sont volumineux (les miens comptent 10^6 à 10^7 enregistrements), vous voudrez éviter de tout lire dans pandas ou d'utiliser une compréhension de liste/boucle python native (bien que je ne les ai pas chronométrés pour comparaison).

Pour les gros fichiers, je pense que la meilleure option est d'utiliser subprocess.run() pour exécuter la commande d'importation de sqlite. Dans l'exemple ci-dessous, je suppose que la table existe déjà, mais le fichier csv a des en-têtes dans la première ligne. Voir .import docs pour plus d'informations.

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)

_note d'édition : les fonctions de sqlite3 .import a été améliorée de sorte qu'elle peut traiter la première rangée comme des noms d'en-tête ou même sauter la première x (nécessite une version >=3.32, comme indiqué dans la rubrique cette réponse . Si vous avez une ancienne version de sqlite3, vous devrez peut-être d'abord créer la table, puis supprimer la première ligne du csv avant de l'importer. Le site --skip 1 donnera une erreur avant la version 3.32._

Explication
Depuis la ligne de commande, la commande que vous recherchez est sqlite3 my.db -cmd ".mode csv" ".import file.csv table" . subprocess.run() exécute un processus en ligne de commande. L'argument de subprocess.run() est une séquence de chaînes de caractères qui sont interprétées comme une commande suivie de tous ses arguments.

• sqlite3 my.db ouvre la base de données
• -cmd après la base de données vous permet de passer plusieurs commandes de suivi au programme sqlite. Dans l'interpréteur de commandes, chaque commande doit être entre guillemets, mais ici, elles doivent simplement être leur propre élément de la séquence.
• '.mode csv' fait ce que l'on attend d'elle
• '.import --skip 1'+str(csv_file).replace('\\','\\\\')+' <table_name>' est la commande d'importation.
  Malheureusement, puisque le sous-processus transmet tous les suivis à -cmd en tant que chaînes entre guillemets, vous devez doubler vos antislashs si vous avez un chemin de répertoire Windows.

Décapage des en-têtes

Ce n'est pas vraiment le point principal de la question, mais voici ce que j'ai utilisé. Encore une fois, je ne voulais pas lire les fichiers entiers en mémoire à un moment donné :

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

Answer 4

Mes 2 centimes (plus génériques) :

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

Answer 5

El .import est une fonctionnalité de l'outil de ligne de commande sqlite3. Pour le faire en Python, il suffit de charger les données à l'aide de toutes les possibilités offertes par Python, telles que la commande module csv et d'insérer les données comme d'habitude.

De cette façon, vous avez également le contrôle sur les types qui sont insérés, plutôt que de compter sur le comportement apparemment non documenté de sqlite3.