Python 3: acceso a la base de datos MySQL

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

Puede elegir la base de datos adecuada para su aplicación. La API de base de datos de Python admite una amplia gama de servidores de base de datos, como:

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

Aquí está la lista de interfaces de base de datos de Python disponibles: interfaces de base de datos de Python y API . Debe descargar un módulo DB API por separado para cada base de datos a la que necesite acceder. Por ejemplo, si necesita acceder a una base de datos Oracle y una base de datos MySQL, debe descargar los módulos de base de datos Oracle y MySQL.

La API DB proporciona un estándar mínimo para trabajar con bases de datos que utilizan estructuras y sintaxis de Python siempre que sea posible. Esta API incluye lo siguiente:

• Importando el módulo API.
• Adquirir una conexión con la base de datos.
• Emisión de sentencias SQL y procedimientos almacenados.
• Cerrando la conexión

Python tiene un soporte incorporado para SQLite. En esta sección, aprenderíamos todos los conceptos usando MySQL. El módulo MySQLdb, una interfaz popular con MySQL no es compatible con Python 3. En su lugar, usaremos el módulo PyMySQL .

¿Qué es PyMySQL?

PyMySQL es una interfaz para conectarse a un servidor de base de datos MySQL desde Python. Implementa la API de base de datos de Python v2.0 y contiene una biblioteca cliente MySQL de Python puro. El objetivo de PyMySQL es ser un reemplazo directo de MySQLdb.

¿Cómo instalo PyMySQL?

Antes de continuar, asegúrese de tener PyMySQL instalado en su máquina. Simplemente escriba lo siguiente en su secuencia de comandos de Python y ejecútelo:

#!/usr/bin/python3

import pymysql

Si produce el siguiente resultado, significa que el módulo MySQLdb no está instalado:

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

La última versión estable está disponible en PyPI y se puede instalar con pip -

pip install pymysql

Alternativamente (por ejemplo, si pip no está disponible), se puede descargar un tarball de GitHub e instalarlo con Setuptools de la siguiente manera:

$ # X.X is the desired pymysql version (e.g. 0.5 or 0.6).
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python setup.py install
$ # The folder PyMySQL* can be safely removed now.

Note - Asegúrese de tener privilegios de root para instalar el módulo anterior.

Conexión de base de datos

Antes de conectarse a una base de datos MySQL, asegúrese de los siguientes puntos:

• Ha creado una base de datos TESTDB.

• Ha creado una tabla EMPLOYEE en TESTDB.

• Esta tabla tiene campos FIRST_NAME, APELLIDO, EDAD, SEXO e INGRESOS.

• El ID de usuario "testuser" y la contraseña "test123" están configurados para acceder a TESTDB.

• El módulo Python PyMySQL está instalado correctamente en su máquina.

• Ha seguido el tutorial de MySQL para comprender los conceptos básicos de MySQL.

Ejemplo

A continuación se muestra un ejemplo de conexión con la base de datos MySQL "TESTDB":

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.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()

Mientras ejecuta este script, produce el siguiente resultado.

Database version : 5.5.20-log

Si se establece una conexión con la fuente de datos, se devuelve un objeto de conexión y se guarda en db para uso posterior, de lo contrario dbestá establecido en Ninguno. Próximo,db El objeto se utiliza para crear un cursorobjeto, que a su vez se utiliza para ejecutar consultas SQL. Finalmente, antes de salir, se asegura de que la conexión a la base de datos esté cerrada y se liberen los recursos.

Crear tabla de base de datos

Una vez que se establece una conexión a la base de datos, estamos listos para crear tablas o registros en las tablas de la base de datos utilizando execute método del cursor creado.

Ejemplo

Creemos una tabla de base de datos EMPLEADO -

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.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()

INSERTAR Operación

La operación INSERT es necesaria cuando desea crear sus registros en una tabla de base de datos.

Ejemplo

El siguiente ejemplo, ejecuta la instrucción SQL INSERT para crear un registro en la tabla EMPLOYEE:

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.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()

El ejemplo anterior se puede escribir de la siguiente manera para crear consultas SQL dinámicamente:

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.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()

Ejemplo

El siguiente segmento de código es otra forma de ejecución en la que puede pasar parámetros directamente:

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

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

Operación LEER

READ Operar en cualquier base de datos significa obtener información útil de la base de datos.

Una vez que se establece la conexión a la base de datos, está listo para realizar una consulta en esta base de datos. Puedes usar cualquierafetchone() método para obtener un solo registro o fetchall() método para obtener varios valores de una tabla de base de datos.

