Comment créer et manipuler des bases de données SQL avec Python

Python et SQL sont deux des langages les plus importants pour les analystes de données.

Dans cet article, je vais vous expliquer tout ce que vous devez savoir pour connecter Python et SQL.

Vous apprendrez comment extraire des données de bases de données relationnelles directement dans vos pipelines d'apprentissage automatique, stocker les données de votre application Python dans votre propre base de données ou tout autre cas d'utilisation que vous pourriez proposer.

Nous couvrirons ensemble :

• Pourquoi apprendre à utiliser Python et SQL ensemble ?
• Comment configurer votre environnement Python et MySQL Server
• Connexion au serveur MySQL en Python
• Création d'une nouvelle base de données
• Création de tables et de relations entre tables
• Remplir les tables avec des données
• Lecture des données
• Mise à jour des enregistrements
• Suppression d'enregistrements
• Création d'enregistrements à partir de listes Python
• Créer des fonctions réutilisables pour faire tout cela pour nous à l'avenir

Cela fait beaucoup de choses très utiles et très intéressantes. Allons-y !

Un petit mot avant de commencer : il existe un Jupyter Notebook contenant tout le code utilisé dans ce tutoriel disponible dans ce référentiel GitHub. Le codage est fortement recommandé !

La base de données et le code SQL utilisés ici proviennent de ma précédente série Introduction à SQL publiée sur Towards Data Science (contactez-moi si vous rencontrez des problèmes pour visualiser les articles et je peux vous envoyer un lien pour les voir gratuitement).

Si vous n'êtes pas familier avec SQL et les concepts derrière les bases de données relationnelles, je vous orienterais vers cette série (en plus, il y a bien sûr une énorme quantité de choses intéressantes disponibles ici sur freeCodeCamp !)

Pourquoi Python avec SQL ?

Pour les Data Analysts et les Data Scientists, Python présente de nombreux avantages. Une vaste gamme de bibliothèques open source en fait un outil incroyablement utile pour tout analyste de données.

Nous avons pandas, NumPy et Vaex pour l'analyse des données, Matplotlib, seaborn et Bokeh pour la visualisation, et TensorFlow, scikit-learn et PyTorch pour les applications d'apprentissage automatique (et bien d'autres encore).

Avec sa courbe d'apprentissage (relativement) facile et sa polyvalence, il n'est pas étonnant que Python soit l'un des langages de programmation à la croissance la plus rapide.

Donc, si nous utilisons Python pour l'analyse de données, cela vaut la peine de se demander : d'où viennent toutes ces données ?

Bien qu'il existe une grande variété de sources d'ensembles de données, dans de nombreux cas - en particulier dans les entreprises - les données seront stockées dans une base de données relationnelle. Les bases de données relationnelles constituent un moyen extrêmement efficace, puissant et largement utilisé pour créer, lire, mettre à jour et supprimer des données de toutes sortes.

Les systèmes de gestion de bases de données relationnelles (SGBDR) les plus largement utilisés - Oracle, MySQL, Microsoft SQL Server, PostgreSQL, IBM DB2 - utilisent tous le langage de requête structuré (SQL) pour accéder aux données et y apporter des modifications.

Notez que chaque SGBDR utilise une version légèrement différente de SQL, donc le code SQL écrit pour l'un ne fonctionnera généralement pas dans un autre sans modifications (normalement assez mineures). Mais les concepts, les structures et les opérations sont largement identiques.

Cela signifie que pour un analyste de données en activité, une solide compréhension de SQL est extrêmement importante. Savoir utiliser Python et SQL ensemble vous donnera encore plus d'avantages lorsqu'il s'agira de travailler avec vos données.

Le reste de cet article sera consacré à vous montrer exactement comment nous pouvons y parvenir.

Commencer

Exigences et installation

Pour coder avec ce didacticiel, vous aurez besoin de configurer votre propre environnement Python.

J'utilise Anaconda, mais il existe de nombreuses façons de procéder. Recherchez simplement sur Google "comment installer Python" si vous avez besoin d'aide supplémentaire. Vous pouvez également utiliser Binder pour coder avec le Jupyter Notebook associé.

Nous utiliserons MySQL Community Server car il est gratuit et largement utilisé dans l'industrie. Si vous utilisez Windows, ce guide vous aidera à vous installer. Voici également des guides pour les utilisateurs Mac et Linux (bien que cela puisse varier selon la distribution Linux).

Une fois ceux-ci configurés, nous devrons les faire communiquer entre eux.

Pour cela, nous devons installer la bibliothèque MySQL Connector Python. Pour ce faire, suivez les instructions, ou utilisez simplement pip :

pip install mysql-connector-python

Nous allons également utiliser des pandas, alors assurez-vous de l'avoir également installé.

pip install pandas

Importation de bibliothèques

Comme pour tout projet en Python, la toute première chose que nous voulons faire est d’importer nos bibliothèques.

Il est recommandé d'importer toutes les bibliothèques que nous allons utiliser au début du projet, afin que les personnes qui lisent ou révisent notre code sachent à peu près ce qui se passe afin qu'il n'y ait pas de surprises.

Pour ce tutoriel, nous n'utiliserons que deux bibliothèques : MySQL Connector et pandas.

import mysql.connector
from mysql.connector import Error
import pandas as pd

Nous importons la fonction Error séparément afin d'y accéder facilement pour nos fonctions.

Connexion au serveur MySQL

À ce stade, nous devrions avoir MySQL Community Server configuré sur notre système. Nous devons maintenant écrire du code en Python qui nous permet d'établir une connexion à ce serveur.

def create_server_connection(host_name, user_name, user_password):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password
        )
        print("MySQL Database connection successful")
    except Error as err:
        print(f"Error: '{err}'")

    return connection

