SQL Alchemy es un kit de herramientas SQL y un ORM (Object Relational Mapper) que permite a las clases en Python asociarlas a tablas en bases de datos relacionales. Este tutorial presenta algunos conceptos básicos de SQLAlchemy, parte de un diagrama de clases (sin métodos) y muestra su correspondiente implementación en Python para ser persistida en una base de datos SQL Lite.

El propósito de este tutorial es aplicar el ORM de SQLAlchemy en el desarrollo del proyecto.

El diagrama de clases del cual se partirá se muestra a continuación.

En este diagrama se presenta un modelo básico de los contenidos de una fonoteca, donde la entidad principal es un disco, la cual presenta un conjunto de canciones y se realizó con la ayuda de un productor.

Para comenzar a utilizar SQLAlchemy, primero se debe instalar la librería correspondiente, utilizando la siguiente instrucción.

pip install sqlalchemy

Una vez instalada, se puede verificar la versión actual verificando en Python la siguiente variable::

sqlalchemy.__version__

Para interactuar con una base de datos, primero se debe contar con un motor (Engine), el cual se encarga de administrar los llamados que se realizan para el almacenamiento y recuperación de información, y sobre el que se especifican los datos para el uso de la base de datos. Para utilizar este motor con SQL Lite se debe crear en Python con la siguiente instrucción:

engine = create_engine('sqlite:///baseDeDatos.sqlite')

Donde baseDeDatos representa el nombre del archivo con la base de datos en SQL Lite, la cual, al no tener una ruta de almacenamiento, se crea en la raíz del proyecto. Para cambiar el sitio de almacenamiento de la base de datos se puede especificar una ruta antes del nombre del archivo.

Además del motor, se debe contar con una sesión, la cual implementa el patrón de diseño Unit of Work, un objeto que tendrá las transacciones realizadas en un conjunto de instrucciones para actualizar los cambios en la base de datos. Para definir la sesión se crea una clase con la siguiente instrucción:

Session = sessionmaker(bind=engine)

Esta clase permitirá crear los objetos para cada sesión y realizar el almacenamiento y recuperación de los datos.

Finalmente, se debe crear una clase Base, de la cual heredarán las clases del diagrama presentado con el propósito de definir las propiedades a almacenar sobre la base de datos, y sobre la que se definirá el dialecto con el cual se traducirán las consultas en la base de datos, ya que este puede ser eventualmente personalizado. Para crear la clase base se utiliza la siguiente instrucción

Base = declarative_base()

Estos tres elementos se definen dentro de un archivo que se llamará base.py, de la siguiente manera:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

#engine = create_engine('postgresql://usr:pass@localhost:5432/sqlalchemy')
engine = create_engine('sqlite:///canciones.sqlite')
Session = sessionmaker(bind=engine)

Base = declarative_base()

SQL Alchemy provee sus tipos de datos para modelar la información en la base de datos. Esos tipos de datos permiten hacer un mapa de los atributos o propiedades de la clase en el almacenamiento, y permiten especificar en cuál entidad se van a guardar los datos. Para definir la clase se debe:

  1. Definir la tabla sobre la que se va a almacenar la información utilizando la propiedad __tablename__ con la asignación del nombre de la tabla como una cadena de caracteres.
  2. Definir cada propiedad de la clase asignando un valor de la clase Column y la especificación del tipo de dato que va a almacenar.
  3. Definir las relaciones de la clase con las demás a través de propiedades a las que se les va a asignar una relación.

La primera parte es definir las clases con sus propiedades. Cada clase debe conservar las siguientes características:

  1. Heredar de la clase Base, con el propósito de poder definir sus propiedades como columnas en una tabla de la base de datos.
  2. Incluir una propiedad llamada __tablename__ que hará referencia al nombre de la entidad en la base de datos a mapear
  3. Definir una propiedad llamada id que se utilizará como llave principal en la base de datos y que será una columna de tipo entero
  4. Definir las demás propiedades declaradas como columnas de diferentes tipos.

A continuación se define la clase Productor en el archivo productor.py según estas especificaciones:

from sqlalchemy import Column, Integer, String
from base import Base

class Productor(Base):

   __tablename__ = 'productor'
   id = Column(Integer, primary_key=True)
   nombre = Column(String)
   direccion = Column(String)
   telefono = Column(String)

De acuerdo a la definición, se identifican dos tipos de columnas para SQLAlchemy, las cuales se definen como enteros y cadenas de caracteres, sin embargo, la especificación permite definir otros tipos como Boolean, Numeric o Date.

Las clases pueden definir relaciones de tipo uno a uno, uno a muchos, muchos a uno y muchos a muchos. Para el ejemplo podemos identificar las siguientes relaciones:

  1. Entre Disco y productor, se tiene una relación donde el Disco conoce a su productor, pero el productor no conoce los discos que ha producido, por lo que se puede tomar como un caso especial de una relación uno a uno.
  2. Entre Disco y canción, se tiene una relación de uno a muchos, donde el Disco conoce a sus canciones, y las canciones saben a qué disco pertenecen.

