[Python 3.X] Charger CSV dans base de données SQLite

Charger CSV dans base de données SQLite

Bonjour à tous,

Après avoir passé des heures à chercher une solution à mon problème, je me permets de soumettre mon code à votre sagacité.

Ce que je cherche à faire, c'est prendre les fichiers CSV d'un repertoire pour les mettre dans un fichier base de données SQLite. Chaque table correspond à un nom de fichier CSV (sans extension).
Mes fichiers CSV ont la particularité de ne pas forcement avoir d'entête, je souhaite donc avant de charger mon fichier dans la base de créer une entête avec 1, 2, 3, 4, 5... Pour cela, au préalable, je lis mon fichier CSV pour connaître le nombre de colonnes.
Autre spécificité, les fichiers CSV peuvent contenir des fichiers avec des caractères chinois.

J'arrive charger partiellement mes fichiers (plusieurs milliers de lignes) mais j'arrive de manière systématique à l'erreur de ce type:

Code:

1
2
3
4
5
6
7
8
9
10
Traceback (most recent call last):
  File "C:\Users\PCF102\Desktop\reporttools.py", line 41, in <module>
    CSVtoTable(SQLiteDatabase, SQLiteTables)
  File "C:\Users\PCF102\Desktop\reporttools.py", line 15, in CSVtoTable
    loader.loadCSVtoTable(CSVFile, SQLiteTable)
  File "C:\Users\PCF102\Desktop\converter.py", line 149, in loadCSVtoTable
    self.__insertRow(tableName, row)
  File "C:\Users\PCF102\Desktop\converter.py", line 51, in __insertRow
    self.cursor.execute(statement)
sqlite3.OperationalError: near "54.87": syntax error

ou bien

Code:

1
2
3
4
5
6
7
8
9
10
Traceback (most recent call last):
  File "C:\Users\PCF102\Desktop\reporttools.py", line 41, in <module>
    CSVtoTable(SQLiteDatabase, SQLiteTables)
  File "C:\Users\PCF102\Desktop\reporttools.py", line 15, in CSVtoTable
    loader.loadCSVtoTable(CSVFile, SQLiteTable)
  File "C:\Users\PCF102\Desktop\converter.py", line 149, in loadCSVtoTable
    self.__insertRow(tableName, row)
  File "C:\Users\PCF102\Desktop\converter.py", line 51, in __insertRow
    self.cursor.execute(statement)
sqlite3.OperationalError: 11 values for 12 columns

Dans le premier cas, je pense que la définition de l'entête pose problème. Mais je n'arrive pas trouver pourquoi. J'ai fait de nombreux essais sans succès, si quelqu'un ici accepte de bien vouloir se pencher sur mon problème. Je lui en serait reconnaissant.
Merci d'avance.

NB: Ci-dessous, le code du programme et des exemples de CSV.

Voici le premier code pour l'appel de mon module d'import CSV:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
#!/usr/bin/env python3
# -*- coding : utf-8 -*-
# Python 3
 
"""Créateur de rapport en ligne de commande"""
 
import csv, os, time, converter, glob
 
 
def CSVtoTable(SQLiteDatabase, SQLiteTables):
 
    # Charger les tables dans la base de données
    loader = converter.csvSQLiteConvert(SQLiteDatabase)
    for CSVFile, SQLiteTable in SQLiteTables.items():
        loader.loadCSVtoTable(CSVFile, SQLiteTable)
    loader.close()
 
if __name__=='__main__':
 
    currentDirectory = os.getcwd()
    # Dossier avec les fichiers CSV
    CSVdirectory = os.path.join(currentDirectory, "csv")
    CSVlistFiles = glob.glob(os.path.join(CSVdirectory +'\*.csv'))
    # Dossier contenant les bases de données et nom du fichier base de données
    SQLiteDirectory = os.path.join(currentDirectory, "database")
    SQLiteDatabase = os.path.join(SQLiteDirectory, "myDatabase.db")
 
    # Obtenir le nom de chaque fichier CSV afin d'en créer des tables SQLite par la suite
    # On supprime le .csv dans le nom de la table, sinon erreur lors de la creation de la table
    SQLiteTables = {}
    for tableSQlite in CSVlistFiles:
        SQLiteTables[tableSQlite] = (os.path.split(tableSQlite)[-1]).rstrip(".csv")
 
    # Affiche le dossier courant et les fichiers CSV du dossier CSV
    # Facultatif
    print(currentDirectory)
    print(os.path.join(CSVdirectory +'\*.csv'))
    print(SQLiteTables)
 
    # Charger les tables dans la bases de données
    CSVtoTable(SQLiteDatabase, SQLiteTables)

