Introduction

Ce tutoriel couvrira l’utilisation de SQLite en combinaison avec L’interface sqlite3 de Python. SQLite est une base de données relationnelle à fichier unique fournie avec la plupart des installations Python standard. SQLite est souvent la technologie de choix pour les petites applications, en particulier celles des systèmes et appareils embarqués tels que les téléphones et les tablettes, les appareils intelligents et les instruments. Cependant, il n’est pas rare de l’entendre être utilisé pour les petites et moyennes applications web et de bureau.,

créer une base de données et établir une connexion

créer une nouvelle base de données SQLite est aussi simple que de créer une connexion à l’aide du module sqlite3 de la bibliothèque standard Python. Pour établir une connexion, il vous suffit de passer un chemin de fichier à la méthode connect(...) dans le module sqlite3, et si la base de données représentée par le fichier n’existe pas, une sera créée à ce chemin.,

import sqlite3con = sqlite3.connect('/path/to/file/db.sqlite3')

Vous constaterez que dans la programmation de base de données quotidienne, vous créerez constamment des connexions à votre base de données, c’est donc une bonne idée d’envelopper cette simple instruction de connexion dans une fonction généralisée réutilisable.

création de Tables

pour créer des tables de base de données, vous devez avoir une idée de la structure des données que vous souhaitez stocker. Il existe de nombreuses considérations de conception qui entrent dans la définition des tables d’une base de données relationnelle, sur lesquelles des livres entiers ont été écrits., Je ne vais pas entrer dans les détails de cette pratique et je vais plutôt laisser au lecteur le soin d’enquêter davantage.

Cependant, pour faciliter notre discussion sur la programmation de base de données SQLite avec Python, je vais travailler sur le principe qu’une base de données doit être créée pour une librairie fictive qui a les données ci-dessous déjà collectées sur les ventes de livres.,

customer date product price
Alan Turing 2/22/1944 Introduction to Combinatorics 7.99
Donald Knuth 7/3/1967 A Guide to Writing Short Stories 17.99
Donald Knuth 7/3/1967 Data Structures and Algorithms 11.99
Edgar Codd 1/12/1969 Advanced Set Theory 16.,99

Lors de l’inspection de ces données, il est évident qu’il contient des informations sur les clients, les produits et les commandes. Un modèle courant dans la conception de base de données pour les systèmes transactionnels de ce type consiste à diviser les ordres en deux tables supplémentaires, les ordres et les éléments de ligne (parfois appelés détails des ordres) pour obtenir une plus grande normalisation.

Dans un interpréteur Python, dans le même répertoire que les db_utils.,PY module défini précédemment, entrez le SQL pour créer les tables clients et produits suit:

le code ci-dessus crée un objet de connexion puis l’utilise pour instancier un objet curseur. L’objet cursor est utilisé pour exécuter des instructions SQL sur la base de données SQLite.

avec le curseur créé, j’ai ensuite écrit le SQL pour créer la table customers, en lui donnant une clé primaire avec un champ de texte prénom et nom et en l’attribuant à une variable appeléecustomers_sql., J’appelle ensuite la méthodeexecute(...) de l’objet curseur en lui passant la variablecustomers_sql. Je crée ensuite une table de produits de la même manière.

Vous pouvez interroger la tablesqlite_master, une table de métadonnées SQLite intégrée, pour vérifier que les commandes ci-dessus ont réussi.

pour voir toutes les tables de la base de données actuellement connectée, interrogez la colonnenamede la tablesqlite_masteroùtype est égale à « table ».,

Pour obtenir un coup d’oeil au schéma des tables de requête de la balise sql colonne de la même table où le type est encore « table » et le name est égal à « clients » et/ou « produits ».

la table suivante à définir sera la table orders qui associe les clients aux commandes via une clé étrangère et la date de leur achat. Étant donné que SQLite ne prend pas en charge un type de données date/heure réel (ou une classe de données pour être cohérente avec la langue vernaculaire SQLite), toutes les dates seront représentées sous forme de valeurs de texte.,

le tableau final à définir sera le tableau des postes de ligne qui donne une comptabilité détaillée des produits dans chaque commande.

chargement des données

dans cette section, je vais montrer comment insérer nos exemples de données dans les tables qui viennent d’être créées. Un point de départ naturel serait de remplir d’abord le tableau des produits, car sans produits, nous ne pouvons pas avoir de vente et n’aurions donc pas les clés étrangères pour se rapporter aux articles et aux commandes., En regardant les exemples de données, je vois qu’il existe quatre produits:

le flux de travail pour l’exécution des instructions INSERT est simplement:

  1. Se connecter à la base de données
  2. créer un objet cursor
  3. écrire une instruction SQL insert paramétrée et stocker en tant que variable
  4. appeler la méthode execute sur l’objet cursor en lui passant la variable sql et les valeurs, en tant que tuple, à insérer dans la table

compte tenu de écrivez un peu plus de code.,

le code ci-dessus semble probablement assez évident, mais permettez-moi d’en discuter un peu car il se passe des choses importantes ici. L’instruction insert suit la syntaxe SQL standard à l’exception du bit ?. Les ?sont en fait des espaces réservés dans ce qu’on appelle une « requête paramétrée ».

