User

Objectif : Comprendre les règles de transformation d'un fichier JSON en base de données SQLite, puis savoir comment paramétrer la fonction Python permettant de créer la base de données.


I. Introduction

Après avoir détaillé la structure générale d'un fichier JSON (JavaScript Object Notation ), on décrira les règles de transformation du fichier en une base de données SQLite : les structures d'objets deviennent des tables et les dépendances entre objets deviennent des liens entre des tables. Puis, on présentera brièvement la fonction de création de la base de données.

Enfin, on proposera le module Python complet permettant de créer la base de données SQLite.

II. Structure JSON

Dans un fichier JSON les parties délimitées par des crochets [] représentent des tableaux d'éléments, et ce qui est entre accolades {} définit la structure d'un objet.

Considérons un fichier clients.json de contenu :

Code json :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
{
  "nom_client": "Dupont",
  "prenom_client": "Jean",
  "telephone_client": "01 01 01 01 01",
  "email_client": "dupont@email.com", 
  "adresse": {
    "rue": "24 avenue des peupliers",
    "code_postal": "75000",
    "ville": "Paris"
  },
  "contact": [
    {
      "nom_contact": "Martin",
      "prenom_contact": "Vincent",
      "telephone_contact": "01 04 04 04 04" 
    },
    {
      "nom_contact": "Cousin",
      "prenom_contact": "Olivier",
      "telephone_contact": "01 04 04 04 05"	
    }
  ]
}

On voit qu'on a une structure d'objet avec des attributs simples (nom_client, prenom_client, telephone_client, email_client) et des attributs composites (adresse, contact).

On peut également avoir un tableau comme structure principale :

Code JSON :

Sélectionner tout - Visualiser dans une fenêtre à part

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
[
  {
    "nom_client": "Dupont",
    "prenom_client": "Jean",
    "telephone_client": "01 01 01 01 01",
    "email_client": "dupont@email.com", 
    "adresse": {
      "rue": "24 avenue des peupliers",
      "code_postal": "75000",
      "ville": "Paris"
    },
    "contact": [
      {
        "nom_contact": "Martin",
        "prenom_contact": "Vincent",
        "telephone_contact": "01 04 04 04 04" 
      },
      {
        "nom_contact": "Cousin",
        "prenom_contact": "Olivier",
        "telephone_contact": "01 04 04 04 05"	
      }
    ]
  },
   ...
]

III. Règles de passage de la structure JSON à la base de données

La structure d'objet principal avec ses propriétés simples (nom_client, prenom_client, telephone_client, email_client) sera transformée en une table client avec les mêmes attributs. On ajoutera à la table une clé primaire client_id pour identifier chaque enregistrement.

Les autres attributs adresse et contact ont eux-mêmes une structure d'objet :

La structure d'objet adresse avec les propriétés (rue, code_postal et ville) sera transformée en une table client_adresse avec les mêmes attributs.

On ajoutera à cette table une clé primaire client_adresse_id, et une clé étrangère client_fk pour faire le lien avec la table principale client.

L'attribut contact représente en fait un tableau d'objets ayant la même structure (nom_contact, prenom_contact, telephone_contact). Il sera transformé en une table client_contact avec les mêmes attributs.

On ajoutera à cette table une clé primaire client_contact_id, et une clé étrangère client_fk pour faire le lien avec la table principale client.

La fonction d'importation va donc créer les tables suivantes :

• client(client_id, nom_client, prenom_client, telephone_client, email_client) ;
• client_adresse(client_adresse_id, client_fk, rue, code_postal, ville) ;
• client_contact(client_contact_id, client_fk, nom_contact, prenom_contact, telephone_contact).

Schéma de la base de données :



On remarque qu'il y a des liaisons sur les colonnes client_id et client_fk, avec une relation 1-à-1 entre les tables client et client_adresse et une relation 1-à-plusieurs entre les tables client et client_contact.

Ces tables contiendront bien sûr les données du fichier JSON.

Correspondances entre les types de données Python et SQLite :

Type de données JSON	Type de données Python	Type de données SQLite
boolean	bool	integer
number	int	integer
number	float	real
string	str	text
...	...	...

A noter que la valeur null dans JSON et SQLite correspond à None côté Python.


IV. Création de la base de données SQLite

Vous devez créer avant un dossier pour la base de données SQLite avant d'exécuter le programme (exemple "c:\sqlite\db").

La fonction de création de la connexion va automatiquement créer la base de données si elle n'est pas déjà présente dans le dossier.