Et ci-dessous, le module que j'ai récupéré pour le traitement proprement dit:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
__author__ = 'stanley'
import sqlite3
import os
import csv
 
class csvSQLiteConvert:
    '''
 
    This class takes a csv file and puts the contents into an SQLite database table.
    This pre--population is important in cases where you have to convert a csv file into an SQLite database and also
    when there is the need to have a prepopulated SQLite DB to use as a resource in mobile development.
 
    '''
 
    def __init__(self, SQLiteDBfileName='csvSQLiteloader.db'):
        '''
        Initialise CSQliteloader Class
 
        :param SQLiteDBfileName:
        :return:
        '''
 
        #remove any previously created file to avoid conflicts
        try:
            os.remove(self.__fullFilename(SQLiteDBfileName))
        except OSError:
            pass
 
        #Initialise connection and Class Variables
        self.SQLiteDBfileName = SQLiteDBfileName
        self.conn = sqlite3.connect(SQLiteDBfileName)
        self.cursor = self.conn.cursor()
        self.tableFields = []
 
 
    def __insertRow(self, tableName, rowDict):
        '''
        Insert a single row into DB
 
        :param tableName:
        :param rowDict:
        :return:
        '''
 
        #Quote values to allow smooth insertion of values
        row = ['"'+v.strip().strip('"')+'"' for v in rowDict.values()]
 
        #Create sql statement to insert data into database table
        statement = "insert into %s (\'%s\') values (%s)" %(tableName, '\', \''.join(self.tableFields), str(', '.join(row)))
        print(statement)
        self.cursor.execute(statement)
 
 
    def __createTable(self, tableName):
 
        '''
        Creates tables based on names of tables and fields
 
        :param tableName:
        :return:
        '''
 
        #Create sql statement to create database table
        print('Table with name: %s created' % tableName)
        print(self.tableFields)
        statement = 'CREATE TABLE IF NOT EXISTS %s (_id integer primary key,\'%s\' text);' %(tableName, """\' text, \'""".join(self.tableFields))
        print(statement)
        print("statement************************************************************")
        print('Table with name: %s created' % tableName)
        self.cursor.execute(statement)
 
 
    def __fullFilename(self, filename):
        '''
        Get full file path
 
        :param filename:
        :return:
        '''
        return os.path.dirname(os.path.realpath(__file__))+'/'+filename
 
 
    def __readFile(self, csvPath):
        '''
        Reads contents of CSV files
 
        :param csvPath:
        :return:
        '''
 
        try:
 
            csv.register_dialect('lines', quotechar="'", delimiter=',',
                     quoting=csv.QUOTE_NONNUMERIC, skipinitialspace=True)
 
            #This will make sure to close file even if exception is raised
            with open(csvPath, 'r', encoding="utf8") as f:
                reader = csv.DictReader(f)
                for row in reader:
                # treat the file object as an iterable,and automatically
                # use buffered IO and memory management to help with large files
                    yield row
        except Exception as e:
            print("I/O error({0}): {1}".format(e.errno, e.strerror))
            print("File {} NOT found".format(csvPath))
            exit()
 
 
    def setTableFields(self,csvFile, customFields = [], firstCustom = False):
 
        '''
            Initialise list of fields; If list is not available and firstCustom is True use first row of csv
        :param fields:
        :return:
        '''
        if not firstCustom:
            with open(csvFile, 'r', encoding="utf8") as f:
                lengthFields = len(f.readline().split(","))
                #self.tableFields = []
                reader = csv.DictReader(f)
                for row in reader:
                    self.tableFields = row
            #with open(csvFile, 'w') as csvfile:
                #writer = csv.DictWriter(csvfile, fieldnames=self.tableFields)
 
        elif len(customFields) == 0 and firstCustom:
            for row in self.__readFile(csvFile):
                self.tableFields = row.keys()
                break
        else:
            self.tableFields = customFields
 
 
    def loadCSVtoTable(self, csvFile, tableName):
 
        '''
        Load data into SLQlite Database
        :return:
        '''
 
        #set table fields from CSV it is not explicitly set
        self.setTableFields(csvFile)
 
        #Create Database table and make it ready for insertion of data
        self.__createTable(tableName)
 
        #Read and insert each live in to the created table
        for row in self.__readFile(csvFile):
            self.__insertRow(tableName, row)
 
        #commit records when done
        self.conn.commit()
 
 
    def close(self):
        self.conn.close()
        print('Connection Closed')

