Accès aux bases de données (MySQL)¶
Python permet facilement d’interagir avec des systèmes de bases de données relationnelles (SGBDR). Cette facilité est en partie due à la définition d’une API standard d’accès aux bases de données décrite par la PEP 249 (Python Database API Specification v2.0).
Pour accéder à une base de données vous devrez installer avec le module pip
le package pour vous connecter au SGBDR de votre choix. La procédure de connexion
peut avoir des spécificités selon le SGBDR mais l’interaction avec la base reste
standard.
Pour ce chapitre nous prendrons comme exemple MySQL. Nous partirons du principe
qu’il existe un serveur MySQL actif sur la machine hôte et qu’il existe un
schéma de base de données appelé demo_python. Ce schéma contient une unique
table utilisateur créée avec le script suivant :
drop table if exists utilisateur;
create table utilisateur (
id int primary key auto_increment,
nom varchar(100) not null,
score int default 0,
date_inscription date not null default now(),
actif bool default FALSE
) engine=innoDB;
Installation du connecteur MySQL¶
Pour vous connecter à une base de données MySQL, vous devez installer le
connecteur MySQL qui est un paquet nommé mysql-connector-python et qui est
disponible sur PyPI.
$ python -m pip install mysql-connector-python
Il est fortement recommandé d’installer le connecteur dans un environnement virtuel Python pour éviter d’entrer en conflit avec d’autres projets qui utiliseraient déjà ce connecteur. Pour en savoir plus, consultez le chapitre consacré aux modules pip et venv.
Connexion à la base de données¶
Le module mysql.connector fournit la méthode connect qui permet de retourner
un objet qui représente la connexion vers la base de données. Vous devez
fournir les paramètres host, user et password pour donner l’adresse
du SGBDR, le login et le mot de passe de connexion. Vous pouvez également fournir
le paramètre database pour indiquer quelle base de données vous souhaitez
utiliser.
import mysql.connector
db = mysql.connector.connect(
host="localhost",
user="login",
password="mot_de_passe",
database="demo_python"
)
# faire quelque chose d'utile avec la connexion
db.close()
La connexion à la base de données se comporte comme un gestionnaire de ressource.
Cela signifie que nous pouvons employer la syntaxe with pour nous
assurer de fermer la connexion correctement. De plus, il est utile d’employer
l’opérateur ** pour faire un unpack de dictionnaire. Ainsi nous pouvons
représenter les paramètres de connexion comme un simple dictionnaire :
import mysql.connector
connection_params = {
'host': "localhost",
'user': "login",
'password': "mot_de_passe",
'database': "demo_python",
}
with mysql.connector.connect(**connection_params) as db :
# faire quelque chose d'utile avec la connexion
pass
L’interaction avec la base de données se fera à travers un curseur. Cet
objet permet à la fois d’envoyer des requêtes et de consulter les résultats
quand il s’agit d’une requête de consultation de données. Pour créer un
curseur, on appelle la méthode cursor() sur la connexion. Il est recommandé
de fermer un curseur lorsqu’il n’est plus utilisé. Comme un curseur est également
un gestionnaire de ressource, nous pouvons également employer la syntaxe
with :
import mysql.connector
connection_params = {
'host': "localhost",
'user': "login",
'password': "mot_de_passe",
'database': "demo_python",
}
with mysql.connector.connect(**connection_params) as db :
with db.cursor() as c:
# faire quelque chose d'utile avec le curseur
pass
Pour exécuter une requête, nous utilisons la méthode execute du curseur.
Insertion de données¶
L’insertion de données se fait avec une requête SQL de type insert.
1 2 3 4 5 | with mysql.connector.connect(**connection_params) as db :
with db.cursor() as c:
c.execute("insert into utilisateur (nom, score, actif) \
values ('david', 10, TRUE)")
db.commit()
|
Notez à la ligne 5, l’appel à la méthode commit() de la connexion qui permet
de valider les modifications. Si vous n’appelez pas cette méthode, la transaction
avec la base de données ne sera pas terminée et aucune ligne ne sera insérée
en base de données.
Note
Pour annuler toutes les modifications effectuées dans une transaction, vous
pouvez appeler la méthode rollback() de la connexion.
Note
Le connecteur MySQL fournit la propriété non standard autocommit sur
la connexion. Si cette propriété vaut True alors un commit est
automatiquement fait après chaque exécution de requête.
1 2 3 4 5 | with mysql.connector.connect(**connection_params) as db :
db.autocommit = True
with db.cursor() as c:
c.execute("insert into utilisateur (nom, score, actif) \
values ('david', 10, TRUE)")
|
Il est souvent plus utile de fournir les valeurs à insérer sous la forme de paramètres.
Il suffit de remplacer les valeurs par %s dans la requête et de passer en
deuxième paramètre à la méthodes execute un n-uplet avec les valeurs.
request = """insert into utilisateur
(nom, score, actif)
values (%s, %s, %s)"""
params = ("david", 10, True)
with mysql.connector.connect(**connection_params) as db :
with db.cursor() as c:
c.execute(request, params)
db.commit()
Remarquez que le curseur se débrouille pour convertir les types Python en types SQL et nous pouvons directement passer des chaînes de caractères, des nombres, des valeurs booléennes et même des dates.
import datetime
request = """insert into utilisateur
(nom, score, actif, date_inscription)
values (%s, %s, %s, %s)"""
params = ("david", 10, True, datetime.date.today())
with mysql.connector.connect(**connection_params) as db :
with db.cursor() as c:
c.execute(request, params)
db.commit()
La méthode executemany permet d’insérer plusieurs lignes en passant un tableau
pour la valeur des paramètres :
request = """insert into utilisateur
(nom, score, actif)
values (%s, %s, %s)"""
params = [
("david", 10, True),
("laurence", 15, True),
("julien", 12, False)
]
with mysql.connector.connect(**connection_params) as db :
with db.cursor() as c:
c.executemany(request, params)
db.commit()
Si vous voulez connaître le nombre de lignes insérées par l’exécution de la
requête, vous pouvez consulter la propriété rowcount du curseur :
request = """insert into utilisateur
(nom, score, actif)
values (%s, %s, %s)"""
params = [
("david", 10, True),
("laurence", 15, True),
("julien", 12, False)
]
with mysql.connector.connect(**connection_params) as db :
with db.cursor() as c:
c.executemany(request, params)
db.commit()
print("Nombre de lignes insérées :", c.rowcount)
Sélection de données¶
Pour récupérer des données depuis la base de données, il suffit de passer un
requête SQL de type select en paramètre de la méthode execute du curseur
et ensuite d’appeler la méthode fetchall() pour récupérer une liste de n-uplets
contenant les résultats.
request = "select id, nom, score, date_inscription, actif from utilisateur"
with mysql.connector.connect(**connection_params) as db :
with db.cursor() as c:
c.execute(request)
resultats = c.fetchall()
for utilisateur in resultats:
print(utilisateur)
L’exécution du code précédent affichera :
(1, 'david', 10, datetime.date(2021, 1, 17), 1)
(2, 'laurence', 15, datetime.date(2021, 1, 17), 1)
(3, 'julien', 12, datetime.date(2021, 1, 17), 0)
On voit que les résultats sont bien des n-uplets et que le connecteur MySQL a
réalisé une conversion de type sauf pour la colonne actif qui est un nombre
au lieu d’être une valeur booléenne. Cela n’est pas trop grave car nous avons vu
que True et False peuvent presque être considérés comme les nombres 1
et 0.
Note
La conversion imparfaite pour les valeurs booléennes ne vient pas de Python
mais de MySQL. En effet, le type BOOLEAN n’existe pas vraiment en MySQL,
il s’agit plus d’un alias pour une colonne de type entier.
Récupérer les données en flux¶
La méthode fetchall() est simple à utiliser mais peut poser un problème
de performance. En effet, cette méthode retourne une liste, cela signifie que
tous les résultats sont récupérés de la base de données pour être convertis
en n-uplets. S’il y a un nombre important de lignes dans le résultat de la requête,
cela signifie que la liste peut être très grande et avoir une empreinte mémoire
importante.
Parfois, on désire traiter directement la donnée retournée et il n’est pas
nécessaire de la stocker dans une liste. Nous pouvons améliorer notre code
en optant pour l’appel à la méthode fetchone(). Cette méthode retourne
un seul résultat sous la forme d’un n-uplet. Lorsqu’il n’y a plus de résultat
à lire, la méthode retourne None. Nous pouvons revoir notre code :
request = "select id, nom, score, date_inscription, actif from utilisateur"
with mysql.connector.connect(**connection_params) as db :
with db.cursor() as c:
c.execute(request)
while True:
utilisateur = c.fetchone()
if utilisateur is None:
break
print(utilisateur)
Le résultat du programme sera le même que précédemment sauf que les résultats sont extraits un à un et qu’aucune liste n’est créée.
Prudence
Si vous fermez un curseur avant d’avoir extrait tous les résultats, vous
obtiendrez une exception. N’utilisez pas la méthode fetchone() pour ne
retourner que le premier résultat, elle n’a pas été conçue pour cela. Son
rôle est de permettre de retourner tous les résultats d’une requête mais
un à un.
Si vous voulez limiter le nombre de résultats retournés par une requête,
utilisez l’instruction SQL limit :
select id, nom, score, date_inscription, actif from utilisateur limit 1
La méthode fetchmany() est un compromis entre fetchone() et fetchall().
En effet, si fetchall() peut avoir un impact sur la consommation mémoire,
fetchone() peut avoir un impact sur les performances car les aller-retour
entre le programme et la base de données ont un coût en terme de temps.
La méthode fetchmany() prend en paramètre le nombre maximum de résultats
qu’il faut aller chercher lors de cette appel. Lorsqu’il n’y a plus de résultat,
la méthode retourne une liste vide.
request = "select id, nom, score, date_inscription, actif from utilisateur"
with mysql.connector.connect(**connection_params) as db :
with db.cursor() as c:
c.execute(request)
while True:
resultats = c.fetchmany(10)
if not resultats:
break
for utilisateur in resultats:
print(utilisateur)
Si ce code paraît difficile à écrire, il est assez facile de créer une fonction pour simplifier une fois pour toute notre travail :
def fetch_from_database(cursor, request, callback, chunck_size=10):
"""
Utilise le cursor pour exécuter la requête SQL donnée par request.
callback est une méthode qui est appelée pour chaque résultat extrait.
Elle recevra en paramètre le tuple des valeurs des colonnes.
chunck_size indique le nombre max de résultats récupérés en une fois.
"""
cursor.execute(request)
while True:
resultats = c.fetchmany(chunck_size)
if not resultats:
break
for colonnes in resultats:
callback(colonnes)
Et le programme devient alors :
request = "select id, nom, score, date_inscription, actif from utilisateur"
with mysql.connector.connect(**connection_params) as db :
with db.cursor() as c:
fetch_from_database(c, request, print)
Utilisation de paramètres dans les conditions where¶
Comme pour les requêtes d’insertion, il est utile de spécifier des paramètres
dans une requête SELECT pour les valeurs d’une clause where.
Astuce
Ce mécanisme nous prémunit de l’injection SQL car la structure de la requête est fournie quelle que soit la valeur des paramètres.
Les paramètres sont spécifiés par %s dans la requête et leurs valeurs sont
données par un n-uplet passé comme second paramètre à la méthode execute :
request = "select id, nom, score, date_inscription, actif \
from utilisateur where nom = %s"
params = ("david",)
with mysql.connector.connect(**connection_params) as db :
with db.cursor() as c:
c.execute(request, params)
resultats = c.fetchall()
for utilisateur in resultats:
print(utilisateur)
Modification des données¶
Toutes les autres requêtes SQL de modification suivent le même principe que
les requêtes d’insertion. On utilise la méthode execute d’un curseur. Il
faut appeler la méthode commit() de la connexion pour valider la transaction.
Enfin, il est possible de passer des paramètres à la requête et de connaître
le nombre de lignes impactées par la requête grâce à la propriété rowcount.
request = "update utilisateur \
set actif = %s \
where nom = %s"
params = (False, "david")
with mysql.connector.connect(**connection_params) as db :
with db.cursor() as c:
c.execute(request, params)
db.commit()
print("Nombre de lignes mises à jour :", c.rowcount)