Créer une fonction réutilisable pour un code comme celui-ci est la meilleure pratique, afin que nous puissions l'utiliser encore et encore avec un minimum d'effort. Une fois que ceci sera écrit, vous pourrez également le réutiliser dans tous vos projets à l’avenir, alors à l’avenir, vous en serez reconnaissant !

Parcourons cela ligne par ligne pour comprendre ce qui se passe ici :

La première ligne consiste à nommer la fonction (create_server_connection) et à nommer les arguments que cette fonction prendra (host_name, user_name et user_password).

La ligne suivante ferme toutes les connexions existantes afin que le serveur ne soit pas confondu avec plusieurs connexions ouvertes.

Ensuite, nous utilisons un bloc Python try-sauf pour gérer les erreurs potentielles. La première partie tente de créer une connexion au serveur à l'aide de la méthode mysql.connector.connect() en utilisant les détails spécifiés par l'utilisateur dans les arguments. Si cela fonctionne, la fonction imprime un petit message de réussite.

La partie except du bloc affiche l'erreur renvoyée par le serveur MySQL, dans le cas malheureux où il y aurait une erreur.

Enfin, si la connexion réussit, la fonction renvoie un objet de connexion.

Nous utilisons cela en pratique en attribuant la sortie de la fonction à une variable, qui devient alors notre objet de connexion. Nous pouvons ensuite lui appliquer d'autres méthodes (comme le curseur) et créer d'autres objets utiles.

connection = create_server_connection("localhost", "root", pw)

Cela devrait produire un message de réussite :

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

Maintenant que nous avons établi une connexion, notre prochaine étape consiste à créer une nouvelle base de données sur notre serveur.

Dans ce didacticiel, nous ne le ferons qu'une seule fois, mais encore une fois, nous l'écrirons comme une fonction réutilisable afin d'avoir une fonction utile et intéressante que nous pouvons réutiliser pour de futurs projets.

