Accès à la base de données MySQL en Python : Utilisation de l'API DB-API standard

Le standard Python pour les interfaces de base de données est le Python DB-API. La plupart des interfaces de base de données Python respectent ce standard.

Vous pouvez choisir la base de données adaptée à votre application. L'API Python Database supporte une large gamme de serveurs de bases de données tels que :

GadFly
mSQL
MySQL
PostgreSQL
Microsoft SQL Server 2000
Informix
Interbase
Oracle
Sybase

Voici la liste des interfaces de base de données Python disponibles : Python Database Interfaces and APIs. Vous devez télécharger un module DB API séparé pour chaque base de données à laquelle vous devez accéder. Par exemple, pour accéder à une base Oracle et une base MySQL, vous devez télécharger les deux modules.

Le DB API fournit un standard minimal pour travailler avec des bases de données en utilisant les structures et la syntaxe Python où que possible. Cette API inclut :

Importation du module API.
Acquisition d'une connexion avec la base de données.
Exécution d'instructions SQL et de procédures stockées.
Fermeture de la connexion.

Nous apprendrons tous les concepts en utilisant MySQL, parlons donc du module MySQLdb.

Qu'est-ce que MySQLdb ?

MySQLdb est une interface pour se connecter à un serveur de base de données MySQL depuis Python. Elle implémente l'API Python Database v2.0 et est construite sur l'API C de MySQL.

Comment installer MySQLdb ?

Avant de continuer, assurez-vous que MySQLdb est installé. Tapez ceci dans votre script Python et exécutez-le :

#!/usr/bin/python
import MySQLdb

Si vous obtenez l'erreur suivante, le module n'est pas installé :

Traceback (most recent call last):
  File "test.py", line 3, in 
    import MySQLdb
ImportError: No module named MySQLdb

Pour installer MySQLdb :

# Ubuntu
$ sudo apt-get install python-pip python-dev libmysqlclient-dev

# Fedora
$ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc

# Invite de commande Python
pip install MySQL-python

Note : Assurez-vous d'avoir les privilèges root pour installer le module.

Connexion à la base de données

Avant de vous connecter à une base MySQL, vérifiez que :

Vous avez créé une base de données TESTDB.
Vous avez créé une table EMPLOYEE dans TESTDB.
Cette table a les champs FIRST_NAME, LAST_NAME, AGE, SEX et INCOME.
L'identifiant "testuser" et le mot de passe "test123" donnent accès à TESTDB.
Le module Python MySQLdb est correctement installé.
Vous avez suivi le tutoriel MySQL pour comprendre les bases.

Exemple

Connexion à la base de données MySQL "TESTDB" :

#!/usr/bin/python
import MySQLdb

# Ouvrir la connexion
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# Préparer un objet curseur avec la méthode cursor()
cursor = db.cursor()

# Exécuter une requête SQL avec execute()
cursor.execute("SELECT VERSION()")

# Récupérer une seule ligne avec fetchone()
data = cursor.fetchone()
print("Database version : %s" % data)

# Se déconnecter du serveur
db.close()

Résultat typique :

Database version : 5.0.45

Si la connexion est établie, un objet Connection est retourné et stocké dans db, sinon db vaut None. L'objet db crée un objet cursor pour exécuter les requêtes SQL. Enfin, on s'assure que la connexion est fermée et les ressources libérées.

Création d'une table de base de données

Une fois la connexion établie, nous sommes prêts à créer des tables ou des enregistrements avec la méthode execute du curseur créé.

Exemple

Création de la table EMPLOYEE :

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()

# Supprimer la table si elle existe
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Créer la table
sql = """CREATE TABLE EMPLOYEE (
    FIRST_NAME CHAR(20) NOT NULL,
    LAST_NAME CHAR(20),
    AGE INT,
    SEX CHAR(1),
    INCOME FLOAT )"""

cursor.execute(sql)
db.close()

Opération INSERT

Nécessaire pour créer des enregistrements dans une table de base de données.

Exemple

Exécution d'une instruction SQL INSERT pour créer un enregistrement dans EMPLOYEE :

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()

# Préparer la requête SQL INSERT
sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

db.close()

Version avec construction dynamique de requêtes :

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()

sql = "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d')" % \
       ('Mac', 'Mohan', 20, 'M', 2000)

try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

db.close()

Exemple : Passage direct de paramètres

