Manejo de la base de datos MySQL con Python

La interfaz estándar de base de datos de Python es Python DB-API, que proporciona a los desarrolladores una interfaz de programación de aplicaciones de base de datos.

La interfaz de base de datos de Python admite muchísimas bases de datos; puedes elegir la base de datos que se adapte a tu proyecto:

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

Puedes visitarPython Database Interface y APIpara ver la lista detallada de bases de datos compatibles.

Para diferentes bases de datos necesitas descargar diferentes módulos de la API de base de datos. Por ejemplo, si necesitas acceder a bases de datos Oracle y MySQL, debes descargar los módulos de base de datos Oracle y MySQL.

DB-API es una especificación. Define una serie de objetos y métodos de acceso a bases de datos que son obligatorios, con el fin de proporcionar una interfaz de acceso consistente para diversos sistemas de bases de datos subyacentes y una amplia variedad de programas de interfaz de bases de datos.

La DB-API de Python implementa interfaces para la mayoría de las bases de datos. Después de usarla para conectarse a varias bases de datos, se pueden operar todas ellas de la misma manera.

Flujo de uso de Python DB-API:

Importar el módulo de API.
Obtener la conexión con la base de datos.
Ejecutar sentencias SQL y procedimientos almacenados.
Cerrar la conexión con la base de datos.

¿Qué es MySQLdb?

MySQLdb es una interfaz utilizada en Python para conectarse a la base de datos MySQL. Implementa la especificación V2.0 de la API de base de datos de Python y está construida sobre la API C de MySQL.

¿Cómo instalar MySQLdb?

Para escribir scripts de MySQL con DB-API, debes asegurarte de que MySQL esté instalado. Copia el siguiente código y ejecútalo:

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

import MySQLdb

Si el resultado de la ejecución es el siguiente, significa que no tienes instalado el módulo MySQLdb:

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

Para instalar MySQLdb, visitahttp://sourceforge.net/projects/mysql-python, (en la plataforma Linux puedes visitar:https://pypi.python.org/pypi/MySQL-python). Desde aquí puedes elegir el paquete de instalación adecuado para tu plataforma, que se divide en archivos binarios precompilados y paquetes de instalación de código fuente.

Si eliges la versión de distribución de archivos binarios, el proceso de instalación se puede completar básicamente siguiendo las indicaciones de instalación. Si instalas desde el código fuente, debes cambiar al directorio de nivel superior de la distribución de MySQLdb y escribir los siguientes comandos:

$ 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

Nota:Asegúrate de tener permisos de root para instalar el módulo mencionado.

Conexión a la base de datos

Antes de conectarte a la base de datos, confirma lo siguiente:

Has creado la base de datos TESTDB.
Has creado la tabla EMPLOYEE en la base de datos TESTDB.
Los campos de la tabla EMPLOYEE son FIRST_NAME, LAST_NAME, AGE, SEX e INCOME.
El nombre de usuario para conectarse a la base de datos TESTDB es "testuser" y la contraseña es "test123". Puedes configurarlo tú mismo o usar directamente el nombre de usuario root y su contraseña. Para la autorización de usuarios de la base de datos MySQL, usa el comando GRANT.
En tu máquina ya está instalado el módulo Python MySQLdb.
Si no estás familiarizado con las sentencias SQL, puedes visitar nuestroTutorial básico de SQL

Ejemplo:

El siguiente ejemplo se conecta a la base de datos 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()

El resultado de ejecutar el script anterior es el siguiente:

Database version : 5.0.45

Crear una tabla de base de datos

Si la conexión a la base de datos existe, podemos usar el método execute() para crear una tabla en la base de datos, como se muestra a continuación para crear la tabla 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()

Operación de inserción en la base de datos

El siguiente ejemplo inserta registros en la tabla EMPLOYEE mediante la ejecución de una sentencia 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()

El ejemplo anterior también se puede escribir de la siguiente manera:

#!/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()

Ejemplo:

El siguiente código usa variables para pasar parámetros a la sentencia SQL:

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

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

Operación de consulta en la base de datos

Python consulta MySQL usando el método fetchone() para obtener un solo dato, y el método fetchall() para obtener múltiples datos.

fetchone():Este método obtiene el siguiente conjunto de resultados de la consulta. El conjunto de resultados es un objeto.
fetchall():Recibe todas las filas de resultados devueltas.
rowcount:Esta es una propiedad de solo lectura que devuelve el número de filas afectadas después de ejecutar el método execute().

Ejemplo:

Consulta todos los datos donde el campo salary (salario) en la tabla EMPLOYEE sea mayor que 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()

