pyDAL – Capa de abstracción de base de datos

pyDAL – Capa de abstracción de base de datos



pyDAL (Database Abstraction Layer) es un módulo que permite al programador abstraerse de los varios dialectos del lenguaje de consultas SQL para reemplazarlo por simple código de Python, mucho más simple y legible. Así, vía pyDAL podemos retornar e insertar información de una base de datos independientemente del motor que utilice (por ejemplo, MySQL, PostgreSQL, SQL Server, etc.) empleando siempre el mismo código.

El paquete comenzó como parte del web framework web2py, pero actualmente es un proyecto multipropósito (no limitado a la web) independiente que puede ser incluido en todo tipo de aplicaciones, en Python 2 y 3.

Al utilizar pyDAL tu aplicación podrá comunicarse sin realizar cambios en el código con los siguientes motores de base de datos:

  • SQLite
  • PostgreSQL
  • MySQL
  • Oracle
  • Microsoft SQL Server
  • Firebird
  • DB2
  • Informix
  • Ingres
  • Cubrid
  • Sybase
  • Teradata
  • SAPDB
  • MongoDB
  • IMAP

Como puede observarse, el paquete es incluso capaz de comunicarse con bases de datos NoSQL, como MongoDB y Google NoSQL.

Este artículo no pretende ser sino una introducción a pyDAL, exponiendo sus características y explicando sus generalidades. Para la documentación completa véase Chapter 6: The database abstraction layer en el libro de web2py.

Instalación

La forma más sencilla de adquirir pyDAL es vía pip:

pip install pydal

Como alternativa puedes descargar el código de fuente desde el proyecto en GitHub y ejecutar:

python setup.py install

El código está escrito por completo en Python por lo que no requiere compilación previa.

Primeros pasos

Comencemos por el siguiente código.

from pydal import DAL, Field

db = DAL("sqlite://storage.db")

En primer lugar importamos las clases necesarias (en la mayoría de los casos no serán más que esas dos). DAL es la clase principal, por lo que a continuación creamos una instancia de ella. Field representa un campo dentro de una tabla, como veremos más adelante.

En pyDAL, para conectar a una base de datos utilizamos lo que se conoce como URI (similar a una URL). La primera parte de la cadena, llamada esquema, indica el motor de base de datos. Otros esquemas válidos son mysql://, postgres://, mssql://, etc.

Luego del esquema pasamos a indicar a qué base de datos queremos conectarnos y en dónde se encuentra. Para SQLite basta con especificar el nombre de un archivo. Para el resto de los motores SQL se debe proveer dirección de IP (o dominio) del servidor, nombre de la base de datos, usuario y contraseña. Específicamente, el formato del URI para cada uno de ellos es el siguiente (tomado de la documentación de web2py).

Base de datos Formato URI
SQLite sqlite://nombrebd.db
MySQL mysql://usuario:contraseña@localhost/nombrebd
PostgreSQL postgres://usuario:contraseña@localhost/nombrebd
MSSQL (legacy) mssql://usuario:contraseña@localhost/nombrebd
MSSQL (>=2005) mssql3://usuario:contraseña@localhost/nombrebd
MSSQL (>=2012) mssql4://usuario:contraseña@localhost/nombrebd
Firebird firebird://usuario:contraseña@localhost/nombrebd
Oracle oracle://usuario/contraseña@nombrebd
DB2 db2://usuario:contraseña@nombrebd
Ingres ingres://usuario:contraseña@localhost/nombrebd
Sybase sybase://usuario:contraseña@localhost/nombrebd
Informix informix://usuario:contraseña@nombrebd
Teradata teradata://DSN=dsn;UID=usuario;PWD=contraseña;DATABASE=nombrebd
Cubrid cubrid://usuario:contraseña@localhost/nombrebd
SAPDB sapdb://usuario:contraseña@localhost/nombrebd
IMAP imap://user:contraseña@server:port
MongoDB mongodb://usuario:contraseña@localhost/nombrebd
Google/SQL google:sql://project:instance/database
Google/NoSQL google:datastore
Google/NoSQL/NDB google:datastore+ndb

