Programación con múltiples tablas

Ahora vamos a rehacer de nuevo el programa araña de Twitter usando dos tablas, las claves primarias, y las claves de referencia, como hemos descrito antes. He aquí el código de la nueva versión del programa:

Este programa empieza a resultar un poco complicado, pero ilustra los patrones de diseño que debemos usar cuando utilizamos claves de enteros para enlazar tablas. Esos patrones básicos son:

  1. Crear tablas con claves primarias y restricciones.
  2. Cuando tenemos una clave lógica para una persona (es decir, un nombre de cuenta) y necesitamos el valor del id de esa persona, dependiendo de si esa persona ya está en la tabla Personas o no, tendremos que: (1) buscar la persona en la tabla Personas y recuperar el valor de id para esa persona, o (2) añadir la persona a la tabla Personas y obtener el valor del id para la fila recién añadida.
  3. Insertar la fila que indica la relación de “seguimiento”.

Tranquilo que vamos a explicar todos los puntos de uno en uno.

Restricciones en tablas de bases de datos

Conforme diseñamos la estructura de la tabla, podemos indicar al sistema de la base de datos que queremos aplicar algunas reglas. Estas reglas nos ayudarán a evitar errores y a introducir correctamente los datos en las tablas. Cuando creamos nuestras tablas:

Estamos indicando que la columna nombre de la tabla Personas debe ser UNIQUE (única). Además indicamos que la combinación de los dos números de cada fila de la tabla Seguimientos debe ser también única. Estas restricciones evitan que cometamos errores como añadir la misma relación entre las mismas personas más de una vez.

Después, podemos aprovechar estas restricciones en el código siguiente:

Aquí añadimos la clausula OR IGNORE en la sentencia INSERT para indicar que si este INSERT en particular causara una violación de la regla “el nombre debe ser único”, el sistema de la base de datos está autorizado a ignorar el INSERT. De esta forma, estamos usando las restricciones de la base de datos como una red de seguridad para asegurarnos de que no hacemos algo incorrecto sin darnos cuenta.

De manera similar, el código siguiente se asegura de que no añadamos exactamente la misma relación de Seguimiento dos veces.

De nuevo, simplemente estamos indicándole a la base de datos que ignore cualquier intento de INSERT si éste viola la restricción de unicidad que hemos especificado para cada fila de Seguimientos.

Recuperar y/o insertar un registro

Cuando pedimos al usuario una cuenta de Twitter, si la cuenta ya existe debemos averiguar el valor de su id. Si la cuenta no existe aún en la tabla Personas, debemos insertar el registro y obtener el valor del id de la fila recién insertada.

Éste es un diseño muy habitual y se utiliza dos veces en el programa anterior. Este código muestra cómo se busca el id de la cuenta de un amigo, una vez extraído su screen_name desde un nodo de usuario del JSON recuperado desde Twitter.

Dado que con el tiempo será cada vez más probable que la cuenta ya figure en la base de datos, primero comprobaremos si el registro existe en Personas, usando una sentencia SELECT.

Si todo sale bien dentro de la sección try recuperaremos el registro mediante
fetchone() y luego extraeremos el primer (y único) elemento de la tupla devuelta, que almacenaremos en amigo_id.

Si el SELECT falla, el código fetchone()[0] también fallará, y el control será
transferido a la sección except.

Si terminamos en el código del except, eso sólo significa que la fila no se ha
encontrado en la table, de modo que debemos insertarla. Usamos INSERT OR
IGNORE para evitar posibles errores, y luego llamamos a commit() para forzar a la base de datos a que se actualice de verdad. Después de que se ha realizado
la escritura, podemos comprobar el valor de cur.rowcount, para saber cuántas
filas se han visto afectadas. Como estamos intentando insertar una única fila, si el
número de filas afectadas es distinto de 1, se habría producido un error.

Si el INSERT tiene éxito, podemos usar cur.lastrowid para averiguar el valor que la base de datos ha asignado a la columna id en nuestra fila recién creada.

Almacenar las relaciones entre amigos

Una vez que sabemos el valor de la clave tanto para el usuario de Twitter como para el amigo que hemos extraído del JSON, resulta sencillo insertar ambos números en la tabla de Seguimientos con el código siguiente:

Observa como dejamos que sea la base de datos quien se ocupe de evitar la “inserción duplicada” de una relación, mediante la creación de una tabla con una restricción de unicidad, de modo que luego en nuestra sentencia INSERT tan sólo añadimos o ignoramos.

Aquí está un ejemplo de la ejecución de este programa:

Comenzamos con la cuenta de drchuck y luego dejamos que el programa escoja de forma automática las siguientes dos cuentas para recuperar y añadir a nuestra base de datos.

Las siguientes son las primeras filas de las tablas Personas y Seguimientos después de terminar la ejecución anterior:

Puedes ver los campos id, nombre, visitado desde la tabla Personas, y también los números de ambos extremos de la relación en la tabla Seguimientos. En la tabla Personas, vemos que las primeras tres personas ya han sido visitadas y que sus datos han sido recuperados. Los datos de la tabla Seguidores indican que drchuck (usuario 1) es amigo de todas las personas que se muestran en las
primeras cinco filas. Esto tiene sentido, ya que los primeros datos que recuperamos y almacenamos fueron los amigos de Twitter de drchuck. Si imprimieras más filas de la tabla Seguimientos verías también los amigos de los usuarios 2 y 3.