Python - Accès à la base de données MySQL

Le standard Python pour les interfaces de base de données est l'API DB Python. La plupart des interfaces de base de données Python adhèrent à cette norme.

Vous pouvez choisir la bonne base de données pour votre application. L'API de base de données Python prend en charge un large éventail de serveurs de base 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: Interfaces de base de données Python et API . Vous devez télécharger un module DB API distinct pour chaque base de données à laquelle vous devez accéder. Par exemple, si vous avez besoin d'accéder à une base de données Oracle ainsi qu'à une base de données MySQL, vous devez télécharger les modules de base de données Oracle et MySQL.

L'API DB fournit une norme minimale pour travailler avec des bases de données utilisant les structures et la syntaxe Python dans la mesure du possible. Cette API comprend les éléments suivants -

• Importation du module API.
• Acquérir une connexion avec la base de données.
• Émettre des instructions SQL et des procédures stockées.
• Fermer la connexion

Nous apprendrions tous les concepts en utilisant MySQL, alors parlons 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. Il implémente l'API de base de données Python v2.0 et est construit sur l'API MySQL C.

Comment installer MySQLdb?

Avant de continuer, assurez-vous que MySQLdb est installé sur votre machine. Tapez simplement ce qui suit dans votre script Python et exécutez-le -

#!/usr/bin/python

import MySQLdb

Si cela produit le résultat suivant, cela signifie que le module MySQLdb n'est pas installé -

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

Pour installer le module MySQLdb, utilisez la commande suivante -

For Ubuntu, use the following command -
$ sudo apt-get install python-pip python-dev libmysqlclient-dev
For Fedora, use the following command -
$ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc
For Python command prompt, use the following command -
pip install MySQL-python

Note - Assurez-vous que vous disposez des privilèges root pour installer le module ci-dessus.

Connexion à la base de données

Avant de vous connecter à une base de données MySQL, assurez-vous des points suivants -

• Vous avez créé une base de données TESTDB.

• Vous avez créé une table EMPLOYEE dans TESTDB.

• Cette table contient les champs FIRST_NAME, LAST_NAME, AGE, SEX et INCOME.

• L'ID utilisateur "testuser" et le mot de passe "test123" sont définis pour accéder à TESTDB.

• Le module Python MySQLdb est correctement installé sur votre machine.

• Vous avez parcouru le didacticiel MySQL pour comprendre les bases de MySQL.

Exemple

Voici l'exemple de connexion avec la base de données MySQL "TESTDB"

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print "Database version : %s " % data

# disconnect from server
db.close()

Lors de l'exécution de ce script, il produit le résultat suivant sur ma machine Linux.

Database version : 5.0.45

Si une connexion est établie avec la source de données, un objet de connexion est renvoyé et enregistré dans db pour une utilisation ultérieure, sinon dbest défini sur Aucun. Prochain,db objet est utilisé pour créer un cursorobjet, qui à son tour est utilisé pour exécuter des requêtes SQL. Enfin, avant de sortir, il s'assure que la connexion à la base de données est fermée et que les ressources sont libérées.

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

Une fois la connexion à la base de données établie, nous sommes prêts à créer des tables ou des enregistrements dans les tables de la base de données en utilisant execute méthode du curseur créé.

Exemple

Créons la table de base de données EMPLOYÉ -

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Create table as per requirement
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""

cursor.execute(sql)

# disconnect from server
db.close()

Opération INSERT

Il est nécessaire lorsque vous souhaitez créer vos enregistrements dans une table de base de données.

Exemple

L'exemple suivant exécute l'instruction SQL INSERT pour créer un enregistrement dans la table EMPLOYEE -

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

L'exemple ci-dessus peut être écrit comme suit pour créer des requêtes SQL de manière dynamique -

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

Exemple

Le segment de code suivant est une autre forme d'exécution où vous pouvez passer des paramètres directement -

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

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

LIRE l'opération

LIRE L'opération sur n'importe quelle base de données signifie récupérer des informations utiles dans la base de données.

Une fois notre connexion à la base de données établie, vous êtes prêt à effectuer une requête dans cette base de données. Vous pouvez utiliser soitfetchone() méthode pour récupérer un seul enregistrement ou fetchall() méthode pour récupérer plusieurs valeurs à partir d'une table de base de données.

• fetchone()- Il récupère la ligne suivante d'un jeu de résultats de requête. Un jeu de résultats est un objet renvoyé lorsqu'un objet curseur est utilisé pour interroger une table.

• fetchall()- Il récupère toutes les lignes d'un jeu de résultats. Si certaines lignes ont déjà été extraites de l'ensemble de résultats, il récupère les lignes restantes de l'ensemble de résultats.

• rowcount - Il s'agit d'un attribut en lecture seule et renvoie le nombre de lignes affectées par une méthode execute ().

