Acceso a MySQL con Python
De WikiEducator
Contenido
Instalar
Instalar MySQL en Linux
$ sudo apt-get install mysql-server $ sudo apt-get install python-mysqldb $ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.00 sec) We connect to the database using the root account. We show all available databases with the SHOW DATABASES statement. mysql> CREATE DATABASE testdb; Query OK, 1 row affected (0.02 sec) We create a new testdb database. We will use this database throughout the tutorial. mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test623'; Query OK, 0 rows affected (0.00 sec) mysql> USE testdb; Database changed mysql> GRANT ALL ON testdb.* TO 'testuser'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> quit; Bye
MySQLdb
MySQLdb es un wrapper sobre _mysql, compatible con DB API
Versión de la base de datos
#!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as dbapi import sys try: conn = dbapi.connect('localhost', 'testuser', 'test623', 'testdb'); cursor = conn.cursor() cursor.execute("SELECT VERSION()") data = cursor.fetchone() cursor.close() conn.close() except dbapi.Error, e: print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1) print "Database version : %s " % data
Creación de tablas e inserción de datos
import MySQLdb as dbapi import sys try: con = dbapi.connect('localhost', 'testuser', 'test623', 'testdb'); cursor = con.cursor() cursor.execute("CREATE TABLE IF NOT EXISTS \ Escritores(Id INT PRIMARY KEY AUTO_INCREMENT, Nombre VARCHAR(25))") cursor.execute("INSERT INTO Escritores(Nombre) VALUES('Jack London')") cursor.execute("INSERT INTO Escritores(Nombre) VALUES('Honore de Balzac')") cursor.execute("INSERT INTO Escritores(Nombre) VALUES('Lion Feuchtwanger')") cursor.execute("INSERT INTO Escritores(Nombre) VALUES('Emile Zola')") cursor.execute("INSERT INTO Escritores(Nombre) VALUES('Truman Capote')") con.commit() cursor.close() con.close() except dbapi.Error, e: print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)
mysql> SELECT * FROM Escritores; +----+-------------------+ | Id | Nombre | +----+-------------------+ | 1 | Jack London | | 2 | Honore de Balzac | | 3 | Lion Feuchtwanger | | 4 | Emile Zola | | 5 | Truman Capote | +----+-------------------+ 5 rows in set (0.00 sec)
Recuperar datos
#!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as dbapi import sys try: con = dbapi.connect('localhost', 'testuser', 'test623', 'testdb'); cursor = conn.cursor() cursor.execute("SELECT * FROM Escritores") rows = cursor.fetchall() for row in rows: print row cursor.close() conn.close() except dbapi.Error, e: print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)
$ ./recupera.py (1L, 'Jack London') (2L, 'Honore de Balzac') (3L, 'Lion Feuchtwanger') (4L, 'Emile Zola') (5L, 'Truman Capote')
Recupera uno a uno
con = dbapi.connect('localhost', 'testuser', 'test623', 'testdb'); cursor = con.cursor() cursor.execute("SELECT * FROM Escritores") numrows = int(cursor.rowcount) for i in range(numrows): row = cursor.fetchone() print row[0], row[1] cursor.close() conn.close()
Cursor Diccionario (DictCursor)
#!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as dbapi import sys try: conn = dbapi.connect('localhost', 'testuser', 'test623', 'testdb'); cursor = conn.cursor(dbapi.cursors.DictCursor) cursor.execute("SELECT * FROM Escritores") rows = cursor.fetchall() for row in rows: print "%s %s" % (row["Id"], row["Name"]) cursor.close() conn.close() except dbapi.Error, e: print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)
Usando descripción del cursor
#!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as dbapi import sys try: conn = dbapi.connect('localhost', 'testuser', 'test623', 'testdb'); cursor = conn.cursor() cursor.execute("SELECT * FROM Escritores") rows = cursor.fetchall() desc = cursor.description print "%s %3s" % (desc[0][0], desc[1][0]) for row in rows: print "%2s %3s" % row cursor.close() conn.close() except dbapi.Error, e: print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)
Sentencias preparadas
#!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as dbapi import sys try: conn = dbapi.connect('localhost', 'testuser', 'test623', 'testdb'); cursor = conn.cursor() cursor.execute("UPDATE Escritores SET Name = %s WHERE Id = %s", ("Guy de Maupasant", "4")) print "Number of rows updated: %d" % cursor.rowcount cursor.close() conn.close() except dbapi.Error, e: print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)
mysql> SELECT Name FROM Escritores WHERE Id=4; +------------------+ | Name | +------------------+ | Guy de Maupasant | +------------------+ 1 row in set (0.00 sec)
Escritura de imágenes
Las imágenes son datos binarios. MySQL tiene un tipo de dato especial: BLOB (Binary Large Object).
mysql> CREATE TABLE Imagenes(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB); Query OK, 0 rows affected (0.06 sec)
#!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as dbapi import sys try: fin = open("foto.png") img = fin.read() fin.close() except IOError, e: print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1) try: conn = dbapi.connect(host='localhost',user='testuser', passwd='test623', db='testdb') cursor = conn.cursor() cursor.execute("INSERT INTO Imagenes SET Data='%s'" % \ dbapi.escape_string(img)) conn.commit() cursor.close() conn.close() except dbapi.Error, e: print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1) In the above script, we read a png image and insert it into the Images table. fin = open("chrome.png") img = fin.read() We open and read an image. The read() function returns the data as string. cursor.execute("INSERT INTO Images SET Data='%s'" % dbapi.escape_string(img))
Leer imágenes
#!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as dbapi import sys try: conn = dbapi.connect(host='localhost',user='testuser', passwd='test623', db='testdb') cursor = conn.cursor() cursor.execute("SELECT Data FROM Imagenes LIMIT 1") fout = open('image.png','wb') fout.write(cursor.fetchone()[0]) fout.close() cursor.close() conn.close() except IOError, e: print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)
Transacciones
Una transacción es una operación "atómica" en la base de datos.
MySQL tiene dos motores: MyISAM y InnoDB. MyISAM (por defecto) es muy rápido, pero no soporta transacciones.
#!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as mdb import sys try: conn = dbapi.connect('localhost', 'testuser', 'test623', 'testdb'); cursor = conn.cursor() cursor.execute("UPDATE Escritores SET Name = %s WHERE Id = %s", ("Leo Tolstoy", "1")) cursor.execute("UPDATE Escritores SET Name = %s WHERE Id = %s", ("Boris Pasternak", "2")) cursor.execute("UPDATE Writer SET Name = %s WHERE Id = %s", ("Leonid Leonov", "3")) conn.commit() cursor.close() conn.close() except dbapi.Error, e: conn.rollback() print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)
$ ./test_transaccion.py Error 1146: Table 'testdb.Writer' doesn't exist mysql> SELECT * FROM Escritores; +----+-------------------+ | Id | Name | +----+-------------------+ | 1 | Leo Tolstoy | | 2 | Boris Pasternak | | 3 | Lion Feuchtwanger | | 4 | Guy de Maupasant | | 5 | Truman Capote | +----+-------------------+ 5 rows in set (0.00 sec)
Hay un error, pero las primeras filas se han cambiado.
DROP TABLE Escritores; CREATE TABLE IF NOT EXISTS Escritores(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25)) ENGINE=INNODB; INSERT INTO Escritores(Nombre) VALUES('Jack London'); INSERT INTO Escritores(Nombre) VALUES('Honore de Balzac'); INSERT INTO Escritores(Nombre) VALUES('Lion Feuchtwanger'); INSERT INTO Escritores(Nombre) VALUES('Emile Zola'); INSERT INTO Escritores(Nombre) VALUES('Truman Capote');
#!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as dbapi import sys try: conn = dbapi.connect('localhost', 'testuser', 'test623', 'testdb'); cursor = conn.cursor() cursor.execute("DELETE FROM Escritores WHERE Id = 5") cursor.execute("DELETE FROM Escritores WHERE Id = 4") cursor.execute("DELETE FROM Writer WHERE Id = 3") conn.commmit() except dbapi.Error, e: conn.rollback() print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1) cursor.close() conn.close()
$ ./innodb.py Error 1146: Table 'testdb.Writer' doesn't exist mysql> SELECT * FROM Escritores; +----+-------------------+ | Id | Name | +----+-------------------+ | 1 | Jack London | | 2 | Honore de Balzac | | 3 | Lion Feuchtwanger | | 4 | Emile Zola | | 5 | Truman Capote | +----+-------------------+ 5 rows in set (0.00 sec)
El error se produce antes del commit y entra en juego el método rollback.