El resultado de ejecutar el script anterior es el siguiente:

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

Operación de actualización de la base de datos

La operación de actualización se utiliza para actualizar los datos de una tabla. El siguiente ejemplo incrementa en 1 el campo AGE de los registros donde el campo SEX es 'M' en la tabla EMPLOYEE:

#!/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()

Operación de eliminación

La operación de eliminación se utiliza para eliminar datos de una tabla. El siguiente ejemplo demuestra cómo eliminar todos los datos de la tabla EMPLOYEE donde AGE sea mayor que 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()

Ejecutar transacciones

El mecanismo de transacciones puede garantizar la consistencia de los datos.

Una transacción debe tener cuatro propiedades: atomicidad, consistencia, aislamiento y persistencia. Estas cuatro propiedades suelen denominarse características ACID.

Atomicidad (atomicity). Una transacción es una unidad de trabajo indivisible. Todas las operaciones incluidas en la transacción se realizan o no se realizan.
Consistencia (consistency). La transacción debe hacer que la base de datos pase de un estado consistente a otro estado consistente. La consistencia está estrechamente relacionada con la atomicidad.
Aislamiento (isolation). La ejecución de una transacción no puede ser interferida por otras transacciones. Es decir, las operaciones internas de una transacción y los datos que utiliza están aislados de otras transacciones concurrentes, y las transacciones que se ejecutan simultáneamente no pueden interferirse entre sí.
Persistencia (durability). La persistencia también se denomina permanencia (permanence), y significa que una vez que una transacción se ha confirmado, los cambios que ha realizado en los datos de la base de datos deben ser permanentes. Las operaciones posteriores o fallos no deberían tener ningún efecto sobre ella.

Python DB API 2.0 proporciona dos métodos para las transacciones: commit o rollback.

Ejemplo:

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

Para bases de datos que admiten transacciones, en la programación de bases de datos de Python, cuando se crea el cursor, se inicia automáticamente una transacción de base de datos implícita.

El método commit() confirma todas las operaciones de actualización del cursor, y el método rollback() revierte todas las operaciones del cursor actual. Cada método inicia una nueva transacción.

Manejo de errores

La API de base de datos define algunos errores y excepciones para las operaciones de base de datos. La siguiente tabla enumera estos errores y excepciones:

Excepción	Descripción
Warning	Se activa cuando hay una advertencia grave, por ejemplo, cuando los datos insertados se truncan, etc. Debe ser una subclase de StandardError.
Error	Todas las demás clases de error excepto las advertencias. Debe ser una subclase de StandardError.
InterfaceError	Se activa cuando ocurre un error del propio módulo de interfaz de base de datos (no un error de la base de datos). Debe ser una subclase de Error.
DatabaseError	Se activa cuando ocurre un error relacionado con la base de datos. Debe ser una subclase de Error.
DataError	Se activa cuando ocurre un error durante el procesamiento de datos, por ejemplo: error de división por cero, datos fuera de rango, etc. Debe ser una subclase de DatabaseError.
OperationalError	Se refiere a errores que no están controlados por el usuario, sino que ocurren al operar la base de datos. Por ejemplo: conexión interrumpida inesperadamente, nombre de base de datos no encontrado, fallo en el procesamiento de transacciones, error de asignación de memoria, etc. Debe ser una subclase de DatabaseError.
IntegrityError	Errores relacionados con la integridad, como fallos en la verificación de claves externas, etc. Debe ser una subclase de DatabaseError.
InternalError	Errores internos de la base de datos, como un cursor que ha dejado de ser válido, fallos en la sincronización de transacciones, etc. Debe ser una subclase de DatabaseError.
ProgrammingError	Error de programación, como que la tabla no se encuentre o ya exista, errores de sintaxis en la sentencia SQL, errores en el número de parámetros, etc. Debe ser una subclase de DatabaseError.
NotSupportedError	Error de no soporte, se refiere al uso de funciones o APIs que la base de datos no admite. Por ejemplo, usar la función .rollback() en un objeto de conexión, pero la base de datos no admite transacciones o estas están desactivadas. Debe ser una subclase de DatabaseError.

Otras extensiones

PythonTutorial avanzado

Manejo de la base de datos MySQL con Python

¿Qué es MySQLdb?

¿Cómo instalar MySQLdb?

Conexión a la base de datos

Ejemplo:

Crear una tabla de base de datos

Operación de inserción en la base de datos

Ejemplo:

Operación de consulta en la base de datos

Ejemplo:

Operación de actualización de la base de datos

Operación de eliminación

Ejecutar transacciones

Ejemplo:

Manejo de errores