def create_database(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        print("Database created successfully")
    except Error as err:
        print(f"Error: '{err}'")

Cette fonction prend deux arguments, connection (notre objet de connexion) et query (une requête SQL que nous écrirons à l'étape suivante). Il exécute la requête sur le serveur via la connexion.

Nous utilisons la méthode curseur sur notre objet de connexion pour créer un objet curseur (MySQL Connector utilise un paradigme de programmation orienté objet, il existe donc de nombreux objets héritant des propriétés des objets parents).

Cet objet curseur possède des méthodes telles que exécuter, exécutermany (que nous utiliserons dans ce didacticiel) ainsi que plusieurs autres méthodes utiles.

Si cela peut aider, nous pouvons considérer l'objet curseur comme nous donnant accès au curseur clignotant dans une fenêtre de terminal du serveur MySQL.

Ensuite, nous définissons une requête pour créer la base de données et appelons la fonction :

Toutes les requêtes SQL utilisées dans ce didacticiel sont expliquées dans ma série de didacticiels Introduction à SQL, et le code complet peut être trouvé dans le Jupyter Notebook associé dans ce référentiel GitHub. Je ne fournirai donc pas d'explications sur ce que fait le code SQL dans ce didacticiel. Didacticiel.

Il s’agit peut-être de la requête SQL la plus simple possible. Si vous savez lire l’anglais, vous pouvez probablement comprendre ce qu’il fait !

L'exécution de la fonction create_database avec les arguments ci-dessus entraîne la création d'une base de données appelée « école » sur notre serveur.

Pourquoi notre base de données s'appelle-t-elle « école » ? Ce serait peut-être le bon moment pour examiner plus en détail exactement ce que nous allons implémenter dans ce didacticiel.

Notre base de données

À l'instar de ma série précédente, nous allons mettre en œuvre la base de données de l'International Language School - une école de formation linguistique fictive qui propose des cours de langue professionnels aux entreprises clientes.

Ce diagramme de relation d'entité (ERD) présente nos entités (enseignant, client, cours et participant) et définit les relations entre elles.

Toutes les informations sur ce qu'est un ERD et ce qu'il faut prendre en compte lors de sa création et de la conception d'une base de données peuvent être trouvées dans cet article.

Le code SQL brut, les exigences de la base de données et les données à insérer dans la base de données sont tous contenus dans ce référentiel GitHub, mais vous verrez également tout cela au cours de ce didacticiel.

Connexion à la base de données

Maintenant que nous avons créé une base de données dans MySQL Server, nous pouvons modifier notre fonction create_server_connection pour nous connecter directement à cette base de données.

Notez qu'il est possible - courant, en fait - d'avoir plusieurs bases de données sur un seul serveur MySQL, nous souhaitons donc nous connecter toujours et automatiquement à la base de données qui nous intéresse.

Nous pouvons procéder ainsi :

def create_db_connection(host_name, user_name, user_password, db_name):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=db_name
        )
        print("MySQL Database connection successful")
    except Error as err:
        print(f"Error: '{err}'")

    return connection

C'est exactement la même fonction, mais maintenant nous prenons un argument supplémentaire - le nom de la base de données - et le transmettons comme argument à la méthode connect().

Création d'une fonction d'exécution de requête

La fonction finale que nous allons créer (pour l'instant) est extrêmement vitale : une fonction d'exécution de requêtes. Cela va prendre nos requêtes SQL, stockées en Python sous forme de chaînes, et les transmettre à la méthode curseur.execute() pour les exécuter sur le serveur.

def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query successful")
    except Error as err:
        print(f"Error: '{err}'")

Cette fonction est exactement la même que notre fonction create_database précédente, sauf qu'elle utilise la méthode connection.commit() pour s'assurer que les commandes détaillées dans nos requêtes SQL sont implémentées.

Ce sera notre fonction phare, que nous utiliserons (aux côtés de create_db_connection) pour créer des tables, établir des relations entre ces tables, remplir les tables avec des données et mettre à jour et supprimer des enregistrements dans notre base de données.

Si vous êtes un expert SQL, cette fonction vous permettra d'exécuter toutes les commandes et requêtes complexes que vous pourriez avoir, directement à partir d'un script Python. Cela peut être un outil très puissant pour gérer vos données.

Création de tableaux

Nous sommes maintenant tous prêts à commencer à exécuter des commandes SQL sur notre serveur et à commencer à créer notre base de données. La première chose que nous voulons faire est de créer les tableaux nécessaires.

Commençons par notre table Enseignant :

create_teacher_table = """
CREATE TABLE teacher (
  teacher_id INT PRIMARY KEY,
  first_name VARCHAR(40) NOT NULL,
  last_name VARCHAR(40) NOT NULL,
  language_1 VARCHAR(3) NOT NULL,
  language_2 VARCHAR(3),
  dob DATE,
  tax_id INT UNIQUE,
  phone_no VARCHAR(20)
  );
 """

connection = create_db_connection("localhost", "root", pw, db) # Connect to the Database
execute_query(connection, create_teacher_table) # Execute our defined query

Tout d'abord, nous attribuons notre commande SQL (expliquée en détail ici) à une variable avec un nom approprié.

Dans ce cas, nous utilisons la notation triple guillemet de Python pour les chaînes multilignes pour stocker notre requête SQL, puis nous l'introduisons dans notre fonctionexecute_query pour l'implémenter.

Notez que ce formatage multiligne est uniquement destiné aux humains qui lisent notre code. Ni SQL ni Python ne s'en soucient si la commande SQL est répartie comme ceci. Tant que la syntaxe est correcte, les deux langues l'accepteront.

Cependant, pour le bénéfice des humains qui liront votre code (même si ce ne sera que vous dans le futur !), il est très utile de faire cela pour rendre le code plus lisible et compréhensible.

Il en va de même pour la CAPITALISATION des opérateurs en SQL. Il s'agit d'une convention largement utilisée qui est fortement recommandée, mais le logiciel réel qui exécute le code n'est pas sensible à la casse et traitera « CREATE TABLE professeur » et « créer une table professeur » comme des commandes identiques.

L'exécution de ce code nous donne nos messages de réussite. Nous pouvons également le vérifier dans le client de ligne de commande du serveur MySQL :

Super! Créons maintenant les tables restantes.

create_client_table = """
CREATE TABLE client (
  client_id INT PRIMARY KEY,
  client_name VARCHAR(40) NOT NULL,
  address VARCHAR(60) NOT NULL,
  industry VARCHAR(20)
);
 """

create_participant_table = """
CREATE TABLE participant (
  participant_id INT PRIMARY KEY,
  first_name VARCHAR(40) NOT NULL,
  last_name VARCHAR(40) NOT NULL,
  phone_no VARCHAR(20),
  client INT
);
"""

create_course_table = """
CREATE TABLE course (
  course_id INT PRIMARY KEY,
  course_name VARCHAR(40) NOT NULL,
  language VARCHAR(3) NOT NULL,
  level VARCHAR(2),
  course_length_weeks INT,
  start_date DATE,
  in_school BOOLEAN,
  teacher INT,
  client INT
);
"""


connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, create_client_table)
execute_query(connection, create_participant_table)
execute_query(connection, create_course_table)

Cela crée les quatre tables nécessaires à nos quatre entités.

Nous voulons maintenant définir les relations entre eux et créer une table supplémentaire pour gérer la relation plusieurs-à-plusieurs entre les tables des participants et des cours (voir ici pour plus de détails).

Nous procédons exactement de la même manière :

alter_participant = """
ALTER TABLE participant
ADD FOREIGN KEY(client)
REFERENCES client(client_id)
ON DELETE SET NULL;
"""

alter_course = """
ALTER TABLE course
ADD FOREIGN KEY(teacher)
REFERENCES teacher(teacher_id)
ON DELETE SET NULL;
"""

alter_course_again = """
ALTER TABLE course
ADD FOREIGN KEY(client)
REFERENCES client(client_id)
ON DELETE SET NULL;
"""

create_takescourse_table = """
CREATE TABLE takes_course (
  participant_id INT,
  course_id INT,
  PRIMARY KEY(participant_id, course_id),
  FOREIGN KEY(participant_id) REFERENCES participant(participant_id) ON DELETE CASCADE,
  FOREIGN KEY(course_id) REFERENCES course(course_id) ON DELETE CASCADE
);
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, alter_participant)
execute_query(connection, alter_course)
execute_query(connection, alter_course_again)
execute_query(connection, create_takescourse_table)

Nos tables sont maintenant créées, ainsi que les contraintes, les clés primaires et les relations de clé étrangère appropriées.

Remplir les tables

L'étape suivante consiste à ajouter des enregistrements aux tables. Encore une fois, nous utilisons execute_query pour introduire nos commandes SQL existantes dans le serveur. Commençons à nouveau par la table Enseignant.

pop_teacher = """
INSERT INTO teacher VALUES
(1,  'James', 'Smith', 'ENG', NULL, '1985-04-20', 12345, '+491774553676'),
(2, 'Stefanie',  'Martin',  'FRA', NULL,  '1970-02-17', 23456, '+491234567890'), 
(3, 'Steve', 'Wang',  'MAN', 'ENG', '1990-11-12', 34567, '+447840921333'),
(4, 'Friederike',  'Müller-Rossi', 'DEU', 'ITA', '1987-07-07',  45678, '+492345678901'),
(5, 'Isobel', 'Ivanova', 'RUS', 'ENG', '1963-05-30',  56789, '+491772635467'),
(6, 'Niamh', 'Murphy', 'ENG', 'IRI', '1995-09-08',  67890, '+491231231232');
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, pop_teacher)

