Python - Acceso a Bases de Datos MySQL

Instalar LAMP en Debian 9

$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 102
Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select user, host 
    -> from mysql.user;
+------------+-----------+
| user       | host      |
+------------+-----------+
| phpmyadmin | localhost |
| root       | localhost |
+------------+-----------+
2 rows in set (0.00 sec)


MariaDB [(none)]> show grants for phpmyadmin@localhost;
+-------------------------------------------------------------------------------------------------------------------+
| Grants for phpmyadmin@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'phpmyadmin'@'localhost' IDENTIFIED BY PASSWORD '*3240E4A6F96C8F12FA1FAA6E47FF414BE575522B' |
| GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO 'phpmyadmin'@'localhost'                                                |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


MariaDB [(none)]> grant all privileges on *.* to 'phpmyadmin'@'localhost' identified by 'abc123.' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select user,host from mysql.user;
+------------+-----------+
| user       | host      |
+------------+-----------+
| phpmyadmin | localhost |
| root       | localhost |
+------------+-----------+
2 rows in set (0.00 sec)

MariaDB [(none)]> show grants for phpmyadmin@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for phpmyadmin@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' IDENTIFIED BY PASSWORD '*3240E4A6F96C8F12FA1FAA6E47FF414BE575522B' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO 'phpmyadmin'@'localhost'                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

• Si queremos acceder al servidor remotamente, comentar en el archivo /etc/mysql/mariadb.conf.d/50-server.cnf la línea skip-external-locking y cambiar el parámetro bind-address=[IPservidor]

Enlace interesante

• Instalar el módulo MySQLdb:

$ apt-get install python3-mysqldb

Leer una tabla de una BD existente

#!/usr/bin/python3

import MySQLdb

DB_HOST = 'localhost'
DB_USER = 'dbsensor'
DB_PASS = 'abc123..'
DB_NAME = 'dbsensor'

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

for linea in run_query('SELECT * FROM datos'):
    print ("{}".format(linea))
    for dato in linea:
        print (" - {}".format(dato))

Insertar datos en una tabla

f = datetime.datetime.now().strftime("%Y-%m-%d")
ho = datetime.datetime.now().strftime("%H:%M:%S")
t = float(24)
hu = float(55)
l = float(500)
d = float(20)
p = float(900)
c = float(1111)
c2 = float(400)
v = float(5)

query = "INSERT INTO datos (fecha, hora, temperatura, humedad, luminosidad, dbs, \
    presion, co, co2, vocs) VALUES ('{}', '{}', {}, {}, {}, {}, {}, {}, {}, {})".\
    format(f, ho, t, hu, l, d, p, c, c2, v)

print(query)
run_query(query)

--Volver