Connexion à la base de données MySQL avec Python3 - Pilote PyMySQL

Cet article présente l'utilisation de Python3PyMySQLpour se connecter à la base de données et implémenter des opérations CRUD simples.

Qu'est-ce que PyMySQL ?

PyMySQL est une bibliothèque utilisée dans Python3.x pour se connecter à un serveur MySQL, tandis que Python2 utilise mysqldb.

PyMySQL suit la spécification Python Database API v2.0 et inclut une bibliothèque client MySQL pure-Python.

Installation de PyMySQL

Avant d'utiliser PyMySQL, nous devons nous assurer que PyMySQL est installé.

Adresse de téléchargement de PyMySQL :https://github.com/PyMySQL/PyMySQL。

S'il n'est pas encore installé, nous pouvons utiliser la commande suivante pour installer la dernière version de PyMySQL :

$ pip3 install PyMySQL

Si votre système ne prend pas en charge la commande pip, vous pouvez l'installer en utilisant les méthodes suivantes :

1. Utilisez la commande git pour télécharger et installer le paquet d'installation (vous pouvez également le télécharger manuellement) :

$ git clone https://github.com/PyMySQL/PyMySQL
$ cd PyMySQL/
$ python3 setup.py install

2. Si vous devez spécifier un numéro de version, vous pouvez utiliser la commande curl pour installer :

$ # X.X 为 PyMySQL 的版本号
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python3 setup.py install
$ # 现在你可以删除 PyMySQL* 目录

Remarque :Veuillez vous assurer que vous disposez des permissions root pour installer les modules ci-dessus.

Lors de l'installation, le message d'erreur "ImportError: No module named setuptools" peut apparaître, ce qui signifie que vous n'avez pas installé setuptools. Vous pouvez visiterhttps://pypi.python.org/pypi/setuptoolspour trouver les méthodes d'installation pour chaque système.

Exemple d'installation sur système Linux :

$ wget https://bootstrap.pypa.io/ez_setup.py
$ python3 ez_setup.py

Connexion à la base de données

Avant de vous connecter à la base de données, veuillez confirmer les points suivants :

  • Vous avez déjà créé la base de données TESTDB.
  • Vous avez déjà créé la table EMPLOYEE dans la base de données TESTDB.
  • Les champs de la table EMPLOYEE sont FIRST_NAME, LAST_NAME, AGE, SEX et INCOME.
  • Le nom d'utilisateur utilisé pour se connecter à la base de données TESTDB est "testuser", et le mot de passe est "test123". Vous pouvez les définir vous-même ou utiliser directement le nom d'utilisateur et le mot de passe root. Pour l'autorisation des utilisateurs de la base de données MySQL, veuillez utiliser la commande GRANT.
  • Sur votre machine, Python est déjà installé.pymysqlmodule.
  • Si vous n'êtes pas familier avec les instructions SQL, vous pouvez visiter notreTutoriel de base SQL

Exemple :

L'exemple suivant se connecte à la base de données TESTDB de MySQL :

Exemple (Python 3.0+)

#!/usr/bin/python3 import pymysql # Ouvrir la connexion à la base de données db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Utiliser la méthode cursor() pour créer un objet curseur cursor cursor = db.cursor() # Utiliser la méthode execute() pour exécuter la requête SQL cursor.execute("SELECT VERSION()") # Utiliser la méthode fetchone() pour obtenir une seule donnée. data = cursor.fetchone() print ("Version de la base de données : %s " % data) # Fermer la connexion à la base de données db.close()

La sortie de l'exécution du script ci-dessus est la suivante :

Database version : 5.5.20-log

Créer une table de base de données

Si la connexion à la base de données existe, nous pouvons utiliser la méthode execute() pour créer une table pour la base de données, comme indiqué ci-dessous pour créer la table EMPLOYEE :

Exemple (Python 3.0+)

#!/usr/bin/python3 import pymysql # Ouvrir la connexion à la base de données db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Utiliser la méthode cursor() pour créer un objet curseur cursor cursor = db.cursor() # Utiliser la méthode execute() pour exécuter du SQL, supprimer la table si elle existe cursor.execute("DROP TABLE IF EXISTS EMPLOYEE") # Utiliser une instruction préparée pour 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) # Fermer la connexion à la base de données db.close()

Opération d'insertion dans la base de données

