Lmorillas en 22:49 17 ago 2011

2011-08-17T22:49:04Z

Lmorillas en 22:44 17 ago 2011

2011-08-17T22:44:35Z

Lmorillas: /* Instalar */

2011-08-17T22:36:53Z

‎Instalar

Lmorillas: Página creada con '{{MiTitulo|Acceso a MySQL con Python}} == Instalar == == Instalar MySQL en Linux == $ sudo apt-get install mysql-server $ sudo apt-get install python-mysql…'

2011-08-17T22:17:23Z

Página creada con '{{MiTitulo|Acceso a MySQL con Python}} == Instalar == == Instalar MySQL en Linux == <source lang="bash"> $ sudo apt-get install mysql-server $ sudo apt-get install python-mysql…'

Página nueva

{{MiTitulo|Acceso a MySQL con Python}}

== Instalar ==
== Instalar MySQL en Linux ==
<source lang="bash">
$ 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
</source>

== MySQLdb ==

* Basado en http://zetcode.com/databases/mysqlpythontutorial/

MySQLdb es un wrapper sobre _mysql, compatible con DB API

== Ejemplo1: versión de la base de datos ==
<source lang="python">
#!/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
</source>

==Creación de tabla e inserción de datos==
<source lang="python">
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)
</source>

<source lang="bash">
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)
</source>

== Recuperar datos ==
<source lang="python">
#!/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)
</source>

<source lang="bash">
$ ./recupera.py
(1L, 'Jack London')
(2L, 'Honore de Balzac')
(3L, 'Lion Feuchtwanger')
(4L, 'Emile Zola')
(5L, 'Truman Capote')
</source>

== Recupera uno a uno ==
<source lang="python">
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()
</source>

== Cursor Diccionario ('''DictCursor''')==
<source lang="python">
#!/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)
</source>

== Usando descripción del cursor ==
<source lang="python">
#!/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)
</source>

== Sentencias preparadas ==

<source lang="python">
#!/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)
</source>

<source lang="bash">
mysql> SELECT Name FROM Escritores WHERE Id=4;
+------------------+
| Name |
+------------------+
| Guy de Maupasant |
+------------------+
1 row in set (0.00 sec)
</source>

== Escritura de imágenes ==
Las imágenes son datos binarios. MySQL tiene un tipo de dato especial: BLOB (Binary Large Object).
<source lang="bash">
mysql> CREATE TABLE Imagenes(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);
Query OK, 0 rows affected (0.06 sec)
</source>

<source lang="python">
#!/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))
</source>

== Leer imágenes ==
<source lang="python">
#!/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)
</source>
==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.

<source lang="python">
#!/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)
</source>

<source lang="bash">
$ ./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)
</source>
Hay un error, pero las primeras filas se han cambiado.

<source lang="sql">
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');
</source>

<source lang="python">
#!/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()
</source>

<source lang="bash">
$ ./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)
</source>
El error se produce antes del '''commit''' y entra en juego el método '''rollback'''.

@@ Línea 4: / Línea 4: @@
 * Fuente: http://sourceforge.net/projects/mysql-python/
-== Instalar MySQL/MySQLdb en Linux ==
+=== Instalar MySQL/MySQLdb en Linux ===
 <source lang="bash">
 $ sudo apt-get install mysql-server

@@ Línea 2: / Línea 2: @@
 == Instalar ==
 == Instalar MySQL en Linux ==
 <source lang="bash">

Curso Python DGA 2011/acceso a datos/mysql python - Historial de revisiones

Lmorillas en 22:49 17 ago 2011

Lmorillas en 22:44 17 ago 2011

Lmorillas: /* Instalar */

Lmorillas: Página creada con '{{MiTitulo|Acceso a MySQL con Python}} == Instalar == == Instalar MySQL en Linux == $ sudo apt-get install mysql-server $ sudo apt-get install python-mysql…'