13 Python et les bases de données

Cette partie va présenter les principes de base de la connexion aux bases de données, à l’envoi de requêtes et au traitement des données retournées. Cette partie traite des bases de données relationnelles. Les outils tel que les ORMs ne sont pas abordés.

Le principe général indépendant de la base de données est :

Établir une connexion
Créer un curseur sur cette connexion
Attribuer une requête à ce curseur
Récupérer les données si il y a lieu
Fermer la connexion

13.1 Connexion à MariaDb

Cette partie illustre le principe précédent à l’aide d’une base MariaDb. Cet exemple est évidemment transposable directement à MySQL et plus généralement à tout système de gestion de bases de données (PostgreSql, Oracle…).

Nous aurons besoins d’un connecteur ou pilote. Pour MariaDb, qui est un fort de MySQL, il s’agit de celui de MySQL disponible par pip.

Le code suivant montre comment récupérer la version de MariaDb.

import MySQLdb

try:
    conn = MySQLdb.connect(host='localhost', user='test user',
                           passwd='test pass', db='test')
    cursor = conn.cursor()
    cursor.execute("SELECT VERSION()")
    row = cursor.fetchone()
    print('server version', row[0])

finally:
    if conn:
        conn.close()

Notez que cet exemple utiliser pour se connecter les paramètres host, user, passwd et db. Les trois premiers sont indispensables. Le dernier vous permet de vous connecter à une base de donnée. Vous pouvez l’omettre pour vous connecter au serveur de base de données en dehors de toute base. Dans ce contexte, vous pouvez effectuer les tâches d’administration tel que la création de tables.

Si vous vous connectez sur le serveur pour créer une base, pour utiliser cette base il faudra créer une nouvelle connexion. ## Connexion à SQLite

Pour les formations et l’apprentissage, il y a une base de données plus adaptée, SQLite. Ses caractéristiques sont :

Le pilote SQLite est disponible dans l’installation standard de Python. Il n’est pas nécessaire d’installer d’autres dépendances.
SQLite est une base de données fichier. Il n’y a pas de notion de base de donnée à laquelle se connecter ni d’utilisateurs.
Lors de la connexion à la base, si elle n’existe pas, elle est créée.
Il est possible de travailler avec une base en mémoire.

SQLite est une base que vous pouvez utiliser à chaque fois que vous avez besoin de gérer des données de manière relationnelle. En tant que base en mémoire, SQLite est la base à utiliser lors des tests.

Le code suivant montre comment récupérer la version de SQLite.

import sqlite3 as lite

con = lite.connect('testdb.db')

try:
    cursor = con.cursor()
    cursor.execute("SELECT SQLITE_VERSION()")
    row = cursor.fetchone()
    print('server version', row[0])

finally:
    if con:
        con.close()

Pour travailler avec une base en mémoire, il faut spécifier une chaine de caractères spécifique :

con = sqlite3.connect(":memory:")

13.2 Utilisation d’un Context Manager

Les connecteurs aux bases de données proposent des Context Managers. Celui-ci prends en charge la fermeture de la connexion. La contre-partie est que la connexion étant fermée à la sortie du bloc suivant le with, l’ensemble des traitements doit être réalisé dans ce bloc.

import sqlite3 as lite

con = lite.connect('testdb.db')

with con:
    cursor = con.cursor()
    cursor.execute('SELECT SQLITE_VERSION()')
    row = cursor.fetchone()
    print('server version', row[0])

13.3 Les requêtes

Les requêtes sont exécutées sur des curseurs. Une requête doit être une requête SQL valide.

13.3.1 Création de tables

Les requêtes de création de tables sont en principe prédéfinies. Elles consistent en une chaine de caractères que nous allons passer à la méthode execute() du curseur. La requête suivante crée une table dans la base SQLite.

cur = conn.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS trainings ("
            "subject TEXT NOT NULL, "
            "duration INT NOT NULL, "
            "seats INT NOT NULL")

13.3.2 Insertion de données : requêtes paramétrées

Les requêtes d’insertion et d’extraction nécessiteront des paramètres (insertion, filtre…). Ces paramètres ont une origine non fiable. Il est indispensable de s’assurer de la sécurité de la base de données en particulier face à l’injection SQL. Il faut alors utiliser la fonctionnalité des requêtes paramétrées. Pour cela, l’endroit de la requête SQL où un paramètre est attendu est marqué par un ?. Les paramètres attendus seront ajoutés à l’appel de la méthode exécuté.

title = "Python, l'essentiel"
duration = 5
seats = 12

cur.execute("INSERT INTO trainings VALUES (?, ?, ?)",
            (title, duration, seats))