L'exemple suivant utilise l'instruction SQL INSERT pour insérer des enregistrements dans la table EMPLOYEE :

Exemple (Python 3.0+)

#!/usr/bin/python3 import pymysql # Ouvrir la connexion à la base de données db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Utiliser la méthode cursor() pour obtenir un curseur d'opération cursor = db.cursor() # Instruction d'insertion SQL sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Mac', 'Mohan', 20, 'M', 2000)""" try: # Exécuter l'instruction SQL cursor.execute(sql) # Valider dans la base de données db.commit() except: # Annuler si une erreur se produit db.rollback() # Fermer la connexion à la base de données db.close()

L'exemple ci-dessus peut également s'écrire comme suit :

Exemple (Python 3.0+)

#!/usr/bin/python3 import pymysql # Ouvrir la connexion à la base de données db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Utiliser la méthode cursor() pour obtenir un curseur d'opération cursor = db.cursor() # Instruction d'insertion SQL sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \ LAST_NAME, AGE, SEX, INCOME) \ VALUES ('%s', '%s', %s, '%s', %s)" % \ ('Mac', 'Mohan', 20, 'M', 2000) try: # Exécuter l'instruction SQL cursor.execute(sql) # Exécuter l'instruction SQL db.commit() except: # Annuler en cas d'erreur db.rollback() # Fermer la connexion à la base de données db.close()

Le code suivant utilise des variables pour transmettre des paramètres aux instructions SQL :

..................................
user_id = "test123"
password = "password"

con.execute('insert into Login values( %s,  %s)' % \
             (user_id, password))
..................................

Opération de requête dans la base de données

Python interroge MySQL en utilisant la méthode fetchone() pour obtenir une seule donnée, et utilise la méthode fetchall() pour obtenir plusieurs données.

  • fetchone():Cette méthode récupère le prochain ensemble de résultats de requête. L'ensemble de résultats est un objet.
  • fetchall(): reçoit toutes les lignes de résultat renvoyées.
  • rowcount:Il s'agit d'un attribut en lecture seule, et renvoie le nombre de lignes affectées après l'exécution de la méthode execute().

Exemple :

Interroger toutes les données de la table EMPLOYEE où le champ salary (wage) est supérieur à 1000 :

Exemple (Python 3.0+)

#!/usr/bin/python3 import pymysql # Ouvrir la connexion à la base de données db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Utiliser la méthode cursor() pour obtenir un curseur d'opération cursor = db.cursor() # Instruction de requête SQL sql = "SELECT * FROM EMPLOYEE \ WHERE INCOME > %s" % (1000) try: # Exécuter l'instruction SQL cursor.execute(sql) # Obtenir une liste de tous les enregistrements results = cursor.fetchall() for row in results: fname = row[0] lname = row[1] age = row[2] sex = row[3] income = row[4] # Afficher le résultat print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \ (fname, lname, age, sex, income )) except: print ("Erreur : impossible de récupérer les données") # Fermer la connexion à la base de données db.close()

Le résultat de l'exécution du script ci-dessus est le suivant :

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

Opération de mise à jour de la base de données

Les opérations de mise à jour sont utilisées pour mettre à jour les données dans les tables de données. L'exemple suivant incrémente le champ AGE de 1 pour les enregistrements où SEX est 'M' dans la table TESTDB :

Exemple (Python 3.0+)

#!/usr/bin/python3 import pymysql # Ouvrir la connexion à la base de données db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Utiliser la méthode cursor() pour obtenir un curseur d'opération cursor = db.cursor() # Instruction de mise à jour SQL sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M') try: # Exécuter l'instruction SQL cursor.execute(sql) # Valider dans la base de données db.commit() except: # Annuler en cas d'erreur db.rollback() # Fermer la connexion à la base de données db.close()

Opération de suppression

Les opérations de suppression sont utilisées pour supprimer des données dans les tables de données. L'exemple suivant montre comment supprimer toutes les données de la table EMPLOYEE où AGE est supérieur à 20 :

Exemple (Python 3.0+)

#!/usr/bin/python3 import pymysql # Ouvrir la connexion à la base de données db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Utiliser la méthode cursor() pour obtenir un curseur d'opération cursor = db.cursor() # Instruction SQL de suppression sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20) try: # Exécuter l'instruction SQL cursor.execute(sql) # Valider les modifications db.commit() except: # Annuler en cas d'erreur db.rollback() # Fermer la connexion db.close()

