Tutoriel MySQL en Python : bien démarrer

Créer une nouvelle base de données MySQL avec Python

À partir de maintenant, vous aurez besoin de compétences SQL ; le cours Introduction to SQL de DataCamp est un excellent point de départ pour vous mettre à niveau. 

La manière classique de créer une base de données dans MySQL est la suivante : 

CREATE DATABASE book_ratings;

Pour exécuter la même requête en Python, vous devez créer une instance de l'objet cursor(). 

Depuis l'objet cursor(), vous pouvez passer des commandes SQL sous forme de chaîne à la méthode execute(), qui exécutera le code. 

Créons une base appelée « book_ratings »…

create_db = "CREATE DATABASE book_ratings"
with database.cursor() as cursor:
    cursor.execute(create_db)

Remarque : exécuter ce code plusieurs fois lèvera une erreur indiquant que la base de données existe déjà.

Vous pouvez lister les bases de données présentes sur un serveur comme suit :

show_existing_db = "SHOW DATABASES"

with database.cursor() as cursor: 
    cursor.execute(show_existing_db)
    for db in cursor:
        print(db)

"""
('book_ratings',)
('information_schema',)
('mysql',)
('performance_schema',)
('sakila',)
('sys',)
('world',)
"""

Le code ci-dessus affiche les noms de toutes les bases de données actuellement présentes sur notre serveur MySQL. Il y en a plusieurs, car le serveur MySQL génère automatiquement des bases pour exposer différentes métadonnées et paramètres du serveur.

Se connecter à une base existante

Supposons que vous ayez déjà créé une base de données, mais que vous ne vous soyez pas encore connecté au serveur ; comment vous connecter directement à cette base ?

Facile. 

Passez le nom de la base de données comme argument à la méthode connect() :

try:
    with connector.connect(
        host = "localhost",
        user = "root",
        password = PASSWORD,
        database = "book_ratings"
    ) as database:
       
        print(database})

except connector.Error as e:
    print(e)

"""
<mysql.connector.connection_cext.CMySQLConnection object at 0x0000016DD8C24970>
"""

Créer des tables

Les informations d'une base sont stockées dans une table. Une table est simplement un format servant à conserver un ensemble de données liées, constitué de colonnes et de lignes. 

Une base peut contenir plusieurs tables, même si elles ne sont pas liées entre elles. Vous pouvez aussi avoir une base relationnelle avec une seule table, comme nous allons le montrer dans cette section – approfondissez vos connaissances des bases relationnelles avec le cours Introduction to Relational Databases in SQL. 

La création de table suit une approche similaire à ce que vous avez déjà vu :

• Écrire une commande SQL sous forme de chaîne
• La passer à la méthode execute() de l'objet curseur.

L'instruction SQL ici est CREATE TABLE.

Créons une table pour les livres :

create_books_table = """
CREATE TABLE books(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(100),
    genre VARCHAR(100),
    release_year YEAR(4)
)
"""
# Create cursor object
with existing_database.cursor() as cursor:
    cursor.execute(create_books_table)
    existing_database.commit()

À la dernière ligne, vous remarquerez l'appel à commit() sur notre objet existing_database. C'est nécessaire, car le connecteur MySQL pour Python ne valide pas automatiquement les transactions par défaut. 

Chaque fois que vous modifiez des données d'une table, vous devez appeler la méthode commit() – en savoir plus sur commit() dans les docs MySQL. 

Autres éléments importants dans le code :

• NOT NULL : contrainte imposant qu'une colonne n'accepte pas de valeurs NULL. 
• PRIMARY KEY : valeur unique permettant d'identifier des lignes spécifiques d'une table. 
• AUTO_INCREMENT : génère automatiquement un numéro unique pour chaque nouvel enregistrement inséré. 

Nous pouvons vérifier que notre schéma a bien été créé à l'aide de l'instruction DESCRIBE puis en affichant tous les éléments via la méthode fetchall() de notre curseur…

describe_books = "DESCRIBE books"
with existing_database.cursor() as cursor:
    cursor.execute(describe_books)
    books_schema = cursor.fetchall()
    for column in books_schema:
        print(column)

"""
('id', b'int', 'NO', 'PRI', None, 'auto_increment')
('title', b'varchar(100)', 'YES', '', None, '')
('author', b'varchar(100)', 'YES', '', None, '')
('genre', b'varchar(100)', 'YES', '', None, '')
('release_year', b'year', 'YES', '', None, '')
"""

Insertion de données

Dans les sections précédentes, nous avons créé une table books dans notre base book_ratings. Pour l'instant, cette table est vide. La suite présente deux approches à votre disposition pour l'alimenter. 

Insérer un enregistrement unique

Il peut arriver que vous deviez insérer un seul enregistrement dans une table. C'est rare en production, mais c'est possible comme suit :

# INSERT SINGLE RECORD
insert_single_record = "INSERT INTO books (id, title, author, genre, release_year)\
    VALUES (%s, %s, %s, %s, %s)"
single_record = (
    "1", "Choose Yourself! Be Happy, Make Millions, Live the Dream", "James Altucher", "self-help", "2013"
    )