Nótese que pyDAL requiere que las bases de datos ya estén creadas, a excepción de SQLite, que creará automáticamente el archivo si no existe.

Para conectarse a una base de datos en particular, pyDAL trabaja internamente con algún driver instalado. Por ejemplo, para SQLite utiliza el módulo sqlite3 que forma parte de la librería estándar. Para MySQL, puede trabajar con pymysql o MySQLdb. Para PostgreSQL, psycopg2 o pg8000. Puedes chequear este enlace para ver la lista completa.

Si no hay ningún driver instalado para conectarse a una determinada base de datos, pyDAL exigirá instalar uno. En caso de haber dos módulos para una misma base de datos, podemos forzar a pyDAL a que use uno en particular. Por ejemplo, si ambos psycopg2 y pg8000 se encuentran instalados, podemos indicar explícitamente que queremos que se utilice el segundo.

db = DAL("postgres:pg8000://usuario:contraseña@localhost/nombrebd")

De lo contrario, pyDAL eligirá uno al azar. (Según mi experiencia, para el caso particular de PostgreSQL, psycopg2 tiene precedencia).

Definiendo una tabla

A continuación del código anterior, vamos a definir una tabla llamada person con dos campos o atributos: name y age.

db.define_table(
    "person",
    Field("name"),
    Field("age", type="integer")
)

El nombre, al no especificar el parámetro type, por defecto es una cadena. Por otro lado, la edad indicamos que es un entero.

Los tipos que especificamos son genéricos e independientes del motor de base de datos que se esté utilizando: string, boolean, integer, double, entre otros. pyDAL se encarga de convertir esos tipos genéricos a tipos específicos de la base de datos, pero el programador no debe ocuparse de ello.

En todas las definiciones pyDAL incluye automáticamente un primer campo llamado id que es un número entero único en la tabla e identifica a una determinada fila (lo que en SQL se conoce como clave primaria).

La lista completa de tipos de datos (aquellos que pueden pasarse al parámetro type) soportados de forma automática por web2py es la siguiente.

  • string
  • text
  • blob
  • boolean
  • integer
  • double
  • decimal(n, m)
  • date
  • time
  • datetime
  • password
  • upload
  • reference <tabla>
  • list:string
  • list:integer
  • list:reference <tabla>
  • json
  • bigint
  • big-id
  • big-reference

Además de los tipos convencionales, pyDAL añade algunos interesantes como fechas y horarios (date, time, datetime), contraseñas (password, que cifra automáticamente los datos), archivos (upload), listas (list), referencias a otras tablas (reference) y datos en formato JSON (json).

Incluso el tipo decimal(n, m) (n es el número total de cifras, mientras que m representa los dígitos después de la coma) interactúa automáticamente con el módulo estándar decimal brindando números racionales con exactitud, a diferencia del tipo double que opera con el clásico float.

Si quieres comunicarte con tablas que emplean tipos de datos específicos de una base de datos (por ejemplo, tsvector en PostgreSQL), pyDAL provee un simple método para incluir dichos tipos de datos.

from pydal import DAL, Field, SQLCustomType

# Funciones de conversion desde tipos de Python a tipos
# específicos de una base de datos y viceversa.
def from_tsvector(data):
    return data

def to_tsvector(data):
    return data

tsvector = SQLCustomType(
    type="string",
    native="tsvector",
    encoder=to_tsvector,
    decoder=from_tsvector
)

db.define_table(
     "<nombre>",
     Field("my_vector", type=tsvector)
)

Vía SQLCustomType creamos un nuevo tipo de datos, donde type corresponde a un tipo de datos de pyDAL (listados anteriormente) y native expresa el nombre del tipo específico del motor de base de datos. Las funciones encoder y decoder convierten los datos desde el nuevo tipo al nativo y viceversa.

(Véase Custom field types en la documentación de pyDAL para más información y otros ejemplos).

Vinculando a una tabla preexistente

pyDAL está pensado principalmente para trabajar con tablas creadas por él mismo, pero también soporta la vinculación con tablas preexistentes (con ciertas limitaciones). Ambos casos se llevan a cabo vía define_table().