Est-ce que ça marche? Nous pouvons vérifier à nouveau dans notre client de ligne de commande MySQL :

Maintenant, remplissons les tables restantes.

pop_client = """
INSERT INTO client VALUES
(101, 'Big Business Federation', '123 Falschungstraße, 10999 Berlin', 'NGO'),
(102, 'eCommerce GmbH', '27 Ersatz Allee, 10317 Berlin', 'Retail'),
(103, 'AutoMaker AG',  '20 Künstlichstraße, 10023 Berlin', 'Auto'),
(104, 'Banko Bank',  '12 Betrugstraße, 12345 Berlin', 'Banking'),
(105, 'WeMoveIt GmbH', '138 Arglistweg, 10065 Berlin', 'Logistics');
"""

pop_participant = """
INSERT INTO participant VALUES
(101, 'Marina', 'Berg','491635558182', 101),
(102, 'Andrea', 'Duerr', '49159555740', 101),
(103, 'Philipp', 'Probst',  '49155555692', 102),
(104, 'René',  'Brandt',  '4916355546',  102),
(105, 'Susanne', 'Shuster', '49155555779', 102),
(106, 'Christian', 'Schreiner', '49162555375', 101),
(107, 'Harry', 'Kim', '49177555633', 101),
(108, 'Jan', 'Nowak', '49151555824', 101),
(109, 'Pablo', 'Garcia',  '49162555176', 101),
(110, 'Melanie', 'Dreschler', '49151555527', 103),
(111, 'Dieter', 'Durr',  '49178555311', 103),
(112, 'Max', 'Mustermann', '49152555195', 104),
(113, 'Maxine', 'Mustermann', '49177555355', 104),
(114, 'Heiko', 'Fleischer', '49155555581', 105);
"""