Para modelar la primera relación, Disco tiene una referencia a Productor, la cual se modela con una propiedad llamada productor, que se crea como una columna que hace referencia al identificador del productor. El nombre de la propiedad debe ser el mismo que el de la relación en el diagrama de clases. El identificador del productor es de tipo entero, por lo que la columna se crea como Integer, y, a continuación, se establece una llave foránea hacia dicho identificador con la instrucción ForeignKey('productor.id'), lo que indica que los valores asignados a productor están restringidos por los productores existentes. La creación de la propiedad productor queda definida de la siguiente manera:

   productor = Column(Integer, ForeignKey('productor.id'))

Dado que esta relación es una agregación según el diagrama de clases, al borrar un disco no se tienen consideraciones o ajustes sobre los productores, sin embargo, al borrar un productor, se debe validar que no se encuentre asociado a un disco.

Para la segunda relación, Disco tiene una relación de uno a muchos la clase Canción y Canción tiene una referencia a Disco. Para la relación de Disco hacia Canción se utiliza una propiedad llamada canciones (Se conserva el nombre de la relación en el diagrama de clases), la cual se crea como una relación con la palabra relationship, y a continuación se especifica la clase con la cual se relaciona, como se muestra a continuación:

   canciones = relationship('Cancion')

En la relación también se puede definir qué sucede con los elementos relacionados o cómo se propagan las operaciones sobre un objeto respecto a los relacionados, en este caso, lo que sucede con las canciones, cuando se edita o borra un disco. Al tener una relación de composición de Disco a Cancion, las canciones asociadas a un disco deberían ser borradas cuando se elimina el disco, o una canción no debería existir si no está huérfana, o sea, no está asociado a un disco. Este comportamiento puede ser definido desde la misma relación con un segundo parámetro llamado cascade en la relación, y con la especificación de que ese parámetro puede ser "all, delete, delete-orphan", donde:

En el caso de la relación entre Disco y Canción, se deben aplicar los tres elementos al parámetro cascade, por lo que la relación anterior se actualiza para dejarla especifica como se aprecia en el siguiente ejemplo:

  canciones = relationship('Cancion', cascade='all, delete, delete-orphan')

Para la asociación de Canción a Disco, se modela como una relación de manera similar a como se hizo con Disco y Productor. Definiendo las clase disco.py, se vería de la siguiente manera:

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from base import Base

class Disco(Base):

   __tablename__ = 'disco'
   id = Column(Integer, primary_key=True)
   titulo = Column(String)
   anio = Column(Integer)
   productor = Column(Integer, ForeignKey('productor.id'))
   canciones = relationship('Cancion', cascade='all, delete, delete-orphan')

La clase canción se debe ver de la siguiente manera:

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from base import Base

class Cancion(Base):

   __tablename__ = 'cancion'
   id = Column(Integer, primary_key=True)
   titulo = Column(String)
   interprete = Column(String)
   posicion = Column(Integer)
   disco = Column(Integer, ForeignKey('disco.id'))

En este tutorial no se abordan relaciones de muchos a muchos, sin embargo, como referencia, se puede consultar la documentación del ORM de SQLAlchemy en el siguiente enlace:

https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html

También para comprender mejor las opciones de cascade y realizar mejoras al ejemplo, se puede consultar el siguiente enlace:

https://docs.sqlalchemy.org/en/13/orm/cascades.html

Para almacenar la información en la base de datos se debe crear primero la base de datos, y luego se deben crear y adicionar los objetos a un objeto de la clase Session definida. Esta sesión será la encargada de transferir la información entre la base de datos y Python, y de mantener los objetos sin almacenar, actualizar o borrar hasta que se ejecute la operación commit().

En el siguiente archivo, llamado almacenar.py, se muestra un ejemplo de un programa ejecutable en Python donde se guardan dos discos con sus productores y sus canciones:

from cancion import Cancion
from productor import Productor
from disco import Disco
from base import Session, engine, Base