Cuando invocamos a define_table() pyDAL creará la tabla si no existe. Si ya existe, por defecto chequea que los campos definidos coincidan con los preexistentes. Si la estructura de la tabla en la base de datos difiere de la especificada, el paquete hace automáticamente los cambios pertinentes (en motores SQL vía el comando ALTER TABLE). De modo que al momento de modificar, añadir o remover campos a una tabla, simplemente se realiza desde el código Python y pyDAL se ocupa de alterar la base de datos.

Todas estas operaciones automáticas que el módulo realiza sobre una base de datos se conocen con el nombre de migraciones. Cuando queremos vincular pyDAL con una tabla preexistente es recomendable desactivar las migraciones vía migrate=False.

Otro requerimiento es que toda tabla tenga una clave primaria para identificar a cada una de las filas, de lo contrario pyDAL no podrá vincularse. Si, por ejemplo, la estructura de nuestra tabla preexistente es la siguiente:

CREATE TABLE "person" (
    "person_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "name" CHAR(512),
    "age" INTEGER
)

Su definición en pyDAL se vería así:

# Vincular a una table preexistente.
db.define_table(
    "person",
    # Indicarle a pyDAL cuál es la clave principal.
    Field("person_id", type="id"),
    Field("name"),
    Field("age", type="integer"),
    # Desactivar migraciones.
    migrate=False
)

Retornando e instertando información

Una vez definida nuestra tabla, es momento de añadirle algo de información. Para ello, usamos la función insert:

id = db.person.insert(name="Juan", age=30)

Cada uno de los campos se indica como un argumento junto con su respectivo valor. id es generado automáticamente por la base de datos e identifica a la fila insertada.

Para obtener datos (realizar una consulta), utilizamos la siguiente notación.

db(condiciones)

Por ejemplo, para retornar todas las personas mayores de 18 años, en nuestra tabla, realizamos:

s = db(db.person.age > 18)

s es lo que se llama en web2py un set (pydal.objects.Set) el cual representa una consulta a la base de datos (lo que en SQL se aplicaría con una cláusula WHERE).

Podemos indicar múltiples condiciones para todos los campos que queramos. Podríamos añadir que sean mayores de 18 pero menores o igual de 65.

s = db((db.person.age > 18) & (db.person.age <= 65))

En lugar de and y or se emplean sus equivalentes binarios: & y |, respectivamente. (Nótese que es importante agrupar cada expresión entre paréntesis por la prioridad de los operadores de comparación respecto de los binarios).

Otros ejemplos:

# Aquellos que su nombre comience en "J".
s = db(db.person.name.startswith("J"))
# Aquellos que su nombre termine en "n" o tengan 20 años.
s = db((db.person.name.endswith("n")) | (db.person.age == 20))

Para obtener cada una de las filas que cumplan con la condición age > 18 (continuando con el primer ejemplo) junto con todas sus columnas, usamos la función select.

s = db(db.person.age > 18)
people = s.select()

people es un objeto iterable, una instancia de la clase pydal.objects.Rows. Para imprimir cada una de las filas, utilizamos:

for person in people:
    print(person)

person es una instancia de la clase pydal.objects.Row, por lo que para acceder a una columna o campo determinado, lo especificamos como atributo un atributo.

for person in people:
    print(person.id, person.name, person.age)

También puede accederse a los campos como ítems.

for person in people:
    print(person["id"], person["name"], person["age"])

La clase Row provee algunos métodos para retornar la información como un diccionario o bien en formato JSON o XML.

for person in people:
    print(person.as_dict())
    print(person.as_json())
    print(person.as_xml())

Para seleccionar únicamente algunos campos (y así optimizar la consulta) podemos pasarlos como argumentos. Ejemplos:

# Solo selecciona el nombre.
people = s.select(db.person.name)
# Solo selecciona el ID y la edad.
people = s.select(db.person.id, db.person.age)
# Selecciona todos los campos.
people = s.select(db.person.ALL)

