martes, 19 de octubre de 2010

Conceptos básicos de Python y SQLite

En el presente post vamos a aprender los conceptos básicos para desarrollar código en Python utilizando la base de datos SQLite. Se presupone que el lector conoce los conceptos básicos de Python y de SQL, y que ya tiene instalado en su máquina tanto el intérprete de Python como SQLite.

Librería pysqlite

Lo primero que debemos hacer es descargar e instalar la librería pysqlite desde su página web: http://code.google.com/p/pysqlite

En el caso de tener Windows, descargamos un archivo ejecutable (.exe) y lo ejecutamos, instalándolo como cualquier aplicación Windows.

En el caso de tener Linux, descargamos el archivo comprimido (.tar.gz), y lo descomprimimos. Una vez descomprimido, instalamos la librería mediante el siguiente comando:

$ python setup.py build
$ python setup.py install


Conexión a la base de datos

Para realizar una conexión a una base de datos SQLite, ésta debe estar arrancada:

$ sqlite3 mibasedatos.bbdd

Para realizar una conexión a una base de datos, importar la librería:

from pysqlite2 import dbapi2 as sqlite

A continuación se establece la conexión indicando el archivo de base de datos:

con = sqlite.connect('mibasedatos.bbdd')


Consultas de datos

Para todas las operaciones con la base de datos, hay que crear un cursor a partir de la conexión:

cur = con.cursor()

A partir del cursor, se pueden ejecutar las operaciones que se requieran, como la consulta de datos:

cur.execute('select campo from tabla')

Al ejecutar la consulta, el cursor apunta antes del primer registro o fila. Los siguientes comandos avanzan hasta el siguiente registro:

cur.next() # Avance
cur.fetchone() # Avance y recupera registro

Para obtener de una vez todos los registros del cursor:

cur = con.cursor()
cur.execute('select * from tabla')
result = cur.fetchall()

En la variable result se almacenará una lista o secuencia de tuplas, cada una de las cuales corresponde a un registro, y el valor de cada uno de los campos se encuentra separado por comas:

print result
[(0, u'valor1', 87), (1, u'valor2', 32), (2, u'valor3', 38)]

En este ejemplo, el resultado da 3 registros con tres columnas o campos, de los cuales, el primero y el último son numéricos, y el segundo es alfanumérico.

print result[1] # registro 2
(1, u'valor2', 32)
print result[2][1] # campo 2 del registro 3
valor3

Recorrer el resultado resulta muy sencillo, utilizando un iterador sobre la secuencia:

cursor = con.cursor()
cursor.execute('SELECT id, nombre, nick, password, rol FROM usuarios')
for fila in cursor:
   print 'id:', fila[0]
   print 'Nombre:', fila[1]
   print 'Nick:', fila[2]
   print 'Password:', fila[3]
   print 'Rol:', fila[4]
   print '*'*30


Actualizaciones

Por actualización se entiende toda aquella operación que realiza algún cambio, lo que implica algún tipo de escritura. Existen varios tipos de escritura o modificaciones en la base de datos. El primero afectaría únicamente a los datos de las tablas, lo que se consigue mediante las sentencias UPDATE, INSERT y DELETE de SQL. Otro afectaría a la estructura de la base de datos, pudiendo crear, modificar o eliminar tablas o índices.


Actualización de datos en tablas

Para todas ellas, se utilizará un cursor y la función execute(), tal y como vimos anteriormente:

from pysqlite2 import dbapi2 as sqlite
con = sqlite.connect("ticube.bbdd")
cur = con.cursor()
cur.execute("insert into tbconfig (parametro, valor, descripcion) values ('PARAM1', '10', 'Parametro 1')")
cur.lastrowid

En este ejemplo se inserta un registro en una tabla. La última línea retorna el número de la última fila afectada en el cursor. Si sólo se realizó esta operación de escritura, pero no afecta a ningún registro, no retornará nada. Si todo fue bien, retornará (en este caso) 1.

El cursor acumulará operaciones de escritura, a medida que se vayan ejecutando. Pero estas operaciones no se hacen efectivas físicamente en la base de datos. Ello se debe a que se encuentran en una transacción.

Una transacción resulta muy útil cuando hay un encadenamiento de operaciones de escritura que tienen dependencias unas de otras. Cuando se completan todas las operaciones de escritura, si no se ha producido ningún tipo de error, se puede procesar toda la transacción indicando a la conexión de la base de datos que perpetre definitivamente la transacción con todas las operaciones realizadas:

con.commit()

Si se produjera algún error durante la transacción, podrían abortarse todas las operaciones de escritura indicadas en el cursor para evitar inconsistencias de datos. Para ello, una vez se detecte algún error o inconsistencia, se indicaría a la conexión de la base de datos que dé marcha atrás, cancelando todas las operaciones realizadas:

con.rollback()



Actualización de la estructura de la BBDD

También es posible acceder y modificar la estructura de la base de datos, pudiendo crear nuevas tablas, añadir campos a las tablas existentes, crear o borrar índices, etc.

El proceder es idéntico a como hemos visto antes, a través del cursor. El siguiente ejemplo crea una sencilla tabla en nuestra base de datos:

cursor.execute("CREATE TABLE configuracion (parametro VARCHAR(30) NOT NULL, valor VARCHAR(30))")

A diferencia de la actualización de datos en las tablas, este tipo de actualizaciones se realiza directamente, y no requiere de transacciones.


Consultas parametrizadas

Imaginemos que se pretende hacer una carga masiva de datos de una tabla. En lugar de tantas sentencias INSERT como conjuntos de valores (cosa inviable si los datos son dinámicos), se pueden recoger éstos de otra consulta o de un fichero, y asignar dichos valores a unas variables y utilizar el valor de éstas en una única sentencia INSERT:

from pysqlite2 import dbapi2 as sqlite
con = sqlite.connect("ticube.bbdd")
cursor = con.cursor()
parametros=['UNO','DOS',’TRES’]
valores=['1','2','3']
max=len(parámetros)
for i in range(0,max):
   cursor.execute("INSERT INTO configuracion (parametro, valor) VALUES (?, ?)", (parametros[i], valores[i]))
con.commit()

En este ejemplo se insertarán 3 registros en la tabla configuracion, obtenidos de dos secuencias (parametros y valores). El símbolo ? (interrogación), reserva el valor que va a tomar de la lista de parámetros que se especifican a continuación, en el mismo orden en que se enumeran.

Esta capacidad se puede aplicar también a otras consultas de actualización (como UPDATE o DELETE) o a consultas de datos (SELECT).



Cerrar siempre

SQLite es una base de datos muy versátil, ligera y práctica. Ello se consigue sacrificando una parte esencial en entornos distribuídos: la concurrencia. Así es: SQLite no es concurrente. Por ello, para evitar problemas e inconsistencias por la concurrencia, deberemos siempre cerrar todos los cursores y conexiones una vez han sido utilizados y no los utilicemos en cada módulo. Ello libera la conexión para nuevas operaciones.

El método close() se encarga de esta operación:

cursor.close()
con.close()

El orden de apertura y cierre es importante:

1) Abrir conexión
2) Abrir cursor
3) Operaciones
4) …
5) Cerrar cursor
6) Cerrar conexión

Safe Creative #1010197608377