Déroulé de la fonction de création de la base :

• création de la connexion à la base de données SQLite ;
• ouverture du fichier JSON en mode lecture ;
• chargement du contenu du fichier JSON dans un objet dict ou list avec la fonction load du module json ;
• transformation des objets en tables et importation des données à l'aide de la fonction create_database.

Code Python :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
def main(): # fonction principale
 
    database = r"C:\sqlite\db\pythonsqlite.db" # chemin de la base de données
 
    # création de la connexion à la base de données sqlite
    conn = create_connection(database)
 
    # si la connexion a été réalisée
    if conn is not None:
 
        with open('clients.json', 'r', encoding='utf-8') as jsonfile: # on ouvre le fichier json situé dans le même dossier que le script python
 
            json_data = json.load(jsonfile) # on charge les données json dans une variable objet
            #create_database(conn, json_data, table_name, column_name='', fk_column_name='', fk_column_value=0)
            create_database(conn, json_data, 'client') # on créé et on alimente la base de données sqlite        
 
    else:
        print("Erreur! impossible de créer la connexion à la base !")

Arguments de la fonction create_database :

• conn: objet permettant la connexion à la base de données SQLite ;
• json_data: données JSON dont on extrait les noms des colonnes et leur type de données ;
• table_name: nom de la table à créer ;
• column_name: nom de la colonne de la table dans le cas ou il y a une seule colonne à ajouter en plus des clés, argument optionnel ;
• fk_column_name: nom de la colonne clé étrangère, argument optionnel ;
• fk_column_value: valeur de la colonne clé étrangère, argument optionnel.

Lors du premier appel de la fonction récursive l'argument table_name correspond au nom de la table principale ("client").

Si les tables ont déjà été créées une première fois, les fois suivantes elle n'importera que les données.


V. Module complet

Pour pouvoir charger les données du fichier JSON dans une liste ou un dictionnaire, on doit d'abord importer le module json :

Code python :

Sélectionner tout - Visualiser dans une fenêtre à part

import json

Pour disposer des fonctions de gestion de la base SQLite (connexion, création des tables, ajout des données..), on doit aussi importer le module splite3 :

Code python :

Sélectionner tout - Visualiser dans une fenêtre à part

import sqlite3

Code python :