Para obtener la cantidad de filas retornadas de una consulta, usamos count().

count = s.count()

Y para eliminar de la base de datos todas las filas contenidas en s, delete().

# Retorna la cantidad de filas eliminadas.
deleted_rows = s.delete()

Nótese que un set en sí mismo nunca impacta sobre la base de datos sino hasta que se ejecute alguna operación en él (como select() o delete())

Ahora bien, para editar o actualizar (lo que en SQL se denomina UPDATE) información de una tabla se emplea el método update. Opera igual que insert pero sobre las filas contenidas en un set. Entonces, si s contenía todas las personas mayores de 18 años, vamos a hacer que vuelvan a tener 5 años.

# Retorna la cantidad de filas alteradas.
updated_rows = s.update(age=5)

Para cambiar el nombre de todos los menores de 18 años por “Pedro” usamos:

s = db(db.person.age > 18)
s.update(name="Pedro")

O simplemente:

db(db.person.age > 18).update(name="Pedro")

Siempre que sea posible considera utilizar iterselect() en lugar de select(). Es igual que este último pero retorna los valores “sobre la marcha” para ahorrar memoria. (Estrictamente hablando se trata de un generador).

for person in db(db.person.age > 18).iterselect():
    print(person.name)

Para guardar los cambios en la base de datos:

db.commit()

Y para deshacerlos en caso de un error:

db.rollback()

Si necesitas construir una consulta lo suficientemente compleja tal que exceda las posibilidades de pyDAL, puedes utilizar executesql() para ejecutar tu propio código SQL.

people = db.executesql("SELECT name, age FROM person WHERE age > 18")

Nótese que people es en este caso una lista (pues es el resultado directo del driver utilizado y no es alterado por pyDAL), que contiene tuplas cuyos elementos representan a cada uno de las columnas seleccionadas, por ejemplo:

[("Juan", 30), ("Pedro", 26), ...]

Por último, para eliminar una tabla:

db.person.drop()

Más sobre definiciones

Al momento de definir los campos de una tabla, Field provee varios argumentos para implementar soluciones que típicamente se dan al tratar con bases de datos.

Por ejemplo, podemos indicar el valor por defecto para un campo al llamar a insert():

# Campo opcional con valor por defecto.
Field("age", type="integer", default=18)

El valor default es válido únicamente al momento de insertar información. Para actualización vía update() utilícese el parámetro update.

# Valor por defecto para insert() y update().
Field("age", type="integer", default=18, update=18)

O bien forzar al usuario a que siempre explicite su valor, lanzando una excepción en caso contrario.

# Campo requerido.
Field("age", type="integer", required=True)

De modo que:

>>> db.person.insert(name="Pablo")
Traceback (most recent call last):
   ...
RuntimeError: Table: missing required field: age

Para campos del tipo "string", "password" y "upload" es recomendable especificar el parámetro length, que indica su máxima longitud.

# Máximo de 50 caracteres.
Field("name", length=50)

Esto facilita las migraciones entre distintas bases de datos y evita potenciales pérdidas de información.

Los parámetros filter_in y filter_out aceptan funciones para procesar los datos antes de que ingresen a la base de datos y una vez retornados de ella, respectivamente. Por ejemplo, el siguiente código convierte en mayúscula la primera letra del nombre.

def capitalize(name):
    return name.capitalize()

db.define_table(
    "person",
    Field("name", length=50, filter_in=capitalize),
    Field("age", type="integer")
)

De modo que:

# Esto inserta "Pablo".
db.person.insert(name="pablo")

filter_out opera de forma similar:

def upper(name):
    return name.upper()

db.define_table(
    "person",
    Field("name", length=50, filter_in=capitalize, filter_out=upper),
    Field("age", type="integer")
)

# Obtener la primera fila cuyo nombre sea "Pablo".
row = db(db.person.name == "Pablo").select().first()
print(row.name)  # Imprime 'PABLO'

Accediendo al código SQL

La mayoría de las bases de datos soportadas son relacionales, por lo que pyDAL opera internamente construyendo y ejecutando consultas SQL en tiempo real.