if __name__ == '__main__':
   #Crea la BD
   Base.metadata.create_all(engine)

   #Abre la sesión
   session = Session()

   #crear productores
   productor1 = Productor(nombre="BMG", direccion="Cra 1 # 2 - 3", telefono="3101122334")
   productor2 = Productor(nombre="Sony", direccion="Cra 4 # 5 - 6", telefono="3105566778")
   session.add(productor1)
   session.add(productor2)
   session.commit()

   # Crear discos
  disco1 = Disco(titulo="Latin Jazz Compilation", anio=2020, productor=productor1.id)
  disco2 = Disco(titulo="Bandas sonoras famosas", anio=2021, productor=productor2.id)
   session.add(disco1)
   session.add(disco2)


   # Crear canciones
   cancion1 = Cancion(titulo="Ajiaco", interprete="Samuel Torres", posicion=1, disco=disco1.id)
   cancion2 = Cancion(titulo="Epílogo", interprete="Aldo López Gavilán", posicion=2, disco=disco1.id)
   cancion3 = Cancion(titulo="Lo que el viento se llevó", interprete="Max Steiner", posicion=3, disco=disco2.id)
   cancion4 = Cancion(titulo="Psicosis", interprete="Bernard Herrmann", posicion=3, disco=disco2.id)

   disco1.canciones=[cancion1, cancion2]
   disco2.canciones=[cancion3, cancion4]

   session.commit()
   session.close()

Las primeras líneas permiten traer las definiciones de las clases establecidas, posteriormente se crea la base de datos y se abre una sesión con las siguientes líneas:

   Base.metadata.create_all(engine)
   session = Session()

Posteriormente, en la sección "#Crear productores" se crean los productores, y se almacenan de inmediato. Este almacenamiento se hace en este punto con el propósito de que se generen los identificadores de los productores al momento de almacenarlos y que al recuperarlos permitan relacionarlos con los discos. Para crear los productores se usan las siguientes instrucciones:

   productor1 = Productor(nombre="BMG", direccion="Cra 1 # 2 - 3", telefono="3101122334")
   productor2 = Productor(nombre="Sony", direccion="Cra 4 # 5 - 6", telefono="3105566778")

Para adicionarlos a la sesión de utilizan las siguientes instrucciones:

   session.add(productor1)
   session.add(productor2)

Para realizar el almacenamiento, se hace commit:

   session.commit()

Al ejecutar la instrucción session.add, los productores fueron provistos de un valor en su propiedad id, por lo que se puede utilizar este identificador para crear las relaciones con los discos.

Luego, en la sección "#Crear discos" se crean los discos, se asignan los productores correspondientes y adicionan a la sesión para generar los identificadores que serán usados en las canciones. Por ejemplo, al disco1 se le asignó un título, año y el primero de los productores en la relación productor, como se muestra a continuación.

  disco1 = Disco(titulo="Latin Jazz Compilation", anio=2020, productor=productor1.id)

Por último, en la sección "# crear canciones", se crean las canciones con sus propiedades y especificando a cuál disco pertenecen, como se muestra a continuación:

   cancion1 = Cancion(titulo="Ajiaco", interprete="Samuel Torres", posicion=1, disco=disco1.id)

Posteriormente las canciones se adicionan al disco, como si la relación canciones recibiera una lista de discos. A continuación se muestra la manara de relacionar las canciones con el disco:

   disco1.canciones=[cancion1, cancion2]

Para el almacenamiento se hace commit. En este punto se debe aclarar que, al tener Disco una relación con Canción con el valor all cuando se definió el parámetro cascade, no es necesario adicionar las canciones a la sesión, ya que estas se almacenan automáticamente al hacer commit.

Para recuperar la información en los objetos, se pueden hacer consultas sobre la base de datos, utilizando las funcionalidades de query, find y all sobre la sesión, como se muestra en el siguiente ejemplo:

from cancion import Cancion
from productor import Productor
from disco import Disco
from base import Session, engine, Base

if __name__ == '__main__':
   session = Session()
   discos = session.query(Disco).all()

   print('Los discos almacenados son:')
   for disco in discos:
       print("Titulo: " + disco.titulo)

       print("Canciones")
       for cancion in disco.canciones:
           print(" - " + cancion.titulo)

       productor = session.query(Productor).filter(Productor.id == disco.productor).all()
       print("Productor: " + productor[0].nombre)

       print("")

  
session.close()

En este ejemplo lo primero que se debe hacer es crear la sesión con la siguiente instrucción:

   session = Session()

Posteriormente se consultan todos los discos con la sentencia query y la opción all. En query se especifica la clase del objeto que se va a llenar con la consulta, y a continuación se adicionan las opciones de consulta, que en este caso, fue la de traer todos los registros. La instrucción quedó así:

discos = session.query(Disco).all()

A continuación se recorren todos los discos y se muestra por pantalla la información asociada a cada uno, comenzando por su título, luego por las canciones, y finalmente por el productor. Dado que la clase Disco tiene una relación de uno a muchos con la clase Canción, al recuperar los discos, se recuperan también las canciones, por eso se pueden recorrer sin hacer llamados a la sesión, como se recorre en el ciclo:

       for cancion in disco.canciones:
           print(" - " + cancion.titulo)

