Python : utilisation d'une base de données MySQL

L'interface standard de base de données Python est Python DB-API. Python DB-API fournit aux développeurs une interface de programmation d'application de base de données.

L'interface de base de données Python prend en charge un très grand nombre de bases de données. Vous pouvez choisir la base de données qui convient à votre projet :

GadFly
mSQL
MySQL
PostgreSQL
Microsoft SQL Server 2000
Informix
Interbase
Oracle
Sybase

Vous pouvez visiterInterfaces et API de bases de données Pythonpour consulter la liste détaillée des bases de données prises en charge.

Pour différentes bases de données, vous devez télécharger différents modules DB API. Par exemple, si vous devez accéder à des bases de données Oracle et MySQL, vous devez télécharger les modules de base de données Oracle et MySQL.

DB-API est une spécification. Elle définit une série d'objets requis et de méthodes d'accès aux bases de données afin de fournir une interface d'accès cohérente pour une variété de systèmes de bases de données sous-jacents et un large éventail de programmes d'interface de bases de données.

L'API DB-API de Python implémente des interfaces pour la plupart des bases de données. Après l'avoir utilisée pour vous connecter à chaque base de données, vous pouvez manipuler toutes les bases de données de la même manière.

Processus d'utilisation de l'API DB-API de Python :

Importez le module de l'API.
Obtenez la connexion à la base de données.
Exécutez des instructions SQL et des procédures stockées.
Fermez la connexion à la base de données.

Qu'est-ce que MySQLdb ?

MySQLdb est une interface permettant à Python de se connecter à la base de données MySQL. Elle implémente la spécification Python Database API V2.0 et est construite sur l'API C de MySQL.

Comment installer MySQLdb ?

Afin d'écrire des scripts MySQL à l'aide de DB-API, vous devez vous assurer que MySQL est installé. Copiez le code suivant et exécutez-le :

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

Si la sortie après exécution est comme indiqué ci-dessous, cela signifie que vous n'avez pas installé le module MySQLdb :

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

Pour installer MySQLdb, veuillez visiterhttp://sourceforge.net/projects/mysql-python, (les plateformes Linux peuvent visiter :https://pypi.python.org/pypi/MySQL-python) À partir d'ici, vous pouvez choisir un paquet d'installation adapté à votre plateforme, divisé en fichiers binaires précompilés et en paquets d'installation à partir du code source.

Si vous choisissez la version de publication binaire, le processus d'installation peut essentiellement être terminé en suivant les invites d'installation. Si vous installez à partir du code source, vous devez vous placer dans le répertoire de premier niveau de la version de publication de MySQLdb et saisir les commandes suivantes :

$ gunzip MySQL-python-1.2.2.tar.gz
$ tar -xvf MySQL-python-1.2.2.tar
$ cd MySQL-python-1.2.2
$ python setup.py build
$ python setup.py install

Remarque :Veuillez vous assurer que vous disposez des privilèges root pour installer le module ci-dessus.

Connexion à la base de données

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

Vous avez créé la base de données TESTDB.
Dans la base de données TESTDB, vous avez créé la table EMPLOYEE.
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 définir les vôtres, ou utiliser directement le nom d'utilisateur root et son mot de passe. Pour l'autorisation des utilisateurs de la base de données MySQL, veuillez utiliser la commande GRANT.
Vous avez installé le module Python MySQLdb sur votre machine.
Si vous ne connaissez pas les instructions SQL, vous pouvez consulter notreTutoriel sur les bases de SQL

Exemple :

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

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# 使用execute方法执行SQL语句
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()

print "Database version : %s " % data

# 关闭数据库连接
db.close()

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

Database version : 5.0.45

Création de tables dans la base de données

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

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# 如果数据表已经存在使用 execute() 方法删除表。
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# 创建数据表SQL语句
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""

cursor.execute(sql)

# 关闭数据库连接
db.close()

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

L'exemple suivant insère des enregistrements dans la table EMPLOYEE en exécutant une instruction SQL INSERT :

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 插入语句
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# 关闭数据库连接
db.close()

L'exemple ci-dessus peut également être écrit sous la forme suivante :

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 插入语句
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES (%s, %s, %s, %s, %s )" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

# 关闭数据库连接
db.close()