Exemple

La procédure suivante interroge tous les enregistrements de la table EMPLOYEE ayant un salaire supérieur à 1000 -

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > '%d'" % (1000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # Now print fetched result
      print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
             (fname, lname, age, sex, income )
except:
   print "Error: unable to fecth data"

# disconnect from server
db.close()

Cela produira le résultat suivant -

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

Opération de mise à jour

UPDATE L'opération sur n'importe quelle base de données signifie mettre à jour un ou plusieurs enregistrements, qui sont déjà disponibles dans la base de données.

La procédure suivante met à jour tous les enregistrements ayant SEX comme 'M'. Ici, nous augmentons l'âge de tous les hommes d'un an.

Exemple

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to UPDATE required records
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
                          WHERE SEX = '%c'" % ('M')
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

Opération DELETE

L'opération DELETE est requise lorsque vous souhaitez supprimer certains enregistrements de votre base de données. Voici la procédure pour supprimer tous les enregistrements de EMPLOYÉ où AGE est plus de 20 -

Exemple

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

Effectuer des transactions

Les transactions sont un mécanisme qui garantit la cohérence des données. Les transactions ont les quatre propriétés suivantes -

• Atomicity - Soit une transaction se termine, soit rien ne se passe du tout.

• Consistency - Une transaction doit démarrer dans un état cohérent et laisser le système dans un état cohérent.

• Isolation - Les résultats intermédiaires d'une transaction ne sont pas visibles en dehors de la transaction en cours.

• Durability - Une fois qu'une transaction a été validée, les effets sont persistants, même après une panne du système.

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

Exemple

Vous savez déjà comment mettre en œuvre des transactions. Voici à nouveau un exemple similaire -

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

Opération COMMIT

Valider est l'opération qui donne un signal vert à la base de données pour finaliser les modifications, et après cette opération, aucune modification ne peut être annulée.

Voici un exemple simple à appeler commit méthode.

db.commit()

Fonctionnement ROLLBACK

Si vous n'êtes pas satisfait d'une ou de plusieurs modifications et que vous souhaitez annuler complètement ces modifications, utilisez rollback() méthode.

Voici un exemple simple à appeler rollback() méthode.

db.rollback()

Déconnexion de la base de données

Pour déconnecter la connexion à la base de données, utilisez la méthode close ().

db.close()

Si la connexion à une base de données est fermée par l'utilisateur avec la méthode close (), toutes les transactions en cours sont annulées par la base de données. Cependant, au lieu de dépendre de l'un des détails d'implémentation de niveau inférieur de la base de données, votre application ferait mieux d'appeler explicitement commit ou rollback.

Traitement des erreurs

Il existe de nombreuses sources d'erreurs. Quelques exemples sont une erreur de syntaxe dans une instruction SQL exécutée, un échec de connexion ou l'appel de la méthode fetch pour un descripteur d'instruction déjà annulé ou terminé.

L'API DB définit un certain nombre d'erreurs qui doivent exister dans chaque module de base de données. Le tableau suivant répertorie ces exceptions.

N ° Sr.	Exception et description
1	Warning Utilisé pour les problèmes non fatals. Doit sous-classe StandardError.
2	Error Classe de base pour les erreurs. Doit sous-classe StandardError.
3	InterfaceError Utilisé pour les erreurs dans le module de base de données, pas dans la base de données elle-même. Doit sous-classe Error.
4	DatabaseError Utilisé pour les erreurs dans la base de données. Doit sous-classe Error.
5	DataError Sous-classe de DatabaseError qui fait référence à des erreurs dans les données.
6	OperationalError Sous-classe de DatabaseError qui fait référence à des erreurs telles que la perte d'une connexion à la base de données. Ces erreurs sont généralement hors du contrôle du scripteur Python.
sept	IntegrityError Sous-classe de DatabaseError pour les situations qui endommageraient l'intégrité relationnelle, telles que les contraintes d'unicité ou les clés étrangères.
8	InternalError Sous-classe de DatabaseError qui fait référence à des erreurs internes au module de base de données, comme un curseur qui n'est plus actif.
9	ProgrammingError Sous-classe de DatabaseError qui fait référence à des erreurs telles qu'un nom de table incorrect et d'autres choses qui peuvent vous être imputées en toute sécurité.
dix	NotSupportedError Sous-classe de DatabaseError qui fait référence à la tentative d'appeler une fonctionnalité non prise en charge.

Vos scripts Python doivent gérer ces erreurs, mais avant d'utiliser l'une des exceptions ci-dessus, assurez-vous que votre MySQLdb prend en charge cette exception. Vous pouvez obtenir plus d'informations à leur sujet en lisant la spécification DB API 2.0.