Exécuter des transactions

Le mécanisme de transaction peut garantir la cohérence des données.

Une transaction doit avoir 4 attributs : atomicité, cohérence, isolation et durabilité. Ces quatre attributs sont généralement appelés caractéristiques ACID.

  • Atomicité. Une transaction est une unité de travail indivisible. Les opérations incluses dans une transaction sont soit toutes exécutées, soit aucune n'est exécutée.
  • Cohérence. Une transaction doit faire passer la base de données d'un état cohérent à un autre. La cohérence et l'atomicité sont étroitement liées.
  • Isolation. L'exécution d'une transaction ne peut pas être perturbée par d'autres transactions. Autrement dit, les opérations et les données utilisées dans une transaction sont isolées des transactions concurrentes, et les transactions concurrentes ne peuvent pas interférer les unes avec les autres.
  • Durabilité. Également appelée permanence, elle signifie qu'une fois qu'une transaction est validée, ses modifications apportées aux données de la base de données doivent être permanentes. Les opérations ou défaillances ultérieures ne doivent avoir aucun effet sur celles-ci.

La transaction de l'API Python DB 2.0 fournit deux méthodes : commit ou rollback.

Exemple

Exemple (Python 3.0+)

# Instruction SQL pour supprimer des enregistrements sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20) try: # Exécuter l'instruction SQL cursor.execute(sql) # Valider dans la base de données db.commit() except: # Annuler en cas d'erreur db.rollback()

Pour les bases de données qui prennent en charge les transactions, dans la programmation de bases de données en Python, lorsqu'un curseur est créé, une transaction de base de données implicite démarre automatiquement.

La méthode commit() valide toutes les opérations de mise à jour du curseur, et la méthode rollback() annule toutes les opérations du curseur courant. Chaque méthode démarre une nouvelle transaction.

Gestion des erreurs

L'API DB définit certaines erreurs et exceptions d'opérations sur base de données. Le tableau suivant répertorie ces erreurs et exceptions :

ExceptionDescription
AvertissementDéclenchée lorsqu'il y a un avertissement sérieux, par exemple lorsque les données insérées sont tronquées, etc. Doit être une sous-classe de StandardError.
ErreurToutes les autres classes d'erreur sauf les avertissements. Doit être une sous-classe de StandardError.
InterfaceErrorDéclenché lorsqu'une erreur se produit dans le module d'interface de base de données lui-même (plutôt qu'une erreur de base de données). Doit être une sous-classe de Error.
DatabaseErrorDéclenchée lorsqu'une erreur liée à la base de données se produit. Doit être une sous-classe de Error.
DataErrorDéclenché lorsqu'une erreur de traitement de données se produit, telle qu'une division par zéro, des données hors plage, etc. Doit être une sous-classe de DatabaseError.
OperationalErrorDésigne des erreurs qui ne sont pas contrôlées par l'utilisateur mais qui se produisent lors de l'utilisation de la base de données. Par exemple : déconnexion inattendue de la connexion, nom de base de données introuvable, échec du traitement des transactions, erreur d'allocation mémoire, et autres erreurs qui se produisent lors de l'utilisation de la base de données. Doit être une sous-classe de DatabaseError.
IntegrityErrorErreurs liées à l'intégrité, telles que les échecs de vérification des clés étrangères, etc. Doit être une sous-classe de DatabaseError.
InternalErrorErreurs internes de la base de données, telles qu'un curseur non valide, un échec de synchronisation des transactions, etc. Doit être une sous-classe de DatabaseError.
ProgrammingErrorErreurs de programme, telles qu'une table de données introuvable ou déjà existante, des erreurs de syntaxe d'instruction SQL, un nombre incorrect de paramètres, etc. Doit être une sous-classe de DatabaseError.
NotSupportedErrorErreur non prise en charge, désignant l'utilisation de fonctions ou d'API non prises en charge par la base de données. Par exemple : appeler la fonction .rollback() sur un objet de connexion lorsque la base de données ne prend pas en charge les transactions ou que la transaction a été fermée. Doit être une sous-classe de DatabaseError.

La structure d'héritage des exceptions est la suivante :

Exception
|__Warning
|__Error
   |__InterfaceError
   |__DatabaseError
      |__DataError
      |__OperationalError
      |__IntegrityError
      |__InternalError
      |__ProgrammingError
      |__NotSupportedError
Autres extensions