Sélectionner tout - Visualiser dans une fenêtre à part

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
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
import json
import sqlite3
from sqlite3 import Error
 
 
def create_connection(db_file):
    """ crée une connexion à la base de données SQLite
        specifié par le chemin du fichier passé en argument
    :param db_file: chemin du fichier db
    :return: un objet connexion ou renvoie none
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file) # on crée l'objet connexion
        return conn
    except Error as e: # gestion de l'erreur
        print(e) # affichage du message d'erreur
 
    return conn
 
 
def eval_type(var):
    """ renvoie le type de données sqlite correspondant à la variable passée en argument
    """
    if (type(var) is bool) or (type(var) is int): # si la valeur est du type booléen ou integer
        return 'integer'  
    else: # sinon
        if type(var) is float: # si la valeur est du type float
            return 'real'
        else: # sinon
            return 'text'
 
def create_table(conn, json_data, table_name, column_name, fk_column_name):
    """ crée une table en fonction des données json contenues dans la variable objet json_data
    arguments:
    conn: objet permettant la connexion à la base de données sqlite
    json_data: données json dont on extrait les noms des colonnes et leur type de données
    table_name: nom de la table à créer
    column_name: nom de la colonne de la table dans le cas ou il y a une seule colonne à ajouter en plus des clés
    fk_column_name: nom de la colonne clé étrangère
    """
 
    try:
 
        c = conn.cursor()
 
        id_column_name = table_name +  "_id"
        create_table_sql = """ CREATE TABLE IF NOT EXISTS """ + table_name + "(" + id_column_name + " integer PRIMARY KEY AUTOINCREMENT," # on ajoute le nom de la colonne identifiant et son type à la chaine sql
 
        if fk_column_name!='': # si la table doit comporter une clé étrangère
            create_table_sql = create_table_sql + "[" + fk_column_name + "] integer," # on ajoute le nom de la colonne clé étrangère et son type à la chaine sql
        else:
            column_name = table_name
 
        if type(json_data) is dict: # si c'est un dictionnaire
            for k,v in json_data.items(): # on parcourt ses éléments
                if (type(v) is not list) & (type(v) is not dict) & (v is not None): # si l'élément est une valeur
                    create_table_sql = create_table_sql + " [" + k + "] " + eval_type(v) + "," # on ajoute le nom de la colonne et son type à la chaine sql
        else: # sinon
            if (type(json_data) is list) & (json_data!=[]): # si c'est une liste non vide
                if (type(json_data[0]) is dict): # et si c'est une liste de dictionnaires
                    for k,v in json_data[0].items(): # on parcourt ses éléments
                        if (type(v) is not list) & (type(v) is not dict) & (v is not None): # si l'élément est une valeur
                            create_table_sql = create_table_sql + " [" + k + "] " + eval_type(v) + "," # on ajoute le nom de la colonne et son type à la chaine sql
                else: # sinon
                    create_table_sql = create_table_sql + " [" + column_name + "] " + eval_type(json_data[0]) + "," # on ajoute le nom de la colonne et son type à la chaine sql
            else:
                create_table_sql = create_table_sql + " [" + column_name + "] " + eval_type(json_data) + "," # on ajoute le nom de la colonne et son type à la chaine sql
 
        create_table_sql = create_table_sql[0:-1] + ")" # on constitue la chaine sql finale en ajoutant une parenthèse à la fin
 
        c.execute(create_table_sql) # on exécute le sql
 
        conn.commit() # on aussure que les données sont à jour
 
        return True
 
    except Error as e: # gestion d'erreur
        print(e) # message affiché en cas d'erreur
        return False
 
 
def add_data(conn, json_data, table_name, column_name, id_column_name, id_column_value, fk_column_name, fk_column_value):
    """ crée une table en fonction des données json contenues dans la variable objet json_data
    arguments :
    conn: objet permettant la connexion à la base de données sqlite
    json_data: données json dont on extrait les noms des colonnes et leur type de données
    table_name: nom de la table à créer
    column_name: nom de la colonne de la table dans le cas ou il y a une seule colonne à ajouter en plus des clés
    id_column_name: nom de la colonne identifiant 
    id_column_value: valeur de la colonne identifiant 
    fk_column_name: nom de la colonne clé étrangère
    fk_column_value: valeur de la colonne clé étrangère
    """
 
    try:
 
        c = conn.cursor()
 
        string_values = '('
        values = ()
 
        insert_table_sql = """ INSERT INTO """ + table_name + "([" + id_column_name + "]," # on ajoute le nom de la colonne identifiant et son type à la chaine sql
        string_values =  string_values  + '?,' # on constitue la liste des paramètres de la clause values dans la chaine sql
        values = values + (id_column_value,)
 
        if fk_column_name!='': # si la table doit comporter une clé étrangère
            insert_table_sql = insert_table_sql + "[" + fk_column_name + "]," # on ajoute le nom de la colonne à la chaine sql
            string_values =  string_values  + '?,' # on constitue la liste des paramètres de la clause values dans la chaine sql
            values = values + (fk_column_value,)
        else:
            column_name = table_name
 
        if type(json_data) is dict: # si c'est un dictionnaire
 
            for k,v in json_data.items(): # on parcourt ses éléments
                if (type(v) is not list) & (type(v) is not dict) & (v is not None):
                    insert_table_sql = insert_table_sql + " [" + k + "]," # on ajoute le nom de la colonne à la chaine sql
                    string_values =  string_values  + '?,' # on constitue la liste des paramètres de la clause values dans la chaine sql
                    values = values + (v,)
 
        else:
            if (type(json_data) is list) & (json_data!=[]): # si c'est une liste non vide
                if (type(json_data[0]) is dict): # et si c'est une liste de dictionnaires
                    for k,v in json_data[0].items(): # on parcourt ses éléments
                        if (type(v) is not list) & (type(v) is not dict) & (v is not None): # si l'élément est une valeur
                            insert_table_sql = insert_table_sql + " [" + k + "]," # on ajoute le nom de la colonne à la chaine sql
                            string_values =  string_values  + '?,' # on constitue la liste des paramètres de la clause values dans la chaine sql
                            values = values + (v,)
                else: # sinon
                    column_value = json_data[0]
                    insert_table_sql = insert_table_sql + " [" + column_name + "]," # on ajoute le nom de la colonne à la chaine sql
                    string_values =  string_values  + '?,' # on constitue la liste des paramètres de la clause values dans la chaine sql
                    values = values + (column_value,)
            else: # sinon
                column_value = json_data
                insert_table_sql = insert_table_sql + " [" + column_name + "]," # on ajoute le nom de la colonne à la chaine sql
                string_values =  string_values  + '?,' # on constitue la liste des paramètres de la clause values dans la chaine sql
                values = values + (column_value,)
 
        insert_table_sql = insert_table_sql[0:-1] + ")" 
        string_values =  string_values[0:-1] + ')'
 
        insert_table_sql = insert_table_sql + ' VALUES' + string_values # on constitue la chaine sql finale en ajoutant la clause VALUES
 
        c.execute(insert_table_sql,values) # on exécute le sql
 
        conn.commit() # on s'aussure que les données sont à jour
 
    except Error as e: # gestion d'erreur
        print(e) # message affiché en cas d'erreur
 
def get_id(conn, table_name, id_column_name):
 
    cur = conn.cursor()
 
    cur.execute("SELECT max(" + id_column_name + ") as id_max FROM " + table_name)
 
    data=cur.fetchone()
 
    if data[0]is None:
        return 1
    else:            
        return data[0] + 1    
 
 
def create_database(conn, json_data, table_name, column_name='', fk_column_name='', fk_column_value=0):
    """ crée la base de données sqlite en fonction des données json contenues dans la variable objet json_data
    arguments :
    conn: objet permettant la connexion à la base de données sqlite
    json_data: données json dont on extrait les noms des colonnes et leur type de données
    table_name: nom de la table à créer
    column_name: nom de la colonne de la table dans le cas ou il y a une seule colonne à ajouter en plus des clés, argument optionnel.
    fk_column_name: nom de la colonne clé étrangère, argument optionnel.
    fk_column_value: valeur de la colonne clé étrangère, argument optionnel.
    """
 
    if type(json_data) is list: # si c'est une liste
 
        # on crée la table dans la base sqlite
        create_table(conn, json_data, table_name, column_name, fk_column_name)
 
        for element in json_data: # on parcourt la liste des éléments de la liste
            create_database(conn, element, table_name, column_name, fk_column_name, fk_column_value) # on appelle la fonction avec comme argument l'élément de la liste
    else:                
        if type(json_data) is dict: # si c'est un dictionnaire
            # on crée la table dans la base sqlite
            create_table(conn, json_data, table_name, column_name, fk_column_name)            
 
            id_column_value = get_id(conn, table_name, table_name + '_id') # on récupère la valeur de l'identifiant
            # on ajoute les données du dictionnaire dans la table nouvellement crée
            add_data(conn, json_data, table_name, column_name, table_name + '_id', id_column_value, fk_column_name, fk_column_value)
 
            fk_column_name =  table_name + '_fk' # nom de la colonne clé étrangère
            fk_column_value = id_column_value # valeur de la colonne clé étrangère
 
            # on parcourt les éléments du dictionnaire
            for k, v in json_data.items():                
                column_name = k
                if (type(v) is list) or (type(v) is dict): # si l'élément est une valeur
                    create_database(conn, v, table_name + '_' + column_name, column_name, fk_column_name, fk_column_value) # on appelle la fonction avec comme argument l'élément du dictionnaire
 
        else:           
            column_value = json_data # copie de la valeur de la colonne dans la variable
            id_column_name = table_name + '_id' # copie du nom de la colonne identiant
            id_column_value = get_id(conn, table_name, id_column_name) # on récupère la valeur de l'identifiant
            # on ajoute la donnée json dans la table nouvellement crée
            add_data(conn, json_data, table_name, column_name, id_column_name, id_column_value, fk_column_name, fk_column_value)
 
 
 
def main(): # fonction principale
 
    database = r"C:\sqlite\db\pythonsqlite.db" # chemin de la base de données
 
    # création de la connexion à la base de données sqlite
    conn = create_connection(database)
 
    # si la connexion a été réalisée
    if conn is not None:
 
        with open('clients.json', 'r', encoding='utf-8') as jsonfile: # on ouvre le fichier json situé dans le même dossier que le script python
 
            json_data = json.load(jsonfile) # on charge les données json dans une variable objet
            #create_database(conn, json_data, table_name, column_name='', fk_column_name='', fk_column_value=0)
            create_database(conn, json_data, 'client') # on créé et on alimente la base de données sqlite        
 
    else:
        print("Erreur! impossible de créer la connexion à la base !")
 
 
if __name__ == '__main__':
    main()

Attention : les éléments des tableaux dans les fichiers JSON doivent avoir la même structure, dans le cas contraire il peut y avoir un message d'erreur.


Le code peut bien sûr être adapté pour d'autres bases de données.