Saltar al contenido

Python 3: acceso a la base de datos MySQL

octubre 19, 2021

El estándar de Python para las 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 bases de datos, como:

  • Tábano
  • mSQL
  • MySQL
  • PostgreSQL
  • Microsoft SQL Server 2000
  • Informix
  • Interbase
  • Oráculo
  • Sybase
  • SQLite

Aquí está la lista de interfaces de base de datos Python disponibles: Interfaces y API de bases de datos de Python. Debe descargar un módulo DB API 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 de base de datos 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. Módulo MySQLdb, una interfaz popular con MySQL no es compatible con Python 3. En su lugar, usaremos PyMySQL módulo.

¿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 pura de Python. 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 desde GitHub e instalado 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.

Nota – 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 EMPLEADO 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 se 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 db está establecido en Ninguno. Próximo, db El objeto se utiliza para crear un cursor objeto, 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 los recursos se liberen.

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 ejecutar 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 SQL INSERTAR declaración 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

LEER La operación 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 cualquiera fetchone () 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.

  • número de filas – 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()

Producción

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 ‘METRO’. 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:

  • Atomicidad – O se completa una transacción o no ocurre nada.

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

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

  • Durabilidad – 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 cometer o Retroceder 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 cometer 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 Retroceder() método.

Aquí hay un ejemplo simple para llamar al Retroceder() 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 …

close