Acceso a MySQL con Python: Usando el Estándar DB-API

El estándar de Python para interfaces de bases de datos es el Python DB-API. La mayoría de interfaces de bases de datos Python se adhieren a este estándar.

Puedes elegir la base de datos adecuada para tu aplicación. Python Database API soporta una amplia gama de servidores:

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

Debes descargar un módulo DB API separado para cada base de datos. Por ejemplo, para Oracle y MySQL necesitas ambos módulos.

El DB API proporciona un estándar mínimo para trabajar con bases de datos usando estructuras y sintaxis Python:

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

Aprenderemos todos los conceptos usando MySQL y el módulo MySQLdb.

¿Qué es MySQLdb?

MySQLdb es una interfaz para conectar a un servidor MySQL desde Python. Implementa Python Database API v2.0 y está construido sobre la API C de MySQL.

Instalación de MySQLdb

Verifica si está instalado:

#!/usr/bin/python
import MySQLdb

Si falla con ImportError: No module named MySQLdb, instálalo:

# Ubuntu
sudo apt-get install python-pip python-dev libmysqlclient-dev

# Fedora
sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc

# Python pip
pip install MySQL-python

Nota: Se requieren privilegios de root.

Conexión a la Base de Datos

Requisitos previos:

• Base de datos TESTDB creada.
• Tabla EMPLOYEE con campos: FIRST_NAME, LAST_NAME, AGE, SEX, INCOME.
• Usuario "testuser" y contraseña "test123" con acceso a TESTDB.
• MySQLdb instalado correctamente.

Ejemplo de Conexión

#!/usr/bin/python
import MySQLdb

# Abrir conexión
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# Crear cursor
cursor = db.cursor()

# Ejecutar consulta SQL
cursor.execute("SELECT VERSION()")

# Obtener una fila
data = cursor.fetchone()
print("Database version: %s" % data)

# Cerrar conexión
db.close()

Resultado: Database version: 5.0.45

Si la conexión falla, db es None. El objeto cursor ejecuta consultas SQL. Siempre cierra la conexión para liberar recursos.

Creación de Tablas

Una vez establecida la conexión, usa el método execute del cursor.

Ejemplo: Crear Tabla EMPLOYEE

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()

# Eliminar tabla si existe
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Crear tabla
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 INSERT

Para crear registros en una tabla.

Ejemplo: INSERT Estático

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()

sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

db.close()

Ejemplo: INSERT Dinámico

sql = "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d')" % \
       ('Mac', 'Mohan', 20, 'M', 2000)

Ejemplo: Parámetros Directos

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

Operación READ

Obtener información útil de la base de datos.

• fetchone(): Obtiene la siguiente fila de un conjunto de resultados.
• fetchall(): Obtiene todas las filas del conjunto de resultados.
• rowcount: Atributo de solo lectura con número de filas afectadas por execute().

Ejemplo: Consultar Empleados con INCOME > 1000

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()

sql = "SELECT * FROM EMPLOYEE WHERE INCOME > '%d'" % (1000)

try:
    cursor.execute(sql)
    results = cursor.fetchall()
    for row in results:
        fname, lname, age, sex, income = row
        print("fname=%s, lname=%s, age=%d, sex=%s, income=%d" % 
              (fname, lname, age, sex, income))
except:
    print("Error: unable to fetch data")

db.close()

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

Operación UPDATE

Actualizar registros existentes. Ejemplo: incrementar edad de todos los hombres en 1 año.

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()

sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')

try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

db.close()

Operación DELETE

Eliminar registros. Ejemplo: borrar empleados con edad > 20.

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()

sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)

try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

db.close()

Transacciones

Mecanismo que asegura consistencia de datos. Propiedades ACID:

• Atomicidad: La transacción se completa totalmente o no ocurre nada.
• Consistencia: Estado consistente antes y después.
• Aislamiento: Resultados intermedios no visibles fuera de la transacción.
• Durabilidad: Cambios persistentes tras commit, incluso tras fallo del sistema.

Python DB API 2.0 proporciona commit() y rollback().

Ejemplo

sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

Operación COMMIT

Finaliza los cambios definitivamente.

db.commit()

Operación ROLLBACK

Revierte cambios si no estás satisfecho.

db.rollback()

Desconexión de la Base de Datos

Usa close(). Si cierras sin commit, las transacciones pendientes se revierten automáticamente, pero es mejor llamar commit/rollback explícitamente.

db.close()

Manejo de Errores

El DB API define excepciones estándar que deben existir en cada módulo de base de datos:

Excepción	Descripción
Warning	Problemas no fatales. Subclase de StandardError.
Error	Clase base para errores. Subclase de StandardError.
InterfaceError	Errores en el módulo de base de datos, no en la BD. Subclase de Error.
DatabaseError	Errores en la base de datos. Subclase de Error.
DataError	Subclase de DatabaseError: errores en los datos.
OperationalError	Subclase de DatabaseError: pérdida de conexión, etc. Fuera de control del programador.
IntegrityError	Subclase de DatabaseError: integridad relacional (unicidad, claves foráneas).
InternalError	Subclase de DatabaseError: errores internos del módulo (cursor inactivo).
ProgrammingError	Subclase de DatabaseError: errores del programador (nombre tabla incorrecto, etc.).
NotSupportedError	Subclase de DatabaseError: funcionalidad no soportada.

Tus scripts deben manejar estos errores. Verifica que MySQLdb soporte la excepción antes de usarla. Consulta la especificación DB API 2.0 para más detalles.