Python3 MySQL Conexión de base de datos - Controlador PyMySQL

En este artículo les presentamos el uso de Python3PyMySQLpara conectarse a la base de datos e implementar operaciones simples de inserción, eliminación, modificación y consulta.

¿Qué es PyMySQL?

PyMySQL es una biblioteca utilizada en las versiones de Python 3.x para conectarse al servidor MySQL; en Python 2 se usa mysqldb.

PyMySQL sigue la especificación de la API de base de datos de Python v2.0 e incluye una biblioteca cliente MySQL en Python puro.

Instalación de PyMySQL

Antes de usar PyMySQL, debemos asegurarnos de que PyMySQL esté instalado.

Dirección de descarga de PyMySQL:https://github.com/PyMySQL/PyMySQL。

Si aún no está instalado, podemos usar el siguiente comando para instalar la última versión de PyMySQL:

$ pip3 install PyMySQL

Si su sistema no es compatible con el comando pip, puede instalarlo de las siguientes maneras:

1. Use el comando git para descargar e instalar el paquete de instalación (también puede descargarlo manualmente):

$ git clone https://github.com/PyMySQL/PyMySQL
$ cd PyMySQL/
$ python3 setup.py install

2. Si necesita especificar un número de versión, puede usar el comando curl para instalarlo:

$ # X.X 为 PyMySQL 的版本号
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python3 setup.py install
$ # 现在你可以删除 PyMySQL* 目录

Nota:Asegúrese de tener privilegios de root para instalar los módulos mencionados anteriormente.

Durante la instalación puede aparecer el mensaje de error "ImportError: No module named setuptools", lo que significa que no tiene instalado setuptools. Puede visitarhttps://pypi.python.org/pypi/setuptoolspara encontrar los métodos de instalación para cada sistema.

Ejemplo de instalación en sistema Linux:

$ wget https://bootstrap.pypa.io/ez_setup.py
$ python3 ez_setup.py

Conexión a la base de datos

Antes de conectarse a la base de datos, confirme lo siguiente:

  • Ya ha creado la base de datos TESTDB.
  • En la base de datos TESTDB ha creado la tabla EMPLOYEE.
  • 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". Puede configurarlos usted 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, use el comando Grant.
  • En su máquina ya se ha instalado Pythonpymysqlmódulo.
  • Si no está familiarizado con las sentencias SQL, puede visitar nuestroTutorial básico de SQL

Ejemplo:

El siguiente ejemplo conecta con la base de datos TESTDB de MySQL:

Ejemplo (Python 3.0+)

#!/usr/bin/python3 import pymysql # Abrir la conexión a la base de datos db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Usar el método cursor() para crear un objeto cursor cursor = db.cursor() # Usar el método execute() para ejecutar la consulta SQL cursor.execute("SELECT VERSION()") # Usar el método fetchone() para obtener un solo dato. data = cursor.fetchone() print ("Database version : %s " % data) # Cerrar la conexión a la base de datos db.close()

La salida del script anterior es la siguiente:

Database version : 5.5.20-log

Crear 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:

Ejemplo (Python 3.0+)

#!/usr/bin/python3 import pymysql # Abrir la conexión a la base de datos db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Usar el método cursor() para crear un objeto cursor cursor = db.cursor() # Usar el método execute() para ejecutar SQL; si la tabla existe, se elimina cursor.execute("DROP TABLE IF EXISTS EMPLOYEE") # Usar sentencias preparadas para crear la 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) # Cerrar la conexión a la base de datos db.close()

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

El siguiente ejemplo usa la sentencia SQL INSERT para insertar registros en la tabla EMPLOYEE:

Ejemplo (Python 3.0+)

#!/usr/bin/python3 import pymysql # Abrir la conexión a la base de datos db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Usar el método cursor() para obtener el cursor de operación cursor = db.cursor() # Sentencia SQL de inserción sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Mac', 'Mohan', 20, 'M', 2000)""" try: # Ejecutar la sentencia SQL cursor.execute(sql) # Confirmar la ejecución en la base de datos db.commit() except: # Si ocurre un error, revertir (rollback) db.rollback() # Cerrar la conexión a la base de datos db.close()

El ejemplo anterior también puede escribirse de la siguiente forma:

Ejemplo (Python 3.0+)

#!/usr/bin/python3 import pymysql # Abrir la conexión a la base de datos db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Usar el método cursor() para obtener el cursor de operación cursor = db.cursor() # Sentencia SQL de inserción sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \ LAST_NAME, AGE, SEX, INCOME) \ VALUES ('%s', '%s', %s, '%s', %s)" % \ ('Mac', 'Mohan', 20, 'M', 2000) try: # Ejecutar la sentencia SQL cursor.execute(sql) # Ejecutar la sentencia SQL db.commit() except: # Revertir (rollback) cuando ocurra un error db.rollback() # Cerrar la conexión a la base de datos db.close()

El siguiente código usa variables para pasar parámetros a las sentencias 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 varios 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:

Consultar todos los datos del campo salary (salario) mayor que 1000 en la tabla EMPLOYEE:

Ejemplo (Python 3.0+)