Un exemple de CSV. (Les caractères chinois ne semblent pas passer sur le forum)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
"545";"5487";"878878787";"545";"";"";"4568";"大红灯笼高高"
"54.5";"54.87";"878878787";"545";"";"";"4.568";"大红灯笼高高"
"545";"5487";"878878787";"545";"";"";"456.8";"大红灯笼高高"
"545";"5487";"878458787";"545";"";"";"45.68";"大红灯笼高高"
"545";"5487";"878443878787";"545";"";"";"4.568";"大红灯笼高高"
"545";"5487";"878878434787";"545";"";"";"45.68";"大红灯笼高高"
"545";"5487";"878878787";"545";"";"";"4.568";"大红灯笼高高"
"545";"5487";"878834478787";"545";"";"";"45.68";"大红灯笼高高"
"545";"5487";"878878787";"545";"";"";"4.568";"大红灯笼高高"
"545";"5487";"878434878787";"545";"";"";"4.568";"大红灯笼高高"
"545";"5487";"878878787";"545";"";"";"4568";"大红灯笼高高"
"545";"5487";"8788458578787";"544545";"";"";"45.68";"大红灯笼高高"

Salut,

Lorsque vous voyez une erreur comme:

Code:

sqlite3.OperationalError: 11 values for 12 columns

Il faut regarder ce qu'affiche l'instruction "print(statement)" à la ligne 50 et essayer de comprendre pourquoi il trouve 11 plutôt que 12.

- W

Bonjour Wiztricks et merci pour votre réponse.

Pour résoudre cela, je me suis focalisé sur ce que vous m'avez indiqué en me concentrant des éléments que j'utilise pour formuler mes requêtes. Après de nombreux essais et un peu de lecture (d'où le temps de réponse un peu long), j'arrive enfin charger mes fichiers CSV dans ma base de données.

Tous? Non, un ficher résiste encore et toujours...et sur ce coup, je ne sais absolument pas pourquoi. A priori, le problème est un saut de ligne au milieu de ma ligne de données. Le fichier étant généré par un autre programme sur lequel je n'ai pas la main, je n'ai pas d'autre choix que remouliner le CSV produit.

Au départ, je suis parti sur une fonction type split() ou rename('\n', ""). Cela me supprime bien le saut de ligne en bout mais pas celui du milieu. J'en déduis que ce doit être un caractère plus particulier.

Je ne peux pas dire de remplacer les caractères autres que "A_Z","a_z" et "0_9" par "" car j'utilise des caractères accentués ou asiatiques dans mes fichiers CSV. Et la reprise manuelle est difficilement envisageable car le fichier fait plusieurs centaines de lignes.

Pour le test, j'ai essayé l'import dans excel, même combat, impossible d'importer correctement le fichier à cause de ce maudit caractère spécial.

je vous joins ci-dessous un exemple (en espérant que le format et le problème sera conservé.
Code:

1 2 3 4 "8","20160930","","1","","010000009728000","","","","","0114","0","","2","EUR","30410.94","34124.12","1 ","20160929" "5","20160929","","1","","011009728000001","","","","","0114","0","","2","EUR","9.63","10.81","20 ","20160929"
Si vous avez des pistes car là, je sèche.
Merci d'avance.

Salut,

Citation:

Envoyé par austin57

Pour le test, j'ai essayé l'import dans excel, même combat, impossible d'importer correctement le fichier à cause de ce maudit caractère spécial.

Ben pour voir ce que c'est, vous pouvez toujours regarder le contenu du fichier en hexadécimal soit avec Python soit avec l'utilitaire qui va bien sur votre environnement système.

- W

Bonne idée l'affichage en Hex...
Cela m'a permis de lever le doute sur le type de caractère récalcitrant, il s'agit bien d'un saut de ligne (code 0a)! A voir comment je peux traiter le problème. Je cherche et si je trouve une solution je repost un message. Et si vraiment je n'arrive à rien...euh..je reposte. :mrgreen:

Bon, j'ai finalement trouvé la solution à tous mes problèmes...enfin, en python au moins.

J'ai crée un petit programme à part pour pouvoir remettre d'aplomb mon fichier CSV. La solution était simple, remplacer le saut de ligne par une chaine vide suivi d'une concaténation avec la ligne suivante. Le résultat est impeccable.

Merci de m'avoir mis sur la voie.