Acceso a MySQL con Python

De WikiEducator
Saltar a: navegación, buscar


Instalar

Instalar MySQL/MySQLdb 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

Instalar MySQLdb en Windows

MySQLdb

MySQLdb es un wrapper sobre _mysql, compatible con DB API

Ejemplo1: 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 tabla 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.

Obtenido de «https://es.wikieducator.org/index.php?title=Curso_Python_DGA_2011/acceso_a_datos/mysql_python&oldid=5146»