Exemple :

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 à l'aide de la méthode fetchone() pour récupérer une seule donnée et de la méthode fetchall() pour récupérer plusieurs données.

fetchone():Cette méthode récupère l'ensemble de résultats de requête suivant. 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 il 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 est supérieur à 1000 :

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > %s" % (1000)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 获取所有记录列表
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # 打印结果
      print "fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
             (fname, lname, age, sex, income )
except:
   print "Error: unable to fetch data"

# 关闭数据库连接
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

L'opération de mise à jour sert à mettre à jour les données dans les tables de données. L'exemple suivant incrémente de 1 le champ AGE des enregistrements de la table EMPLOYEE dont le champ SEX vaut 'M' :

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 更新语句
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

# 关闭数据库连接
db.close()

Opération de suppression

L'opération de suppression sert à supprimer des données des tables de données. L'exemple suivant montre comment supprimer toutes les données de la table EMPLOYEE où AGE est supérieur à 20 :

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 删除语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交修改
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

# 关闭连接
db.close()

Exécution de transactions

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

Une transaction doit avoir quatre propriétés : atomicité, cohérence, isolation et durabilité. Ces quatre propriétés sont généralement appelées les 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 état cohérent. La cohérence est étroitement liée à l'atomicité.
Isolation. L'exécution d'une transaction ne peut pas être perturbée par d'autres transactions. Autrement dit, les opérations à l'intérieur d'une transaction et les données qu'elle utilise sont isolées des autres transactions concurrentes, et les transactions exécutées simultanément ne peuvent pas interférer les unes avec les autres.
Durabilité. La durabilité est aussi appelée permanence, ce qui signifie qu'une fois qu'une transaction est validée, ses modifications des données de la base de données doivent être permanentes. Les opérations ultérieures ou les défaillances ne doivent avoir aucun effet sur elle.

Python DB API 2.0 fournit deux méthodes pour les transactions : commit ou rollback.

Exemple :

# SQL删除记录语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 向数据库提交
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

Pour les bases de données qui prennent en charge les transactions, en programmation de bases de données Python, lorsqu'un curseur est établi, une transaction de base de données implicite commence 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 pour les opérations de base de données. Le tableau suivant répertorie ces erreurs et exceptions :

Exception	Description
Avertissement	Dé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.
Erreur	Toutes les classes d'erreur autres que les avertissements. Doit être une sous-classe de StandardError.
InterfaceError	Déclenchée lorsqu'une erreur du module d'interface de base de données lui-même (plutôt qu'une erreur de la base de données) se produit. Doit être une sous-classe de Error.
DatabaseError	Déclenchée lorsqu'une erreur liée à la base de données se produit. Doit être une sous-classe de Error.
DataError	Déclenchée lorsqu'une erreur de traitement des données se produit, par exemple : erreur de division par zéro, données hors limites, etc. Doit être une sous-classe de DatabaseError.
OperationalError	Désigne les erreurs qui ne relèvent pas du contrôle de l'utilisateur mais qui se produisent lors de l'exploitation de la base de données. Par exemple : déconnexion inattendue, 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'exploitation de la base de données. Doit être une sous-classe de DatabaseError.
IntegrityError	Erreurs liées à l'intégrité, telles qu'un échec de vérification de clé étrangère, etc. Doit être une sous-classe de DatabaseError.
InternalError	Erreurs 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.
ProgrammingError	Erreurs de programmation, telles qu'une table introuvable ou déjà existante, des erreurs de syntaxe dans les instructions SQL, un nombre incorrect de paramètres, etc. Doit être une sous-classe de DatabaseError.
NotSupportedError	Erreurs non prises en charge, désignant l'utilisation de fonctions ou d'API que la base de données ne prend pas en charge. Par exemple, utiliser la fonction .rollback() sur un objet de connexion, alors que la base de données ne prend pas en charge les transactions ou que les transactions ont été désactivées. Doit être une sous-classe de DatabaseError.

Autres extensions

PythonTutoriel avancé

Python : utilisation d'une base de données MySQL

Qu'est-ce que MySQLdb ?

Comment installer MySQLdb ?

Connexion à la base de données

Exemple :

Création de tables dans la base de données

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

Exemple :

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

Exemple :

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

Opération de suppression

Exécution de transactions

Exemple :

Gestion des erreurs