El atributo db._lastsql contiene la última consulta ejecutada, por ejemplo:

>>> people = db(db.person.age > 18).select()
>>> db._lastsql
('SELECT "person"."id", "person"."name", "person"."age" FROM "person" WHERE ("person"."age" > 18);', 0.0)

Retorna una tupla que contiene la consulta y cuánto tiempo tardó en ejecutarse.

Para conocer el código SQL generado por alguna operación específica sin ejecutarla necesariamente primero, pyDAL provee las funciones _select, _insert, _update y _delete.

>>> db(db.person.age > 18)._select()
'SELECT "person"."id", "person"."name", "person"."age" FROM "person" WHERE ("person"."age" > 18);'

Estos métodos pueden ser llamados inclusos sin la necesidad de establecer una conexión real con la base de datos, utilizando do_connect=False.

db = DAL("sqlite://storage.db", do_connect=False)

Migraciones

Anteriormente mencionamos al pasar las migraciones, una de las características fundamentales de pyDAL. Las migraciones automáticas ahorran bastante tiempo de trabajo al relevar al programador de la necesidad de alterar manualmente la estructura de una tabla cuando su definición cambia. Sin embargo, sin el debido cuidado pueden causar un verdadero desastre (por ejemplo, cuando conectamos con una tabla preexistente o cuando dos aplicaciones que utilizan la misma base de datos intentan realizar migraciones).

Notarás que cuando las migraciones están activadas (esto ocurre por defecto), pyDAL crea archivos .table para cada una de las tablas de la base de datos. Estos ficheros contienen información sobre la estructura de la tabla de modo que si se realizan cambios pyDAL efectúa las migraciones correspondientes. Por esta razón es importante que no sean alterados ni eliminados. De hecho, lo recomendable es siempre especificar un nombre único para el archivo de cada tabla, por ejemplo:

db.define_table(
    "person",
    Field("name"),
    Field("age", type="integer"),
    migrate="person.table"
)

Al vincular pyDAL con una tabla preexistente, si no quieres que aquél haga cambios en ésta, desactiva las migraciones vía:

# Desactivar migraciones en una tabla específica.
db.define_table(
    ...,
    migrate=False
)

Esto también se aplica cuando dos aplicaciones que utilizan pyDAL acceden a la(s) misma(s) tabla(s). Solo una de ellas debe activar las migraciones.

Para desactivar las migraciones en todas las tablas especificamos migrate_enabled=False al momento de establecer la conexión.

# Desactivar migraciones en todas las tablas.
db = DAL("sqlite://storage.db", migrate_enabled=False)

En algunas circunstancias, es posible que las migraciones fallen, pyDAL lance un error y por ende que tu aplicación quede inutilizada. Antes de entrar en pánico, es importante tener a mano la sección Fixing broken migrations de la documentación de pyDAL para saber cómo solucionar los problemas.

Comentarios finales

Como pretendí mostrar en esta breve introducción, pyDAL tiene capacidades únicas que no se encuentran en otras librerías de Python; soporta gran cantidad de bases de datos, es versátil, fácil de utilizar y sobre todo, muy potente.

Está preparado para cualquier tipo de aplicación que se comunique con una base de datos. Incluso si dicha aplicación no pretende soportar múltiples motores, el paquete permite escribir consultas más complejas en menos tiempo y, especialmente, le brinda mayor legibilidad a tu código.

Para usuarios más avanzados, incluso la tarea de escribir nuevos adaptadores para comunicarse con bases de datos aún no soportadas es bastante sencilla. Véase Notes on new DAL and adapters para algunos ejemplos.

Por último, pyDAL tiene una comunidad muy activa (de hecho la misma comunidad que web2py) que no solo responde a consultas rápidamente, sino que detecta a tiempo errores para corregirlos de la forma más inmediata posible y añadir nuevas capacidades según la demanda del mundo del IT. Y claro que, como siempre, ante cualquier consulta sobre el paquete u otras cuestiones relacionadas con Python puedes crear un nuevo tema en nuestro foro.



Deja un comentario