https://manuais.iessanclemente.net/index.php?title=Python_-_Acceso_a_Bases_de_Datos_MySQL&feed=atom&action=historyPython - Acceso a Bases de Datos MySQL - Historial de revisiones2024-03-29T10:30:35ZHistorial de revisiones de esta página en el wikiMediaWiki 1.36.2https://manuais.iessanclemente.net/index.php?title=Python_-_Acceso_a_Bases_de_Datos_MySQL&diff=63734&oldid=prevVieites: /* Insertar datos en una tabla */2018-01-03T21:58:22Z<p><span dir="auto"><span class="autocomment">Insertar datos en una tabla</span></span></p>
<p><b>Página nueva</b></p><div>[https://www.cyberciti.biz/faq/how-to-install-linux-apache-mysql-php-lamp-stack-on-debian-9-stretch/ Instalar LAMP en Debian 9]<br />
<br />
<source lang="bash"><br />
$ mysql -u root -p<br />
Enter password: <br />
Welcome to the MariaDB monitor. Commands end with ; or \g.<br />
Your MariaDB connection id is 102<br />
Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1<br />
<br />
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.<br />
<br />
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.<br />
<br />
MariaDB [(none)]> select user, host <br />
-> from mysql.user;<br />
+------------+-----------+<br />
| user | host |<br />
+------------+-----------+<br />
| phpmyadmin | localhost |<br />
| root | localhost |<br />
+------------+-----------+<br />
2 rows in set (0.00 sec)<br />
<br />
<br />
MariaDB [(none)]> show grants for phpmyadmin@localhost;<br />
+-------------------------------------------------------------------------------------------------------------------+<br />
| Grants for phpmyadmin@localhost |<br />
+-------------------------------------------------------------------------------------------------------------------+<br />
| GRANT USAGE ON *.* TO 'phpmyadmin'@'localhost' IDENTIFIED BY PASSWORD '*3240E4A6F96C8F12FA1FAA6E47FF414BE575522B' |<br />
| GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO 'phpmyadmin'@'localhost' |<br />
+-------------------------------------------------------------------------------------------------------------------+<br />
2 rows in set (0.00 sec)<br />
<br />
<br />
MariaDB [(none)]> grant all privileges on *.* to 'phpmyadmin'@'localhost' identified by 'abc123.' with grant option;<br />
Query OK, 0 rows affected (0.00 sec)<br />
<br />
MariaDB [(none)]> select user,host from mysql.user;<br />
+------------+-----------+<br />
| user | host |<br />
+------------+-----------+<br />
| phpmyadmin | localhost |<br />
| root | localhost |<br />
+------------+-----------+<br />
2 rows in set (0.00 sec)<br />
<br />
MariaDB [(none)]> show grants for phpmyadmin@localhost;<br />
+----------------------------------------------------------------------------------------------------------------------------------------------+<br />
| Grants for phpmyadmin@localhost |<br />
+----------------------------------------------------------------------------------------------------------------------------------------------+<br />
| GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' IDENTIFIED BY PASSWORD '*3240E4A6F96C8F12FA1FAA6E47FF414BE575522B' WITH GRANT OPTION |<br />
| GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO 'phpmyadmin'@'localhost' |<br />
+----------------------------------------------------------------------------------------------------------------------------------------------+<br />
2 rows in set (0.00 sec)<br />
<br />
</source><br />
<br />
* 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]'''<br />
<br />
<br />
[http://librosweb.es/libro/python/capitulo_12.html Enlace interesante]<br />
<br />
* Instalar el módulo MySQLdb:<br />
<source lang="bash"><br />
$ apt-get install python3-mysqldb<br />
</source><br />
<br />
<br />
<br />
=== Leer una tabla de una BD existente ===<br />
<source lang="python"><br />
#!/usr/bin/python3<br />
<br />
import MySQLdb<br />
<br />
DB_HOST = 'localhost'<br />
DB_USER = 'dbsensor'<br />
DB_PASS = 'abc123..'<br />
DB_NAME = 'dbsensor'<br />
<br />
def run_query(query=''):<br />
datos = [DB_HOST, DB_USER, DB_PASS, DB_NAME]<br />
<br />
conn = MySQLdb.connect(*datos) # Conectar a la base de datos<br />
cursor = conn.cursor() # Crear un cursor<br />
cursor.execute(query) # Ejecutar una consulta<br />
<br />
if query.upper().startswith('SELECT'):<br />
data = cursor.fetchall() # Traer los resultados de un select<br />
else:<br />
conn.commit() # Hacer efectiva la escritura de datos<br />
data = None<br />
<br />
cursor.close() # Cerrar el cursor<br />
conn.close() # Cerrar la conexion<br />
<br />
return data<br />
<br />
for linea in run_query('SELECT * FROM datos'):<br />
print ("{}".format(linea))<br />
for dato in linea:<br />
print (" - {}".format(dato))<br />
</source><br />
<br />
=== Insertar datos en una tabla ===<br />
<source lang="python"><br />
<br />
f = datetime.datetime.now().strftime("%Y-%m-%d")<br />
ho = datetime.datetime.now().strftime("%H:%M:%S")<br />
t = float(24)<br />
hu = float(55)<br />
l = float(500)<br />
d = float(20)<br />
p = float(900)<br />
c = float(1111)<br />
c2 = float(400)<br />
v = float(5)<br />
<br />
query = "INSERT INTO datos (fecha, hora, temperatura, humedad, luminosidad, dbs, \<br />
presion, co, co2, vocs) VALUES ('{}', '{}', {}, {}, {}, {}, {}, {}, {}, {})".\<br />
format(f, ho, t, hu, l, d, p, c, c2, v)<br />
<br />
print(query)<br />
run_query(query)<br />
<br />
</source><br />
<br />
<br />
--[[Python|Volver]]</div>Vieites