Vous pouvez également utiliser les requêtes paramétrées avec une syntaxe de dictionnaire. Ci-dessous, un exemple avec une requête d’extraction.

cur.execute("SELECT * FROM trainings WHERE duration=:duration",
            {duration:5})

Utilisez toujours les requêtes paramétrées qui ont entre autre la fonction d’échapper tout caractère qui ferait parti d’une requête et ainsi limiter les risques d’injection SQL.

13.3.3 Extractions de données

Une requête d’extraction de données retourne des enregistrements. Un enregistrement n’est autre qu’une liste de données. Pour obtenir ces enregistrements, nous avons plusieurs méthodes à not disposition après l’exécution de la requête :

fetchone() : renvoi l’enregistrement suivant ou None si il n’y en a plus.
fetchmany([size]) : retourne un nombre d’enregistrements correspondant au paramètre size. En cas d’itération, size doit rester constant.
fetchall() : envoi tous les enregistrements correspondant à la requête sous la forme d’une liste (dans la limite du paramètre cursor.arraysize de la base de données

13.4 Les transactions

Un système de gestion de base de données qui est transactionnel doit respecter un ensemble de règles baptisé A.C.I.D. pour Actomicity, Coherence, Isolation et Durability.

Une transaction est un ensemble de modifications indivisible qui doit se réaliser en assurant la cohérence de la base de données en isolation des autres requêtes.

13.4.1 Illustrations par un exemple.

Soit un transfert de compte à compte où les comptes ne doivent pas basculer en négatif. Nous avons 2 écritures : un débit et un crédit. La première contrainte est donc de ne pas faire passer en négatif le compte à débiter. La démarche intuitive est de vérifier si le solde est suffisant et si oui, débiter le compte. Cependant, cela n’empêche pas un débit entre la consultation et le retrait effectif conduisant à un état incohérent de la base.

La bonne démarche consiste donc à effectuer l’écriture de débit puis à vérifier que le solde est toujours positif ou nul avant de continuer. Si ce n’est pas le cas, alors on informera la base d’annuler ces requêtes, c’est à dire cette transaction. Le système reviendra dans son état initial. Parce que la base gère les transactions en isolation (tant qu’elles ne sont pas validées, elles sont invisibles de l’extérieur), cette opération est sans conséquences sur les autres requêtes.

13.4.2 Mécanisme des transactions

Du point de vue de la base de données, une transaction débute par une commande BEGIN et se poursuit jusque’à une commande COMMIT qui valide l’ensemble des modifications ou ROLLBACK qui annule l’ensemble des modifications.

Du point de vue Python, la transaction va débuter avec la première requête de modification et se poursuit jusqu’à l’appel de la méthode commit() ou de la méthode rollback().

Les enregistrements concernés seront verrouillés le temps de la transaction. Il est donc important d’exécuter une transaction aussi vite que possible. Pour éviter un verrou de la base, celles-ci ont en général un mécanisme de Deadlock qui annule une transaction en cas d’inactivité après un certain délais. Dans le cas de SQLite, ce délais est de 5 secondes par défaut.

13.4.3 Transactions et exceptions

Transactions et exceptions sont deux mécanismes distincts. Il appartient au développeur de réaliser un rollback suite à une exception ou de déclencher une exception après un rollback. Si nécessaire, l’un et l’autre ne sont pas systématiques.

En reprenant l’exemple du compte bancaire, si le compte débité est négatif, le rollback annulera la transaction mais aucune information ne remonte au code appelant. Il sera nécessaire de lever une exception.

13.4.4 Les context managers et les transactions

Les objects connexion peuvent être utilisés comme des context managers si le pilote le permet (c’est le cas de SQLite ou PostgreSQL). Celui-ci déclenche automatiquement les commit et rollback. Dans le cas où une exception se produit, le contexte manager provoquera un rollback. Une sortie normale du context manager provoquera un commit.

En sortie du context manager, l’exception est toujours levée, c’est donc l’information que la transaction ne s’est pas achevée normalement. Elle doit donc être capturée.

Des exemples d’illustrations vous seront montrés en formation sur des exemples d’illustration.

13.5 Organisation du code

L’organisation du code (connexion, exécution des requêtes, traitement des données…) dépendra évidemment de votre application. Une manière d’organiser son code de connexion à une base de données est de créer une classe de type dao. Vous avez quelques exemples dans des modules du package training.projects (les modules se terminant par _db). Le principe général proposé dans ce code consiste à :

Établir la connexion dans le constructeur. Pour une connexion à une base SQLite, les tables sont également créées dans le constructeur.
Le destructeur s’occupe de fermer la connexion.
Des méthodes encapsulent les requêtes et le chargement des données.