• fetchone()- Obtiene la siguiente fila de un conjunto de resultados de consulta. Un conjunto de resultados es un objeto que se devuelve cuando se utiliza un objeto de cursor para consultar una tabla.

• fetchall()- Obtiene todas las filas de un conjunto de resultados. Si ya se han extraído algunas filas del conjunto de resultados, recupera las filas restantes del conjunto de resultados.

• rowcount - Este es un atributo de solo lectura y devuelve el número de filas que fueron afectadas por un método execute ().

Ejemplo

El siguiente procedimiento consulta todos los registros de la tabla EMPLOYEE que tienen un salario superior a 1000 -

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.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 = "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 fetch data")

# disconnect from server
db.close()

Salida

Esto producirá el siguiente resultado:

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

Operación de actualización

ACTUALIZAR Operar en cualquier base de datos significa actualizar uno o más registros, que ya están disponibles en la base de datos.

El siguiente procedimiento actualiza todos los registros que tienen SEXO como 'M'. Aquí, aumentamos la EDAD de todos los machos en un año.

Ejemplo

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.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()

Operación DELETE

La operación DELETE es necesaria cuando desea eliminar algunos registros de su base de datos. A continuación se muestra el procedimiento para eliminar todos los registros de EMPLEADO donde la EDAD es mayor de 20:

Ejemplo

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.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()

Realización de transacciones

Las transacciones son un mecanismo que garantiza la coherencia de los datos. Las transacciones tienen las siguientes cuatro propiedades:

• Atomicity - O se completa una transacción o no ocurre nada.

• Consistency - Una transacción debe comenzar en un estado consistente y dejar el sistema en un estado consistente.

• Isolation - Los resultados intermedios de una transacción no son visibles fuera de la transacción actual.

• Durability - Una vez que se ha comprometido una transacción, los efectos son persistentes, incluso después de una falla del sistema.

Python DB API 2.0 proporciona dos métodos para confirmar o revertir una transacción.

Ejemplo

Ya sabes cómo implementar transacciones. Aquí hay un ejemplo similar:

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

Operación COMMIT

Confirmar es una operación, que da una señal verde a la base de datos para finalizar los cambios, y después de esta operación, ningún cambio puede revertirse.

Aquí hay un ejemplo simple para llamar al commit método.

db.commit()

Operación ROLLBACK

Si no está satisfecho con uno o más de los cambios y desea revertirlos por completo, utilice el rollback() método.

Aquí hay un ejemplo simple para llamar al rollback() método.

db.rollback()

Desconexión de la base de datos

Para desconectar la conexión de la base de datos, use el método close ().

db.close()

Si el usuario cierra la conexión a una base de datos con el método close (), la base de datos revierte cualquier transacción pendiente. Sin embargo, en lugar de depender de cualquiera de los detalles de implementación de nivel inferior de la base de datos, sería mejor que su aplicación llamara a commit o rollback explícitamente.

Manejo de errores

Hay muchas fuentes de errores. Algunos ejemplos son un error de sintaxis en una instrucción SQL ejecutada, un error de conexión o una llamada al método de recuperación para un identificador de instrucción ya cancelado o terminado.

La API de base de datos define una serie de errores que deben existir en cada módulo de base de datos. La siguiente tabla enumera estas excepciones.

No Señor.	Excepción y descripción
1	Warning Se utiliza para problemas no fatales. Debe subclase StandardError.
2	Error Clase base para errores. Debe subclase StandardError.
3	InterfaceError Se utiliza para errores en el módulo de la base de datos, no en la base de datos en sí. Debe subclase Error.
4	DatabaseError Se usa para errores en la base de datos. Debe subclase Error.
5	DataError Subclase de DatabaseError que se refiere a errores en los datos.
6	OperationalError Subclase de DatabaseError que se refiere a errores como la pérdida de una conexión a la base de datos. Estos errores generalmente están fuera del control del script de Python.
7	IntegrityError Subclase de DatabaseError para situaciones que dañarían la integridad relacional, como restricciones de unicidad o claves externas.
8	InternalError Subclase de DatabaseError que se refiere a errores internos del módulo de la base de datos, como un cursor que ya no está activo.
9	ProgrammingError Subclase de DatabaseError que se refiere a errores como un nombre de tabla incorrecto y otras cosas de las que se puede culpar de forma segura.
10	NotSupportedError Subclase de DatabaseError que se refiere a intentar llamar a una funcionalidad no admitida.

Sus scripts de Python deben manejar estos errores, pero antes de usar cualquiera de las excepciones anteriores, asegúrese de que su MySQLdb sea compatible con esa excepción. Puede obtener más información sobre ellos leyendo la especificación DB API 2.0.