les requêtes paramétrées sont une caractéristique importante de toutes les interfaces de base de données vers les langages de programmation modernes de haut niveau tels que le module sqlite3 en Python., Ce type de requête sert à améliorer l’efficacité des requêtes répétées plusieurs fois. Peut-être plus important encore, ils désinfectent également les entrées qui remplacent les espaces réservés ? qui sont transmis lors de l’appel à la méthode execute de l’objet cursor pour empêcher les entrées néfastes conduisant à L’injection SQL. Ce qui suit est une bande dessinée du populaire xkcd.com blog décrivant les dangers de L’injection SQL.

Pour remplir les tables restantes que nous allons suivre une évolution légèrement différente de changer un peu les choses., Le flux de travail pour chaque commande, identifié par une combinaison de nom et prénom du client et de la date d’achat, sera:

  1. insérez le nouveau client dans le tableau clients et récupérez son ID de clé primaire
  2. créez une entrée de commande basée sur l’id client et la date d’achat puis récupérez son id de clé primaire
  3. pour chaque produit de la commande déterminez son id de clé primaire et créez une entrée de ligne associant la commande et le produit

pour simplifier les choses sur nous-mêmes faisons un recherche rapide de tous nos produits., Pour l’instant, ne vous inquiétez pas trop de la mécanique de L’instruction SELECT SQL car nous y consacrerons une section sous peu.

la première commande a été passée le 22 février 1944 par Alan Turing qui a acheté Introduction to Combinatorics pour 7,99$.

commencez par créer un nouvel enregistrement client pour M. Turing puis déterminez son id de clé primaire en accédant au champlastrowid de l’objet cursor.

nous pouvons maintenant créer une entrée de commande, collecter la nouvelle valeur d’ID de commande et l’associer à une entrée de ligne avec le produit commandé par M. Turing.,

les enregistrements restants sont chargés exactement de la même manière, à l’exception de L’ordre passé à Donald Knuth, qui recevra deux entrées de ligne. Cependant, la nature répétitive d’une telle tâche pousse à la nécessité d’envelopper ces fonctionnalités dans des fonctions réutilisables. Dans le db_utils.py module ajouter le code suivant:

Awh, maintenant nous pouvons travailler avec une certaine efficacité!

vous devrezexit() votre interpréteur Python et le recharger pour que vos nouvelles fonctions deviennent accessibles dans l’interpréteur.,

je me sens obligé de donner un conseil supplémentaire en tant qu’étudiant de l’Artisanat logiciel. Lorsque vous vous trouvez à effectuer plusieurs manipulations de base de données (insertions dans ce cas) afin d’accomplir ce qui est en fait une tâche cumulative (c’est-à-dire, créer une commande), il est préférable d’envelopper les sous-tâches (création de client, commande, puis éléments de ligne) dans une seule transaction de base de données afin que vous puissiez,

cela ressemblerait à ceci:

je veux terminer cette section avec une démonstration rapide de la façon de mettre à jour un enregistrement existant dans la base de données. Mettons à jour le prix du Guide pour écrire des histoires courtes à 10.99 (en vente).

>>> update_sql = "UPDATE products SET price = ? WHERE id = ?">>> cur.execute(update_sql, (10.99, 2))

interroger la base de données

généralement, l’action la plus courante effectuée sur une base de données est une récupération de certaines des données qui y sont stockées via une instruction SELECT. Pour cette section, je vais montrer comment utiliser l’interface sqlite3 pour effectuer des requêtes de sélection simples.,

pour effectuer une requête MultiRow de base de la table customers, vous passez une instruction SELECT à la méthodeexecute(...) de l’objet cursor. Après cela, vous pouvez parcourir les résultats de la requête en appelant la méthode fetchall() du même objet curseur.

disons que vous souhaitez simplement récupérer un enregistrement de la base de données. Vous pouvez le faire en écrivant une requête plus spécifique, par exemple pour L’id de Donald Knuth de 2, et en la suivant en appelant fetchone() méthode de l’objet cursor.,

>>> cur.execute("SELECT id, first_name, last_name FROM customers WHERE id = 2")>>> result = cur.fetchone()>>> print(result)(2, 'Donald', 'Knuth')

de Voir comment la ligne individuelle de chaque résultat sous la forme d’un tuple? Bien que les tuples soient une structure de données pythonique très utile pour certains cas d’utilisation de la programmation, beaucoup de gens les trouvent un peu gênants lorsqu’il s’agit de la tâche de récupération de données. Il se trouve qu’il existe un moyen de représenter les données d’une manière peut-être plus flexible pour certains. Tout ce que vous devez faire est de définir la méthode row_factory de l’objet connection sur quelque chose de plus approprié tel que sqlite3.Row., Cela vous donnera la possibilité d’accéder aux éléments individuels d’une ligne par position ou valeur de mot clé.

Conclusion

dans cet article, j’ai donné une brève démonstration de ce que je pense être les caractéristiques et fonctionnalités les plus importantes de L’interface Python sqlite3 à la base de données SQLite à fichier unique légère fournie avec la plupart des installations Python., J’ai également essayé de donner quelques conseils concernant les meilleures pratiques en matière de programmation de base de données, mais je mets en garde le nouveau venu que les subtilités de la programmation de base de données sont généralement l’une des plus sujettes aux failles de sécurité au niveau de l’entreprise et que des connaissances supplémentaires sont nécessaires avant une