#!/usr/bin/python3 import pymysql # Abrir la conexión a la base de datos db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Usar el método cursor() para obtener el cursor de operación cursor = db.cursor() # Sentencia SQL de consulta sql = "SELECT * FROM EMPLOYEE \ WHERE INCOME > %s" % (1000) try: # Ejecutar la sentencia SQL cursor.execute(sql) # Obtener la lista de todos los registros results = cursor.fetchall() for row in results: fname = row[0] lname = row[1] age = row[2] sex = row[3] income = row[4] # Imprimir el resultado print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \ (fname, lname, age, sex, income )) except: print ("Error: unable to fetch data") # Cerrar la conexión a la base de datos 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 en la base de datos

La operación de actualización se usa para actualizar los datos de una tabla. El siguiente ejemplo incrementa en 1 el campo AGE de los registros con SEX igual a 'M' en la tabla TESTDB:

Ejemplo (Python 3.0+)

#!/usr/bin/python3 import pymysql # Abrir la conexión a la base de datos db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Usar el método cursor() para obtener el cursor de operación cursor = db.cursor() # Sentencia SQL de actualización sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M') try: # Ejecutar la sentencia SQL cursor.execute(sql) # Confirmar la ejecución en la base de datos db.commit() except: # Revertir (rollback) cuando ocurra un error db.rollback() # Cerrar la conexión a la base de datos db.close()

Operación de eliminación

La operación de eliminación se usa para borrar datos de una tabla. El siguiente ejemplo demuestra la eliminación de todos los datos con AGE mayor que 20 en la tabla EMPLOYEE:

Ejemplo (Python 3.0+)

#!/usr/bin/python3 import pymysql # Abrir la conexión a la base de datos db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # Usar el método cursor() para obtener el cursor de operación cursor = db.cursor() # Sentencia SQL de eliminación sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20) try: # Ejecutar la sentencia SQL cursor.execute(sql) # Confirmar los cambios db.commit() except: # Revertir (rollback) cuando ocurra un error db.rollback() # Cerrar la conexión 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 durabilidad. Estas cuatro propiedades se conocen comúnmente como características ACID.

  • Atomicidad: una transacción es una unidad de trabajo indivisible; las operaciones incluidas en la transacción se realizan todas o no se realiza ninguna.
  • Consistencia: una transacción debe hacer que la base de datos pase de un estado consistente a otro. La consistencia está estrechamente relacionada con la atomicidad.
  • Aislamiento: la ejecución de una transacción no puede ser interferida por otras transacciones. Es decir, las operaciones internas y los datos utilizados por una transacción están aislados de otras transacciones concurrentes, y las transacciones ejecutadas concurrentemente no pueden interferirse entre sí.
  • Durabilidad. También se llama permanencia, se refiere a que una vez que una transacción se ha confirmado, los cambios que realiza en los datos de la base de datos deben ser permanentes. Las operaciones posteriores o fallos no deberían afectarla de ninguna manera.

La transacción de Python DB API 2.0 proporciona dos métodos: commit o rollback.

Ejemplo

Ejemplo (Python 3.0+)

# Instrucción SQL para eliminar registros sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20) try: # Ejecutar la instrucción SQL cursor.execute(sql) # Confirmar a la base de datos db.commit() except: # Revertir cuando ocurra un error db.rollback()

Para bases de datos que admiten transacciones, en la programación de bases de datos en Python, cuando se crea un 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; el método rollback() revierte todas las operaciones del cursor actual. Cada método inicia una nueva transacción.

Manejo de errores

El DB API define algunos errores y excepciones de operaciones de base de datos; la siguiente tabla enumera estos errores y excepciones:

ExcepciónDescripción
WarningSe activa cuando hay una advertencia grave, por ejemplo, cuando los datos insertados se truncan, etc. Debe ser una subclase de StandardError.
ErrorTodas las demás clases de error excepto las advertencias. Debe ser una subclase de StandardError.
InterfaceErrorSe 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.
DatabaseErrorSe activa cuando ocurre un error relacionado con la base de datos. Debe ser una subclase de Error.
DataErrorSe 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.
OperationalErrorSe refiere a errores que no controla el usuario, sino que ocurren al operar la base de datos. Por ejemplo: desconexión inesperada, 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.
IntegrityErrorErrores relacionados con la integridad, como fallos en la comprobación de claves foráneas, etc. Debe ser una subclase de DatabaseError.
InternalErrorErrores internos de la base de datos, por ejemplo, cursor no válido, fallo de sincronización de transacciones, etc. Debe ser una subclase de DatabaseError.
ProgrammingErrorErrores de programa, por ejemplo, tabla de datos no encontrada o ya existente, error de sintaxis en la instrucción SQL, número incorrecto de parámetros, etc. Debe ser una subclase de DatabaseError.
NotSupportedErrorError no soportado, se refiere al uso de funciones o API 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 la transacción ya está cerrada. Debe ser una subclase de DatabaseError.

A continuación se muestra la estructura de herencia de las excepciones:

Exception
|__Warning
|__Error
   |__InterfaceError
   |__DatabaseError
      |__DataError
      |__OperationalError
      |__IntegrityError
      |__InternalError
      |__ProgrammingError
      |__NotSupportedError
Otras extensiones