pop_course = """
INSERT INTO course VALUES
(12, 'English for Logistics', 'ENG', 'A1', 10, '2020-02-01', TRUE,  1, 105),
(13, 'Beginner English', 'ENG', 'A2', 40, '2019-11-12',  FALSE, 6, 101),
(14, 'Intermediate English', 'ENG', 'B2', 40, '2019-11-12', FALSE, 6, 101),
(15, 'Advanced English', 'ENG', 'C1', 40, '2019-11-12', FALSE, 6, 101),
(16, 'Mandarin für Autoindustrie', 'MAN', 'B1', 15, '2020-01-15', TRUE, 3, 103),
(17, 'Français intermédiaire', 'FRA', 'B1',  18, '2020-04-03', FALSE, 2, 101),
(18, 'Deutsch für Anfänger', 'DEU', 'A2', 8, '2020-02-14', TRUE, 4, 102),
(19, 'Intermediate English', 'ENG', 'B2', 10, '2020-03-29', FALSE, 1, 104),
(20, 'Fortgeschrittenes Russisch', 'RUS', 'C1',  4, '2020-04-08',  FALSE, 5, 103);
"""

pop_takescourse = """
INSERT INTO takes_course VALUES
(101, 15),
(101, 17),
(102, 17),
(103, 18),
(104, 18),
(105, 18),
(106, 13),
(107, 13),
(108, 13),
(109, 14),
(109, 15),
(110, 16),
(110, 20),
(111, 16),
(114, 12),
(112, 19),
(113, 19);
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, pop_client)
execute_query(connection, pop_participant)
execute_query(connection, pop_course)
execute_query(connection, pop_takescourse)

Incroyable! Nous avons maintenant créé une base de données complète avec des relations, des contraintes et des enregistrements dans MySQL, en utilisant uniquement des commandes Python.

Nous avons parcouru cela étape par étape pour que cela reste compréhensible. Mais à ce stade, vous pouvez voir que tout cela pourrait très facilement être écrit dans un seul script Python et exécuté en une seule commande dans le terminal. Des trucs puissants.

Lecture des données

Nous disposons désormais d'une base de données fonctionnelle avec laquelle travailler. En tant que Data Analyst, vous êtes susceptible d’entrer en contact avec des bases de données existantes dans les organisations où vous travaillez. Il sera très utile de savoir comment extraire des données de ces bases de données afin qu'elles puissent ensuite être introduites dans votre pipeline de données Python. C'est sur cela que nous allons travailler ensuite.

Pour cela, nous aurons besoin d'une fonction supplémentaire, cette fois en utilisant curseur.fetchall() au lieu de curseur.commit(). Avec cette fonction, nous lisons les données de la base de données et n'apporterons aucune modification.

def read_query(connection, query):
    cursor = connection.cursor()
    result = None
    try:
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except Error as err:
        print(f"Error: '{err}'")

Encore une fois, nous allons implémenter cela d'une manière très similaire à execute_query. Essayons-le avec une simple requête pour voir comment cela fonctionne.

q1 = """
SELECT *
FROM teacher;
"""

connection = create_db_connection("localhost", "root", pw, db)
results = read_query(connection, q1)

for result in results:
  print(result)

Exactement ce à quoi nous nous attendons. La fonction fonctionne également avec des requêtes plus complexes, comme celle-ci impliquant un JOIN sur les tables cours et client.

q5 = """
SELECT course.course_id, course.course_name, course.language, client.client_name, client.address
FROM course
JOIN client
ON course.client = client.client_id
WHERE course.in_school = FALSE;
"""

connection = create_db_connection("localhost", "root", pw, db)
results = read_query(connection, q5)

for result in results:
  print(result)

Très agréable.

Pour nos pipelines de données et flux de travail en Python, nous souhaiterons peut-être obtenir ces résultats dans différents formats pour les rendre plus utiles ou prêts à être manipulés.

Passons en revue quelques exemples pour voir comment nous pouvons procéder.

Formatage de la sortie dans une liste

#Initialise empty list
from_db = []

# Loop over the results and append them into our list

# Returns a list of tuples
for result in results:
  result = result
  from_db.append(result)

Formatage de la sortie dans une liste de listes

# Returns a list of lists
from_db = []

for result in results:
  result = list(result)
  from_db.append(result)

Formatage de la sortie dans un DataFrame pandas

Pour les analystes de données utilisant Python, pandas est notre bel et vieil ami de confiance. Il est très simple de convertir la sortie de notre base de données en DataFrame, et à partir de là, les possibilités sont infinies !

# Returns a list of lists and then creates a pandas DataFrame
from_db = []

for result in results:
  result = list(result)
  from_db.append(result)


columns = ["course_id", "course_name", "language", "client_name", "address"]
df = pd.DataFrame(from_db, columns=columns)

J’espère que vous pourrez voir les possibilités qui s’offrent à vous ici. Avec seulement quelques lignes de code, nous pouvons facilement extraire toutes les données que nous pouvons gérer des bases de données relationnelles où elles se trouvent et les intégrer dans nos pipelines d'analyse de données de pointe. C'est vraiment utile.

Mise à jour des enregistrements

Lorsque nous maintenons une base de données, nous devrons parfois apporter des modifications aux enregistrements existants. Dans cette section, nous allons voir comment procéder.

Supposons que l'ILS soit informée que l'un de ses clients existants, la Big Business Federation, déménage ses bureaux au 23 Fingiertweg, 14534 Berlin. Dans ce cas, l'administrateur de la base de données (c'est nous !) devra apporter quelques modifications.

Heureusement, nous pouvons le faire avec notre fonctionexecute_query aux côtés de l'instruction SQL UPDATE.

update = """
UPDATE client 
SET address = '23 Fingiertweg, 14534 Berlin' 
WHERE client_id = 101;
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, update)