# Create cursor object
with existing_database.cursor() as cursor:
    cursor.execute(insert_single_record, single_record)
    existing_database.commit()

Insérer plusieurs enregistrements

La manière la plus courante d'insérer des données dans une table consiste à utiliser la méthode executemany(operation_to_perform, seq_of_params) de l'objet curseur : elle vous permet d'insérer plusieurs enregistrements en une seule fois. 

La structure de l'opération à réaliser reste la même ; seule la séquence de paramètres évolue. 

Voici à quoi cela ressemble en code :

multiple_records = [
    (
        "2",
        "Skip the Line: The 10,000 Experiments Rule and Other Surprising Advice for Reaching Your Goals",
        "James Altucher",
        "self-help",
        "2021"        
    ),
    (
        "3",
        "The Power of No: Because One Little Word Can Bring Health, Abundance, and Happiness",
        "James Altucher",
        "self-help",
        "2014"
    ),
    ]

with existing_database.cursor() as cursor:
    cursor.executemany(insert_multiple_records, multiple_records)
    existing_database.commit()

Sélectionner des données 

L'instruction SELECT permet de récupérer des lignes issues d'une ou plusieurs tables d'une base. Nous pouvons utiliser SELECT de deux façons :

1. Sélectionner des colonnes spécifiques d'une table.

select_specific_cols = "SELECT author, release_year FROM books"
with existing_database.cursor() as cursor:
    cursor.execute(select_specific_cols)

1. Renvoyer toutes les colonnes avec l'astérisque « * »

# Create cursor object
select_all_cols = "SELECT * FROM books"
with existing_database.cursor() as cursor:
    cursor.execute(select_all_cols)

Tri et filtrage des données 

Le tri et le filtrage des données permettent de retourner les résultats qui vous intéressent en imposant des critères précis aux enregistrements. 

Voici quelques exemples :

WHERE

L'instruction WHERE vous permet de filtrer les données selon une condition donnée. 

Par exemple, disons que nous voulons extraire le nom de l'auteur et le titre du livre pour tout ouvrage paru avant ou en 2010 :

condition = "SELECT author, title FROM books WHERE release_year <= 2010"
with existing_database.cursor() as cursor:
    cursor.execute(condition)

ORDER BY

Nous pouvons aussi utiliser l'instruction ORDER BY pour trier les résultats par ordre croissant (ASC) ou décroissant (DESC). Notez que, par défaut, l'ordre est croissant sauf indication contraire. 

Voici comment trier nos données par année de sortie en ordre décroissant :

order_by_year = "SELECT * FROM books ORDER BY release_year DESC"
with existing_database.cursor() as cursor:
    cursor.execute(order_by_year)

LIMIT

L'instruction LIMIT permet de spécifier le nombre d'enregistrements à retourner dans le jeu de résultats.

# Create cursor object
define_limit = "SELECT * FROM books LIMIT 3"
with existing_database.cursor() as cursor:
    cursor.execute(define_limit)

Le code ci-dessus limite le nombre d'enregistrements retournés à trois. 

Mise à jour et suppression des données

Il arrive que des données évoluent, ne soient plus utiles ou soient erronées. Dans cette section, vous verrez comment gérer ces cas avec des mises à jour et des suppressions. 

UPDATE

La requête UPDATE vous permet de modifier des valeurs existantes dans une table sans toucher à sa structure. 

Par exemple, vous pouvez devoir mettre à jour un enregistrement si quelqu'un change de nom ; voici ce que cela donnerait en code :

update_query = """
UPDATE
    books
SET
    author = "Big J"
WHERE
    author = "James Altucher"
"""
with existing_database.cursor() as cursor:
    cursor.execute(update_query)
    existing_database.commit()

DELETE

La requête DELETE permet de supprimer un enregistrement d'une table :

drop_record = "DELETE FROM books WHERE release_year <= 2000"
with existing_database.cursor() as cursor:
    cursor.execute(drop_record)
    existing_database.commit()

Dans ce code, nous supprimons tous les enregistrements dont l'année de sortie est inférieure ou égale à 2000. 

DROP TABLE

L'instruction DROP TABLE permet de supprimer définitivement une table d'une base. Ajouter IF EXISTS permet de gérer les cas où vous n'êtes pas sûr que la table à supprimer existe, mais c'est totalement optionnel. 

Supprimons la table que nous avons créée dans notre base book_ratings :

drop_table = "DROP TABLE IF EXISTS books"
with existing_database.cursor() as cursor:
    cursor.execute(drop_table)
    existing_database.commit()

Conclusion

Dans ce tutoriel, vous avez appris à établir une connexion à un serveur MySQL en Python, créer une nouvelle base de données, vous connecter à une base existante, créer une table et exécuter diverses opérations de requête sur une table MySQL depuis Python.  

Pour aller plus loin, consultez ces ressources :

• Introduction to Databases in Python 
• SQL Server, PostgreSQL, MySQL… Quelles différences ? Par où commencer ?
• Cours SQL pour débutants
• PostgreSQL vs. MySQL