user_id = "test123"
password = "password"
con.execute('insert into Login values("%s", "%s")' % (user_id, password))

Opération READ

Récupérer des informations utiles depuis la base de données.

Une fois la connexion établie, vous pouvez interroger la base. Utilisez fetchone() pour un seul enregistrement ou fetchall() pour plusieurs valeurs.

fetchone() : Récupère la ligne suivante d'un ensemble de résultats. Un ensemble de résultats est un objet retourné quand un curseur interroge une table.
fetchall() : Récupère toutes les lignes d'un ensemble de résultats. Si certaines lignes ont déjà été extraites, récupère les restantes.
rowcount : Attribut en lecture seule retournant le nombre de lignes affectées par execute().

Exemple

Requête de tous les enregistrements de EMPLOYEE avec salaire > 1000 :

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()

sql = "SELECT * FROM EMPLOYEE WHERE INCOME > '%d'" % (1000)

try:
    cursor.execute(sql)
    results = cursor.fetchall()
    for row in results:
        fname = row[0]
        lname = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        print("fname=%s,lname=%s,age=%d,sex=%s,income=%d" % (fname, lname, age, sex, income))
except:
    print("Error: unable to fetch data")

db.close()

Résultat :

fname=Mac,lname=Mohan,age=20,sex=M,income=2000

Opération UPDATE

Mettre à jour un ou plusieurs enregistrements existants dans la base.

Procédure pour mettre à jour tous les enregistrements avec SEX = 'M' : augmenter AGE de 1 an pour tous les hommes.

Exemple

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()

sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')

try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

db.close()

Opération DELETE

Supprimer des enregistrements de la base. Procédure pour supprimer tous les enregistrements de EMPLOYEE où AGE > 20 :

Exemple

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()

sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)

try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

db.close()

Exécution de transactions

Les transactions assurent la cohérence des données. Elles ont quatre propriétés (ACID) :

Atomicité : Soit la transaction se termine complètement, soit rien ne se passe.
Cohérence : Une transaction doit commencer dans un état cohérent et laisser le système dans un état cohérent.
Isolation : Les résultats intermédiaires ne sont pas visibles hors de la transaction courante.
Durabilité : Une fois validée, les effets persistent même après panne système.

L'API Python DB 2.0 fournit deux méthodes pour commit ou rollback une transaction.

Exemple

sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

Opération COMMIT

Valide les changements définitivement. Après, aucun retour en arrière n'est possible.

db.commit()

Opération ROLLBACK

Annule les changements si vous n'êtes pas satisfait.

db.rollback()

Déconnexion de la base de données

Utilisez close() pour fermer la connexion.

db.close()

Si la connexion est fermée par l'utilisateur avec close(), les transactions en cours sont annulées par la base. Toutefois, il est préférable d'appeler explicitement commit ou rollback.

Gestion des erreurs

Sources d'erreurs : erreur de syntaxe SQL, échec de connexion, appel de fetch sur un handle annulé ou terminé.

Le DB API définit des exceptions qui doivent exister dans chaque module de base de données :

N°	Exception et description
1	Warning Problèmes non fatals. Doit hériter de StandardError.
2	Error Classe de base pour les erreurs. Doit hériter de StandardError.
3	InterfaceError Erreurs dans le module base de données, pas la base elle-même. Hérite de Error.
4	DatabaseError Erreurs dans la base de données. Hérite de Error.
5	DataError Sous-classe de DatabaseError pour erreurs dans les données.
6	OperationalError Sous-classe de DatabaseError : perte de connexion, etc. Généralement hors contrôle du scripteur.
7	IntegrityError Sous-classe de DatabaseError : contraintes d'unicité, clés étrangères.
8	InternalError Sous-classe de DatabaseError : erreurs internes au module (curseur inactif).
9	ProgrammingError Sous-classe de DatabaseError : mauvais nom de table, etc. Responsabilité du développeur.
10	NotSupportedError Sous-classe de DatabaseError : tentative d'appeler une fonctionnalité non supportée.

Vos scripts doivent gérer ces erreurs. Avant d'utiliser ces exceptions, vérifiez que MySQLdb les supporte. Consultez la spécification DB API 2.0 pour plus de détails.

Maîtriser les expressions régulières en Python : Guide pratique Programmation réseau en Python : Maîtriser les sockets et protocoles avancés

Python