Para ver el productor, dado que está asociado a través de una llave foránea, se hace necesario cargarlo de la base de datos a partir de su identificador. Para esto se ejecuta una consulta sobre Productor, donde se limita con la sentencia filter aquel que cumpla con las condiciones de búsqueda, que en este caso, es que el id del productor coincida con el id del productor que tiene el disco. La consulta se ve de la siguiente manera:

       productor = session.query(Productor).filter(Productor.id == disco.productor).all()

Al final, se cierra la sesión.

Los datos pueden ser actualizados a partir de la actualización de los objetos en Python. Para esto se pueden recuperar los objetos almacenados o los objetos que se tienen en edición y cambiar su información y sus relaciones. En el siguiente ejemplo se muestra el cambio de datos de un disco y de la relación con el productor:

from cancion import Cancion
from productor import Productor
from disco import Disco
from base import Session, engine, Base

if __name__ == '__main__':
   session = Session()
   disco1 = session.query(Disco).get(1)
   productor2 = session.query(Productor).get(2)

   disco1.anio = 2022
   disco1.titulo = "Latin Jazz Season Compilation"
   disco1.productor = productor2.id
   session.add(disco1)
   session.commit()
   session.close()

En este ejemplo, nuevamente el primer paso es crear la sesión:

   session = Session()

Posteriormente se consultan los objetos que se necesitan para la modificación. En este caso se cargan desde la base de datos el disco con el id 1 utilizando el método get(1) sobre la consulta, y el productor creado con el id 2, utilizando el método get(2) sobre la consulta, como se muestra a continuación:

   disco1 = session.query(Disco).get(1)
   productor2 = session.query(Productor).get(2)

A continuación, se actualizan las propiedades de los objetos o las relaciones entre los objetos. Para este caso se cambia el título y el año del disco, y se cambia la relación del productor BMG, representado por el id 1, al productor Sony, representado por el id 2, así:

   disco1.anio = 2022
   disco1.titulo = "Latin Jazz Season Compilation"
   disco1.productor = productor2.id

Para que los cambios se puedan tener en cuenta en la transacción, se adiciona el objeto disco en la sesión, de la misma manera en que se hizo cuando se iba a crear, como se muestra a continuación:

   session.add(disco1)

Y al final, para fijar los cambios en la base de datos, se ejecuta la transacción, como se ve a continuación:

   session.commit()

Luego se pueden ejecutar otras transacciones o se puede cerrar la sesión. Para verificar los cambios, se puede ejecutar de nuevo el ejemplo de recuperación en el apartado anterior.

Para borrar información almacenada, se deben enviar los objetos a borrar y luego ejecutar la transacción. El borrado es una operación que se debe ejecutar con cuidado, debido a que al eliminar objetos, se pueden borrar o no los objetos relacionados según la configuración de las relaciones, y pueden quedar objetos con relaciones inexistentes o se puedan presentar errores al momento del borrado.

En el siguiente ejemplo se muestra el borrado de un disco con sus canciones, y un productor:

from cancion import Cancion
from productor import Productor
from disco import Disco
from base import Session, engine, Base

if __name__ == '__main__':
   session = Session()
   disco2 = session.query(Disco).get(2)
   productor1 = session.query(Productor).get(1)
   session.delete(disco2)
   session.delete(productor1)
   session.commit()
   session.close()

En este ejemplo, nuevamente el primer paso es crear la sesión. Posteriormente se consultan los objetos que se necesitan para la modificación. En este caso se cargan desde la base de datos el disco con el id 2 y el productor creado con el id 1, con los métodos get() que llaman los objetos con su identificador, como se muestra a continuación:

   disco1 = session.query(Disco).get(1)
   productor2 = session.query(Productor).get(2)

A continuación, se realiza el borrado del disco con el llamado que se muestra a continuación:

   session.delete(disco2)

Dado que el disco tiene definido el borrado en cascada, borrará también las canciones asociadas. Posteriormente se borra un productor con la siguiente instrucción:

   session.delete(productor1)

Y al final, para fijar los cambios en la base de datos, se ejecuta la transacción, como se ve a continuación:

   session.commit()

Luego se pueden ejecutar otras transacciones o se puede cerrar la sesión. Para verificar los cambios, se puede ejecutar de nuevo el ejemplo de recuperación en el apartado de ejemplo de recuperación de datos.

En el ejemplo, y según la definición de las clases, se debe tener cuidado con el borrado de productores, dado que, al no estar relacionados a través de la instrucción relationship, es posible borrarlos y dejar los discos con una relación a un productor inexistente. De la misma manera es importante tener en cuenta que al borrar una canción, se debe eliminar de la lista de canciones de un disco, para no dejar a un disco relacionado con una canción inexistente. Para mitigar estos riesgos, se puede utilizar el parámetro back_populates al especificar las relaciones, y su uso se puede consultar en la siguiente referencia:

https://docs.sqlalchemy.org/en/13/orm/tutorial.html#building-a-relationship