Notez que la clause WHERE est très importante ici. Si nous exécutons cette requête sans la clause WHERE, alors toutes les adresses de tous les enregistrements de notre table Client seraient mises à jour à 23 Fingiertweg. Ce n’est vraiment pas ce que nous cherchons à faire.

Notez également que nous avons utilisé "WHERE client_id=101" dans la requête UPDATE. Il aurait également été possible d'utiliser "WHERE client_name='Big Business Federation'" ou "WHERE adresse='123 Falschungstraße, 10999 Berlin'" ou encore "WHERE adresse LIKE '%Falschung%'".

L'important est que la clause WHERE nous permet d'identifier de manière unique le ou les enregistrements que nous souhaitons mettre à jour.

Suppression d'enregistrements

Il est également possible d'utiliser notre fonctionexecute_query pour supprimer des enregistrements, en utilisant DELETE.

Lorsque nous utilisons SQL avec des bases de données relationnelles, nous devons être prudents en utilisant l'opérateur DELETE. Ce n'est pas Windows, il n'y a pas de message « Êtes-vous sûr de vouloir supprimer ceci ? pop-up d'avertissement et il n'y a pas de bac de recyclage. Une fois que nous supprimons quelque chose, il disparaît vraiment.

Cela dit, nous avons parfois vraiment besoin de supprimer des éléments. Jetons donc un coup d'œil à cela en supprimant un cours de notre table Cours.

