Conectar a MySQL con python

Publicado en febrero de 2018

Vamos a ver como podemos conectar a una base de datos MySQL para poder guardar y leer datos desde nuestros proyectos con Python. Se me ocurren varias utilidades, como guardar datos de sensores y poder luego sacar gráficos o el momento en que se disparan ciertos eventos, accesos a nuestro servidor web, etc...

Que necesitas:

Para mis pruebas he partido de una Orange Pi Plus 2e con Armbian totalmente actualizado. Lo primero que necesitamos es instalar la base de datos. Podemos hacerlo con:

sudo apt-get install mysql-server

Durante la instalación te preguntará si quieres usar una password de administrador para el usuario root. ¡SÍ QUIERES!. Nunca dejes esta contraseña en blanco. La pantalla es esta:

Puedes comprobar que la instalación ha sido correcta consultando la versión de MySQL con:

mysql --version

mysql  Ver 14.14 Distrib 5.5.59, for debian-linux-gnu (armv7l) using readline 6.3

Ahora vamos a acceder a MySQL con la siguiente línea, donde lanzamos el programa con la opción -u de usuario y -p de password:

mysql -u root -p

introducimos la contraseña

Enter password:

y el prompt pasará a ser el de mysql, con este aspecto:

mysql>

Vamos a usar un ejemplo muy sencillo que tengo activo en un acuario automatizado y que guarda la hora y la fecha a la que se activa el comedero. En mi sistema se hace desde web, pero también se puede hacer desde Python de manera muy sencilla. Primero vamos a crear la base de datos con:

CREATE DATABASE acuario;

Y añadimos un nuevo usuario para no tener que usar el usuario root y le asignamos privilegios para la base de datos acuario. El usuario será opiuser y la clave opipwd:

CREATE USER 'opiuser'@'localhost' IDENTIFIED BY 'opipwd';
GRANT ALL PRIVILEGES ON acuario.* TO 'opiuser'@'localhost' WITH GRANT OPTION;
CREATE USER 'opiuser'@'%' IDENTIFIED BY 'opipwd';
GRANT ALL PRIVILEGES ON acuario.* TO 'opiuser'@'%' WITH GRANT OPTION;

Ahora creamos una tabla que guarde un identificador y la fecha y hora de cada comida:

USE acuario;
CREATE TABLE comidas (id INT NOT NULL AUTO_INCREMENT, fecha DATETIME NOT NULL, PRIMARY KEY (id));

Ya tenemos lo necesario, puedes salir con quit. Para poder usar Python necesitamos instalar un conector, en este caso para Mysql:

sudo apt-get install python-mysqldb

Ahora, para acceder a los datos necesitamos conectarnos a la base de datos y ejecutar una consulta. Los pasos a seguir son los siguientes:

1. Abrir la conexión y crear un puntero
2. Ejecutar la consulta
3. Traer los resultados (si se trata de una selección) o hacer efectiva la escritura (cuando se insertan, actualizan o eliminan datos)
4. Cerrar el puntero y la conexión

Para insertar un dato podemos hacer lo siguiente (este código está preparado para ejecutar todos los pasos, tanto para hacer una consulta, como para insertar o actualizar datos, así se entiende mejor el proceso completo y podremos usar el mismo script para diferentes propósitos con pequeñas variantes):

#!/usr/bin/env python
# -*- coding: iso-8859-15 -*-
# author    : "javier pérez santos para orangepiweb.es"
# credits   : "javier pérez santos, orangepiweb.es"
# copyright : "Copyright 2017, orangepiweb.es"

"""
   ___                             ____  ___        __   _
  / _ \ _ __ __ _ _ __   __ _  ___|  _ \(_) \      / /__| |__   ___  ___
 | | | | '__/ _` | '_ \ / _` |/ _ \ |_) | |\ \ /\ / / _ \ '_ \ / _ \/ __|
 | |_| | | | (_| | | | | (_| |  __/  __/| | \ V  V /  __/ |_) |  __/\__ \
  \___/|_|  \__,_|_| |_|\__, |\___|_|   |_|  \_/\_/ \___|_.__(_)___||___/
                        |___/

Conectar a MySQL con python
"""

import MySQLdb
from datetime import date, datetime

DB_HOST = 'localhost'
DB_USER = 'opiuser'
DB_PASS = 'opipwd'
DB_NAME = 'acuario'

def run_query(query=''):
    datos = [DB_HOST, DB_USER, DB_PASS, DB_NAME]

    conn = MySQLdb.connect(*datos) # Conectar a la base de datos
    cursor = conn.cursor()         # Crear un cursor
    cursor.execute(query)          # Ejecutar una consulta

    if query.upper().startswith('SELECT'):
        data = cursor.fetchall()   # Traer los resultados de un select
    else:
        conn.commit()              # Hacer efectiva la escritura de datos
        data = None

    cursor.close()                 # Cerrar el cursor
    conn.close()                   # Cerrar la conexion

    return data

# Esta es la consulta, insercion o actualizacion que vamos a lanzar
date = datetime.now()
query = "INSERT INTO comidas (fecha) VALUES ('%s')" % date
run_query(query)

Después de ejecutarlo 2 veces, si hacemos una consulta desde mysql, tendremos lo siguiente:

mysql> select * from comidas;
+----+---------------------+
| id | fecha               |
+----+---------------------+
|  1 | 2018-02-23 15:26:49 |
|  2 | 2018-02-23 15:34:25 |
+----+---------------------+
2 rows in set (0.01 sec)

Si queremos leer todos los registros, podemos cambiar las dos últimas líneas por lo siguiente (o añadir al final, con lo que podemos insertar y mostrar el resultado):

query = "SELECT * FROM comidas" ORDER BY fecha DESC" 
result = run_query(query) 
print result

Podemos hacer que nos pregunte algún dato (en este caso con fechas dará problemas por los formatos, pero funcionar, funciona...):

dato = raw_input("Dato a buscar: ") 
query = "SELECT * FROM comidas WHERE fecha = '%s'" % dato 
result = run_query(query)
print result

Eliminar registros con pregunta:

dato = raw_input("Introduce el ID que quieres borrar: ") 
query = "DELETE FROM comidas WHERE id = '%s'" % dato 
run_query(query)

Actualizar datos:

dato1 = raw_input("ID: ")
dato2 = raw_input("Nueva fecha: ") 
query = "UPDATE comidas SET fecha='%s' WHERE id = %i" % (dato2, int(dato1)) 
run_query(query)

Espero que te haya sido de utilidad, no dudes en escribir un comentario si ves algún error o si puedo ayudarte.