Tout d'abord rappelons quels cours nous avons.

Disons que le cours 20, « Fortgeschrittenes Russisch » (c'est-à-dire « Russe avancé » pour vous et moi), touche à sa fin, nous devons donc le supprimer de notre base de données.

À ce stade, vous ne serez pas du tout surpris de la façon dont nous procédons : enregistrez la commande SQL sous forme de chaîne, puis introduisez-la dans notre fonction de travail perform_query.

delete_course = """
DELETE FROM course 
WHERE course_id = 20;
"""

connection = create_db_connection("localhost", "root", pw, db)
execute_query(connection, delete_course)

Vérifions que cela a eu l'effet escompté :

Le « russe avancé » a disparu, comme nous nous y attendions.

Cela fonctionne également avec la suppression de colonnes entières à l'aide de DROP COLUMN et de tables entières à l'aide des commandes DROP TABLE, mais nous ne les aborderons pas dans ce didacticiel.

Cependant, n'hésitez pas à les expérimenter : peu importe si vous supprimez une colonne ou un tableau d'une base de données pour une école fictive, et c'est une bonne idée de vous familiariser avec ces commandes avant de passer à un environnement de production.

Oh CRUD

À ce stade, nous sommes désormais en mesure de terminer les quatre opérations majeures de stockage persistant des données.

Nous avons appris à :

• Créer - des bases de données, des tables et des enregistrements entièrement nouveaux
• Lire : extraire des données d'une base de données et stocker ces données dans plusieurs formats
• Mettre à jour - apporter des modifications aux enregistrements existants dans la base de données
• Supprimer - supprime les enregistrements qui ne sont plus nécessaires

Ce sont des choses incroyablement utiles à faire.

Avant de terminer les choses ici, nous avons encore une compétence très pratique à apprendre.

Création d'enregistrements à partir de listes

Nous avons vu lors du remplissage de nos tables que nous pouvions utiliser la commande SQL INSERT dans notre fonctionexecute_query pour insérer des enregistrements dans notre base de données.

Étant donné que nous utilisons Python pour manipuler notre base de données SQL, il serait utile de pouvoir prendre une structure de données Python (comme une liste) et de l'insérer directement dans notre base de données.

Cela pourrait être utile lorsque nous souhaitons stocker les journaux d'activité des utilisateurs sur une application de réseau social que nous avons écrite en Python, ou les entrées des utilisateurs dans un wiki que nous avons construit, par exemple. Il existe autant d’utilisations possibles que vous pouvez imaginer.  

Cette méthode est également plus sécurisée si notre base de données est ouverte à nos utilisateurs à tout moment, car elle permet de prévenir les attaques par injection SQL, qui peuvent endommager ou même détruire l'ensemble de notre base de données.

Pour ce faire, nous allons écrire une fonction en utilisant la méthodeexecutemany(), au lieu de la méthode plus simpleexecut() que nous avons utilisée jusqu'à présent.

def execute_list_query(connection, sql, val):
    cursor = connection.cursor()
    try:
        cursor.executemany(sql, val)
        connection.commit()
        print("Query successful")
    except Error as err:
        print(f"Error: '{err}'")

Maintenant que nous avons la fonction, nous devons définir une commande SQL (« sql ») et une liste contenant les valeurs que nous souhaitons entrer dans la base de données (« val »). Les valeurs doivent être stockées sous forme de liste de tuples, ce qui est un moyen assez courant de stocker des données en Python.

Pour ajouter deux nouveaux enseignants à la base de données, nous pouvons écrire du code comme celui-ci :

sql = '''
    INSERT INTO teacher (teacher_id, first_name, last_name, language_1, language_2, dob, tax_id, phone_no) 
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
    '''
    
val = [
    (7, 'Hank', 'Dodson', 'ENG', None, '1991-12-23', 11111, '+491772345678'), 
    (8, 'Sue', 'Perkins', 'MAN', 'ENG', '1976-02-02', 22222, '+491443456432')
]

Notez ici que dans le code 'sql', nous utilisons '%s' comme espace réservé pour notre valeur. La ressemblance avec l'espace réservé '%s' pour une chaîne en python est juste une coïncidence (et franchement, très déroutante), nous voulons utiliser '%s' pour tous les types de données (chaînes, entiers, dates, etc.) avec MySQL Python Connecteur.

Vous pouvez voir un certain nombre de questions sur Stackoverflow où quelqu'un est devenu confus et a essayé d'utiliser des espaces réservés '%d' pour les entiers parce qu'il est habitué à le faire en Python. Cela ne fonctionnera pas ici - nous devons utiliser un '%s' pour chaque colonne à laquelle nous voulons ajouter une valeur.

La fonctionexecutemany prend ensuite chaque tuple de notre liste 'val' et insère la valeur pertinente pour cette colonne à la place de l'espace réservé et exécute la commande SQL pour chaque tuple contenu dans la liste.

Cela peut être effectué pour plusieurs lignes de données, à condition qu'elles soient correctement formatées. Dans notre exemple, nous ajouterons simplement deux nouveaux enseignants, à titre indicatif, mais en principe nous pouvons en ajouter autant que nous le souhaitons.

Allons-y, exécutons cette requête et ajoutons les enseignants à notre base de données.

connection = create_db_connection("localhost", "root", pw, db)
execute_list_query(connection, sql, val)

Bienvenue à l'ILS, Hank et Sue !

Il s'agit d'une autre fonction très utile, nous permettant de prendre les données générées dans nos scripts et applications Python et de les saisir directement dans notre base de données.

Conclusion

Nous avons couvert beaucoup de sujets dans ce tutoriel.

Nous avons appris à utiliser Python et MySQL Connector pour créer une toute nouvelle base de données dans MySQL Server, créer des tables dans cette base de données, définir les relations entre ces tables et les remplir de données.

Nous avons expliqué comment créer, lire, mettre à jour et supprimer des données dans notre base de données.

Nous avons examiné comment extraire des données de bases de données existantes et les charger dans des DataFrames pandas, prêts pour l'analyse et des travaux ultérieurs en profitant de toutes les possibilités offertes par la pile PyData.

Dans l'autre sens, nous avons également appris à prendre les données générées par nos scripts et applications Python et à les écrire dans une base de données où elles peuvent être stockées en toute sécurité pour une récupération et une manipulation ultérieures.

J'espère que ce tutoriel vous a aidé à voir comment nous pouvons utiliser Python et SQL ensemble pour pouvoir manipuler les données encore plus efficacement !

Si vous souhaitez voir plus de mes projets et de mon travail, veuillez visiter mon site Web à l'adresse craigdoesdata.de. Si vous avez des commentaires sur ce tutoriel, veuillez me contacter directement – tous les commentaires seront chaleureusement reçus !