SQLite¶

¿Qué es SQLite?¶

SQLite es un sistema gestor de bases de datos relacional contenido en una pequeña librería escrita en C (~275kB) y que usa un único fichero para almacenar la base de datos.

A continuación se muestran algunas de sus principales características:

• Tablas, índices, «triggers» y vistas ilimitadas.
• Hasta 32K columnas en una tabla y filas ilimitadas.
• Índices multi-columna.
• Restricciones de tipo CHECK, UNIQUE, NOT NULL y FOREIGN KEY.
• Transacciones planas usando BEGIN, COMMIT y ROLLBACK.
• Transacciones anidadas usando SAVEPOINT, RELEASE y ROLLBACK TO.
• Subconsultas.
• «Joins» de hasta 64 relaciones.
• «Joins» de tipo «left», «right» y «full outer».
• Uso de DISTINCT, ORDER BY, GROUP BY, HAVING, LIMIT y OFFSET.
• Uso de UNION, UNION ALL, INTERSECT y EXCEPT.
• Una amplia librería de funciones SQL estándar.
• Funciones de agregación.
• Funciones de ventana.
• Por supuesto el uso de UPDATE, DELETE e INSERT.
• Cláusula UPSERT.
• Soporte para valores JSON.

Y muchas otras que se pueden consultar en la página del proyecto.

Tipos de datos¶

SQLite dispone de un conjunto muy reducido de tipos de datos. Aunque hay alguno más, con los siguientes nos será suficiente para la inmensa mayoría de diseños de bases de datos que podamos necesitar:

• INTEGER para valores enteros.
• REAL para valores flotantes.
• TEXT para cadenas de texto.

Booleanos¶

SQLite no dispone de un tipo especial para valores booleanos. Usaremos el tipo INTEGER para representar true como 1 y false como 0.

Fecha y hora¶

SQLite no dispone de un tipo especial para fecha y hora. Usaremos el tipo TEXT para representar fecha y hora en formato ISO8601 como cadenas de texto 'YYYY-MM-DD HH:MM:SS.SSS'.

Sin embargo en SQLite sí que existen múltiples funciones para trabajar con fecha y hora.

Conexión a la base de datos¶

Una base de datos SQLite no es más que un fichero binario, habitualmente con extensión .db o .sqlite. Antes de realizar cualquier operación es necesario «conectar» con este fichero.

La conexión a la base de datos se realiza a través de la función connect() que espera recibir la ruta al fichero de base de datos y devuelve un objeto de tipo Connection.

>>> import sqlite3#(1)!

>>> con = sqlite3.connect('python.db')#(2)!
>>> con#(3)!
<sqlite3.Connection at 0x105953c40>

1. Importamos el módulo.
   Se llama sqlite3 (no olvidarse del 3 al final).
2. Especificamos una ruta (relativa o absoluta) al fichero de base de datos.
3. Comprobamos que el objeto devuelto es una instancia de la clase Connection.

La primera vez que conectamos a una base de datos (fichero) inexistente, Python lo creará sin contenido alguno:

$ file python.db
python.db: empty

Una vez que disponemos de la conexión ya podemos obtener un Cursor mediante la función cursor(). Un cursor se podría ver como un manejador para realizar operaciones sobre la base de datos:

>>> cur = con.cursor()
>>> cur
<sqlite3.Cursor object at 0x1057d4240>

Creación de tablas¶

Durante toda esta sección vamos a trabajar con una tabla de ejemplo que representa las distintas versiones de Python que han sido liberadas.

Empecemos creando la tabla pyversions a través de un código SQL similar al siguiente:

CREATE TABLE pyversions (
    branch TEXT PRIMARY KEY,
    released_at_year INTEGER,
    released_at_month INTEGER,
    release_manager TEXT
)

Haremos uso del cursor creado para ejecutar estas instrucciones:

>>> sql = """CREATE TABLE pyversions (
...     branch TEXT PRIMARY KEY,
...     released_at_year INTEGER,
...     released_at_month INTEGER,
...     release_manager TEXT
... )"""#(1)!

>>> cur.execute(sql)#(2)!
<sqlite3.Cursor object at 0x1057d4240>

1. • Es habitual usar comillas triples para definir sentencias SQL dentro de código Python.
   • No es necesario añadir punto y coma ; al final de la sentencia SQL cuando usamos el módulo sqlite3 salvo que se trate de scripts.
2. Un cursor tiene un método execute() que permite ejecutar sentencias SQL.

Ya hemos creado la tabla pyversions de manera satisfactoria.

Si comprobamos ahora el contenido del fichero python.db podemos observar que nos indica la versión de SQLite y la última escritura:

$ file python.db
python.db: SQLite 3.x database, last written using SQLite version 3047001, file counter 1, database pages 3, cookie 0x1, schema 4, UTF-8, version-valid-for 1

Añadiendo datos¶

Para tener contenido sobre el que trabajar, vamos primeramente a añadir ciertos datos a la tabla. Como básicamente seguimos ejecutando sentencias SQL (en este caso de inserción) podemos volver a hacer uso de la función execute():

>>> sql = 'INSERT INTO pyversions VALUES ("2.6", 2008, 10, "Barry Warsaw")'

>>> cur.execute(sql)
<sqlite3.Cursor object at 0x1057d4240>

Aparentemente todo ha ido bien. Vamos a usar —temporalmente— la herramienta cliente sqlite3¹ para ver el contenido de la tabla:

$ sqlite3 python.db "select * from pyversions"

Resulta que tenemos una salida vacía. ¿No hay ningún registro? Esto se debe a que la transacción está aún pendiente de confirmar. Para consolidarla tendremos que hacer uso de la función commit():

>>> con.commit()#(1)!

1. • Nótese que el método commit() pertenece a la conexión y no a al cursor.
   • Las transacciones pueden consolidarse con commit() o deshacerse con rollback().

Ahora podemos comprobar que sí se han guardado los datos correctamente:

$ sqlite3 python.db "select * from pyversions"
2.6|2008|10|Barry Warsaw

Autocommit¶

Cuando creamos la conexión a la base de datos podemos pasar como argumento autocommit=True de tal forma que no sea necesario invocar explícitamente a commit():

>>> con = sqlite3.connect('python.db', autocommit=True)

Así, cada vez que ejecutemos operaciones de modificación sobre la base de datos se lanzará automáticamente el método commit() confirmando los cambios indicados.

Inserción parametrizada¶

Supongamos que no sabemos, a priori, los datos que vamos a insertar en la tabla puesto que provienen del usuario o de otra fuente externa. En este caso cabría plantearse cuál es la mejor opción para parametrizar la consulta.

En una primera aproximación podríamos pensar en utilizar un f-string.

Por ejemplo vamos a insertar un nuevo registro a partir de ciertas variables:

>>> branch = 3.11
>>> released_at_year = 2022
>>> released_at_month = 10
>>> release_manager = 'Pablo Galindo Salgado'

>>> sql = f'INSERT INTO pyversions VALUES ({branch}, {released_at_year}, {released_at_month}, {release_manager})'
>>> sql
'INSERT INTO pyversions VALUES (3.11, 2022, 10, Pablo Galindo Salgado)'

>>> cur.execute(sql)
Traceback (most recent call last):
  Cell In[7], line 1
    cur.execute(sql)
OperationalError: near "Galindo": syntax error

¿Qué ha ocurrido? Obtenemos un error porque el contenido de «release manager» 'Pablo Galindo Salgado' es una cadena de texto y no puede contener espacios (en SQL).

Una solución a este problema sería detectar qué campos necesitan comillas e incorporarlas de forma manual. Pero una solución más robusta y efectiva es utilizar los «placeholders» de SQLite.

Un «placeholder»² se introduce en la sentencia SQL y se sustituye a posteriori por el correspondiente valor indicado al ejecutar. La gran ventaja de este enfoque es que no hay que preocuparse del tipo de dato ya que el módulo sqlite3 se encarga de introducir comillas o formatear según corresponda.

Veamos cómo sería la inserción anterior utilizando esta técnica, usando...

>>> branch = 3.11
>>> released_at_year = 2022
>>> released_at_month = 10
>>> release_manager = 'Pablo Galindo Salgado'

>>> sql = 'INSERT INTO pyversions VALUES (?, ?, ?, ?)'#(1)!
>>> cur.execute(sql, [
...    branch,
...    released_at_year,
...    released_at_month,
...    release_manager
... ])#(2)!
<sqlite3.Cursor object at 0x1057d4240>

>>> branch = 3.11
>>> released_at_year = 2022
>>> released_at_month = 10
>>> release_manager = 'Pablo Galindo Salgado'

>>> sql = 'INSERT INTO pyversions VALUES (:branch, :year, :month, :manager)'#(1)!
>>> cur.execute(sql, {
...    'branch': branch,
...    'year': released_at_year,
...    'month': released_at_month,
...    'manager': release_manager
... })#(2)!
<sqlite3.Cursor object at 0x1057d4240>

Inserciones en lote¶

Supongamos ahora un escenario en el que disponemos de datos en formato tabular y debemos cargarlos directamente en una tabla de SQLite.

Por ejemplo partimos del siguiente fichero:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

branch,year,month,manager
2.6,2008,10,Barry Warsaw
2.7,2010,7,Benjamin Peterson
3.0,2008,12,Barry Warsaw
3.1,2009,6,Benjamin Peterson
3.2,2011,2,Georg Brandl
3.3,2012,9,Georg Brandl
3.4,2014,3,Larry Hastings
3.5,2015,9,Larry Hastings
3.6,2016,12,Ned Deily
3.7,2018,6,Ned Deily
3.8,2019,10,Łukasz Langa
3.9,2020,10,Łukasz Langa
3.10,2021,10,Pablo Galindo Salgado
3.11,2022,10,Pablo Galindo Salgado
3.12,2023,10,Thomas Wouters
3.13,2024,10,Thomas Wouters

La primera aproximación que se nos podría venir a la cabeza es utilizar una inserción parametrizada por cada línea del fichero de entrada:

>>> sql = 'INSERT INTO pyversions VALUES (?, ?, ?, ?)'

>>> with open('pyversions.csv') as f:
...     f.readline()#(1)!
...     for line in f:
...         pyversion = line.strip().split(',')
...         cur.execute(sql, pyversion)
...     con.commit()
...

1. Saltamos la cabecera.

Pero este módulo permite atacar el problema desde otro enfoque utilizando la función executemany().

Veamos una reimplementación del ejemplo anterior, usando un...

>>> with open('pyversions.csv') as f:
...     f.readline()#(1)!
...     pyversions = [line.strip().split(',') for line in f]
...

>>> pyversions#(2)!
[['2.6', '2008', '10', 'Barry Warsaw'],
 ['2.7', '2010', '7', 'Benjamin Peterson'],
 ['3.0', '2008', '12', 'Barry Warsaw'],
 ['3.1', '2009', '6', 'Benjamin Peterson'],
 ['3.2', '2011', '2', 'Georg Brandl'],
 ['3.3', '2012', '9', 'Georg Brandl'],
 ['3.4', '2014', '3', 'Larry Hastings'],
 ['3.5', '2015', '9', 'Larry Hastings'],
 ['3.6', '2016', '12', 'Ned Deily'],
 ['3.7', '2018', '6', 'Ned Deily'],
 ['3.8', '2019', '10', 'Łukasz Langa'],
 ['3.9', '2020', '10', 'Łukasz Langa'],
 ['3.10', '2021', '10', 'Pablo Galindo Salgado'],
 ['3.11', '2022', '10', 'Pablo Galindo Salgado'],
 ['3.12', '2023', '10', 'Thomas Wouters'],
 ['3.13', '2024', '10', 'Thomas Wouters']]    

>>> sql = 'INSERT INTO pyversions VALUES (?, ?, ?, ?)'#(3)!
>>> cur.executemany(sql, pyversions)
<sqlite3.Cursor object at 0x103432bc0>

>>> con.commit()

>>> with open('pyversions.csv') as f:
...     fields = f.readline().strip().split(',')#(1)!
...     pyversions = [{f: v for f, v in zip(fields, line.strip().split(','))} for line in f]
...

>>> pyversions#(2)!
[{'branch': '2.6', 'manager': 'Barry Warsaw', 'month': '10', 'year': '2008'},
 {'branch': '2.7', 'manager': 'Benjamin Peterson', 'month': '7', 'year': '2010'},
 {'branch': '3.0', 'manager': 'Barry Warsaw', 'month': '12', 'year': '2008'},
 {'branch': '3.1', 'manager': 'Benjamin Peterson', 'month': '6', 'year': '2009'},
 {'branch': '3.2', 'manager': 'Georg Brandl', 'month': '2', 'year': '2011'},
 {'branch': '3.3', 'manager': 'Georg Brandl', 'month': '9', 'year': '2012'},
 {'branch': '3.4', 'manager': 'Larry Hastings', 'month': '3', 'year': '2014'},
 {'branch': '3.5', 'manager': 'Larry Hastings', 'month': '9', 'year': '2015'},
 {'branch': '3.6', 'manager': 'Ned Deily', 'month': '12', 'year': '2016'},
 {'branch': '3.7', 'manager': 'Ned Deily', 'month': '6', 'year': '2018'},
 {'branch': '3.8', 'manager': 'Łukasz Langa', 'month': '10', 'year': '2019'},
 {'branch': '3.9', 'manager': 'Łukasz Langa', 'month': '10', 'year': '2020'},
 {'branch': '3.10', 'manager': 'Pablo Galindo Salgado', 'month': '10', 'year': '2021'},
 {'branch': '3.11', 'manager': 'Pablo Galindo Salgado', 'month': '10', 'year': '2022'},
 {'branch': '3.12', 'manager': 'Thomas Wouters', 'month': '10', 'year': '2023'},
 {'branch': '3.13', 'manager': 'Thomas Wouters', 'month': '10', 'year': '2024'}]

>>> sql = 'INSERT INTO pyversions VALUES (:branch, :year, :month, :manager)'#(3)!
>>> cur.executemany(sql, pyversions)
<sqlite3.Cursor object at 0x103432bc0>

>>> con.commit()

En cualquiera de los casos anteriores el resultado sería el mismo y los registros quedan correctamente insertados en la base de datos:

$ sqlite3 python.db "SELECT * FROM pyversions"
2.6|2008|10|Barry Warsaw
2.7|2010|7|Benjamin Peterson
3.0|2008|12|Barry Warsaw
3.1|2009|6|Benjamin Peterson
3.2|2011|2|Georg Brandl
3.3|2012|9|Georg Brandl
3.4|2014|3|Larry Hastings
3.5|2015|9|Larry Hastings
3.6|2016|12|Ned Deily
3.7|2018|6|Ned Deily
3.8|2019|10|Łukasz Langa
3.9|2020|10|Łukasz Langa
3.10|2021|10|Pablo Galindo Salgado
3.11|2022|10|Pablo Galindo Salgado
3.12|2023|10|Thomas Wouters
3.13|2024|10|Thomas Wouters

>>> import random
>>> import sqlite3

>>> data = [#(1)!
...     [str(random.randint(1, 100)) for _ in range(10)]
...     for _ in range(1_000_000)
... ]

>>> con = sqlite3.connect('test.db')
>>> cur = con.cursor()
>>> sql = """
...     CREATE TABLE numbers
...     (N1 INTEGER, N2 INTEGER, N3 INTEGER, N4 INTEGER, N5 INTEGER,
...      N6 INTEGER, N7 INTEGER, N8 INTEGER, N9 INTEGER, N10 INTEGER)
... """
>>> cur.execute(sql)
<sqlite3.Cursor object at 0x10aab4dc0>

>>> sql = """INSERT INTO numbers
... (N1, N2, N3, N4, N5, N6, N7, N8, N9, N10)
... VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"""

>>> # ===== EXECUTE =====

>>> %%timeit
... for row in data:
...     cur.execute(sql, row)
...
2.06 s ± 7.5 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

>>> # ===== EXECUTEMANY =====

>>> %%timeit
... cur.executemany(sql, data)
...
1.86 s ± 9.71 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

>>> round((1 - (1.86/2.06)) * 100)
10

Identificador de fila¶

En el comportamiento por defecto de una base de datos SQLite todas las tablas disponen de una columna «oculta» denominada «rowid» o identificador de fila.

Esta columna se va rellenando de forma automática con valores enteros únicos y puede utilizarse como clave primaria de los registros.

Para poder visualizar (o utilizar) esta columna es necesario indicarlo explícitamente en la consulta:

$ sqlite3 python.db "SELECT rowid, * FROM pyversions"
1|2.6|2008|10|Barry Warsaw
2|2.7|2010|7|Benjamin Peterson
3|3.0|2008|12|Barry Warsaw
4|3.1|2009|6|Benjamin Peterson
5|3.2|2011|2|Georg Brandl
6|3.3|2012|9|Georg Brandl
7|3.4|2014|3|Larry Hastings
8|3.5|2015|9|Larry Hastings
9|3.6|2016|12|Ned Deily
10|3.7|2018|6|Ned Deily
11|3.8|2019|10|Łukasz Langa
12|3.9|2020|10|Łukasz Langa
13|3.10|2021|10|Pablo Galindo Salgado
14|3.11|2022|10|Pablo Galindo Salgado
15|3.12|2023|10|Thomas Wouters
16|3.13|2024|10|Thomas Wouters

Cerrando la conexión¶

Al igual que ocurre con un fichero de texto, es necesario cerrar la conexión abierta para que se liberen los recursos asociados y se debloquee la base de datos.

La forma más directa de hacer esto sería:

>>> con.close()

Gestor de contexto¶

En SQLite también es posible utilizar un gestor de contexto sobre la conexión, que funciona de la siguiente manera:

• Si todo ha ido bien ejecutará un «commit» al final del bloque.
• Si ha habido alguna excepción ejecutará un «rollback»³ para que todo quede como al principio y deshacer los posibles cambios efectuados.

Analicemos el siguiente ejemplo:

>>> with con:
...     cur.execute('INSERT INTO pyversions VALUES ("3.12", 2023, 10, "Thomas Wouters")')
...     cur.execute('INSERT INTO pyversions VALUES ("3.12", 2024, 10, "Thomas Wouters")')
...
Traceback (most recent call last):
  Cell In[1], line 3
    cur.execute('INSERT INTO pyversions VALUES ("3.12", 2024, 10, "Thomas Wouters")')
IntegrityError: UNIQUE constraint failed: pyversions.branch

Se ha elevado una excepción de tipo IntegrityError indicando que hay valores duplicados en el campo branch ya que se trata de una clave primaria y sus valores deben ser únicos. Pero dado que estamos en un gestor de contexto, se realiza un «rollback» de las acciones previas y la base de datos queda en el mismo estado anterior. Es decir, no se inserta ninguna fila nueva.

Consultas¶

A la hora de realizar consulas en sqlite3 debemos tener en cuenta qué salida estamos buscando:

• Registros como tuplas.
• Registros como filas.

Registros como tuplas¶

La ejecución de una consulta —desde un cursor— retorna un objeto iterable. Por defecto el contenido de dicho iterable son tuplas donde cada tupla representa una fila de la tabla consultada.

Veamos un ejemplo consultando toda la tabla pyversions:

>>> for row in cur.execute('SELECT * FROM pyversions'):
...     print(row)
...
('2.6', 2008, 10, 'Barry Warsaw')
('2.7', 2010, 7, 'Benjamin Peterson')
('3.0', 2008, 12, 'Barry Warsaw')
('3.1', 2009, 6, 'Benjamin Peterson')
('3.2', 2011, 2, 'Georg Brandl')
('3.3', 2012, 9, 'Georg Brandl')
('3.4', 2014, 3, 'Larry Hastings')
('3.5', 2015, 9, 'Larry Hastings')
('3.6', 2016, 12, 'Ned Deily')
('3.7', 2018, 6, 'Ned Deily')
('3.8', 2019, 10, 'Łukasz Langa')
('3.9', 2020, 10, 'Łukasz Langa')
('3.10', 2021, 10, 'Pablo Galindo Salgado')
('3.11', 2022, 10, 'Pablo Galindo Salgado')
('3.12', 2023, 10, 'Thomas Wouters')
('3.13', 2024, 10, 'Thomas Wouters')

También tenemos la opción de utilizar las funciones fetchone() y fetchall() para obtener una o todas las filas de la consulta:

>>> query = cur.execute('SELECT * FROM pyversions')

>>> query.fetchone()#(1)!
('2.6', 2008, 10, 'Barry Warsaw')

>>> query.fetchall()
[('2.7', 2010, 7, 'Benjamin Peterson'),
 ('3.0', 2008, 12, 'Barry Warsaw'),
 ('3.1', 2009, 6, 'Benjamin Peterson'),
 ('3.2', 2011, 2, 'Georg Brandl'),
 ('3.3', 2012, 9, 'Georg Brandl'),
 ('3.4', 2014, 3, 'Larry Hastings'),
 ('3.5', 2015, 9, 'Larry Hastings'),
 ('3.6', 2016, 12, 'Ned Deily'),
 ('3.7', 2018, 6, 'Ned Deily'),
 ('3.8', 2019, 10, 'Łukasz Langa'),
 ('3.9', 2020, 10, 'Łukasz Langa'),
 ('3.10', 2021, 10, 'Pablo Galindo Salgado'),
 ('3.11', 2022, 10, 'Pablo Galindo Salgado'),
 ('3.12', 2023, 10, 'Thomas Wouters'),
 ('3.13', 2024, 10, 'Thomas Wouters')]

1. Nótese que la llamada a fetchone() hace que quede «una fila menos» que recorrer. Es un comportamiento totalmente análogo al de la lectura de una línea en un fichero.

Registros como filas¶

El módulo sqlite3 también nos permite obtener los resultados de una consulta como objetos de tipo Row lo que facilita acceder a los valores de cada registro tanto por el índice como por el nombre de la columna.

Para «activar» este modo tendremos que fijar el valor de la factoría de filas en la conexión:

>>> con = sqlite3.connect('python.db')
>>> con.row_factory = sqlite3.Row

Veamos un ejemplo consultando toda la tabla pyversions:

>>> for row in cur.execute('SELECT * FROM pyversions'):
...     print(row)
...
<sqlite3.Row object at 0x10345c8e0>
<sqlite3.Row object at 0x103cc0940>
<sqlite3.Row object at 0x103bcbf10>
<sqlite3.Row object at 0x103cc0940>
<sqlite3.Row object at 0x10345c8e0>
<sqlite3.Row object at 0x103cc0940>
<sqlite3.Row object at 0x103bcbf10>
<sqlite3.Row object at 0x103cc0940>
<sqlite3.Row object at 0x10345c8e0>
<sqlite3.Row object at 0x103cc0940>
<sqlite3.Row object at 0x103bcbf10>
<sqlite3.Row object at 0x103cc0940>
<sqlite3.Row object at 0x10345c8e0>
<sqlite3.Row object at 0x103cc0940>
<sqlite3.Row object at 0x103bcbf10>
<sqlite3.Row object at 0x103cc0940>

Vemos que los objetos devueltos son de tipo sqlite3.Row pero no obtenemos gran información sobre su contenido.

A continuación se muestra el acceso a los valores del último objeto sqlite3.Row mediante...

>>> row
<sqlite3.Row object at 0x103cc0940>

>>> row['branch']
'3.13'
>>> row['released_at_year']
2024
>>> row['released_at_month']
10
>>> row['release_manager']
'Thomas Wouters'

>>> row.keys()#(1)!
['branch', 'released_at_year', 'released_at_month', 'release_manager']

>>> branch, year, month, manager = row
>>> branch, year, month, manager
('3.13', 2024, 10, 'Thomas Wouters') 

>>> row
<sqlite3.Row object at 0x103cc0940>

>>> row[0]
'3.13'
>>> row[1]
2024
>>> row[2]
10
>>> row[3]
'Thomas Wouters'

>>> branch, year, month, manager = row
>>> branch, year, month, manager
('3.13', 2024, 10, 'Thomas Wouters') 

Número de filas¶

Hay ocasiones en las que lo que necesitamos obtener no es el dato en sí mismo, sino el número de filas vinculadas a una determinada consulta. En este sentido hay varias alternativas:

>>> query = cur.execute('SELECT * FROM pyversions')
>>> len(query.fetchall())
16

>>> query = cur.execute('SELECT COUNT(*) FROM pyversions')
>>> query.fetchone()[0]#(1)!
16

Comprobando si hay resultados¶

La aplicación del operador morsa en las consultas mediante sqlite3 es muy habitual ya que facilita la comprobación de resultados.

Veamos una posible implementación en el siguiente ejemplo...

>>> con = sqlite3.connect('python.db')
>>> cur = con.cursor()

>>> res = cur.execute('SELECT * FROM pyversions WHERE branch=4.0')

>>> if row := res.fetchone():
...     print(row)
... else:
...     print('Empty query')
...
Empty query

>>> con = sqlite3.connect('python.db')
>>> cur = con.cursor()

>>> res = cur.execute('SELECT * FROM pyversions WHERE branch=3.0')

>>> if row := res.fetchone():
...     print(row)
... else:
...     print('Empty query')
...
('3.0', 2008, 12, 'Barry Warsaw')

Otras funcionalidades¶

El módulo sqlite3 dispone de otras funcionalidades realmente interesantes que serán analizadas en este apartado.

Tablas en memoria¶

Existe la posibilidad de trabajar con tablas en memoria sin necesidad de tener un fichero en disco.

Veamos un ejemplo muy sencillo:

>>> con = sqlite3.connect(':memory:')
>>> cur = con.cursor()

>>> sql = 'CREATE TABLE temp (id INTEGER PRIMARY KEY, value TEXT)'
>>> cur.execute(sql)
<sqlite3.Cursor object at 0x103b878c0>

>>> sql = 'INSERT INTO temp VALUES (1, "X")'
>>> cur.execute(sql)#(1)!
<sqlite3.Cursor object at 0x103b878c0>

>>> for row in cur.execute('SELECT * FROM temp'):
...     print(row)
...
(1, 'X')

1. Al utilizar tablas en memoria existe un «autocommit» por defecto.

Esta aproximación puede ser interesante para escenarios donde no nos importe la persistencia, ya que los datos no serán volcados a disco.

Claves autoincrementales¶

Es muy habitual encontrar en la definición de una tabla un campo identificador numérico entero que actúe como clave primaria y se le asignen valores automáticamente.

Para implementar este esquema en SQLite debemos simplemente definir una columna de tipo INTEGER PRIMARY KEY. A partir de ahí, en cualquier operación de inserción, si no especificamos un valor explícito para dicha columna, se rellenará automáticamente con un entero sin usar, típicamente uno más que el último valor generado.

A continuación se muestra un ejemplo de aplicación de claves autoincrementales mediante una tabla en memoria que almacena ciudades y sus geolocalizaciones:

>>> con = sqlite3.connect(':memory:')
>>> cur = con.cursor()

>>> cur.execute('''CREATE TABLE cities (
... id INTEGER PRIMARY KEY,
... city TEXT UNIQUE,
... latitude REAL,
... longitude REAL)''')
<sqlite3.Cursor at 0x107139bc0>

>>> cur.execute('''INSERT INTO
... cities (city, latitude, longitude)
... VALUES ("Tokyo", 35.652832, 139.839478)''')#(1)!
<sqlite3.Cursor at 0x107139bc0>

>>> result = cur.execute('SELECT * FROM cities')
>>> result.fetchall()
[(1, 'Tokyo', 35.652832, 139.839478)]

>>> cur.execute('''INSERT INTO
... cities (city, latitude, longitude)
... VALUES ("Barcelona", 41.390205, 2.154007)''')#(2)!
<sqlite3.Cursor at 0x107139bc0>

>>> result = cur.execute('SELECT * FROM cities')
>>> result.fetchall()
[(1, 'Tokyo', 35.652832, 139.839478),
 (2, 'Barcelona', 41.390205, 2.154007)]

1. Obviamos el campo id.
2. Obviamos el campo id.

Copia de seguridad¶

Es posible realizar copias de seguridad de manera programática. Veamos un ejemplo⁴ donde copiamos dos bases de datos (ficheros):

>>> def progress(status, remaining, total):
...     print(f'Copied {total-remaining} of {total} pages...')
...

>>> src = sqlite3.connect('python.db')
>>> dst = sqlite3.connect('backup.db')

>>> with dst:#(1)!
...     src.backup(dst, pages=1, progress=progress)#(2)!
...
Copied 1 of 3 pages...
Copied 2 of 3 pages...
Copied 3 of 3 pages...

>>> dst.close()
>>> src.close()

1. Se utiliza un gestor de contexto.
2. • El parámetro pages indica el número de páginas a copiar «de cada vez». Si este valor es menor o igual que 0, la base de datos se copia en un único paso. El valor por defecto es -1
   • El parámetro progress permite definir una función para mostrar algún tipo de indicación del progreso de la copia.

Tras la copia, podemos comprobar que ambas bases de datos tienen el mismo contenido:

>>> src = sqlite3.connect('python.db')
>>> dst = sqlite3.connect('backup.db')

>>> with src, dst:#(1)!
...     src_cur = src.cursor()
...     dst_cur = dst.cursor()
...     sql = 'SELECT * FROM pyversions'
...     src_data = src_cur.execute(sql).fetchall()
...     dst_data = dst_cur.execute(sql).fetchall()
...     if src_data == dst_data:
...         print('Contents from both DBs are the same!')
...
Contents from both DBs are the same!

1. Es posible aplicar gestores de contexto sobre dos conexiones a la vez.

Este mecanismo de copia de seguridad funciona incluso...

• Si la base de datos está siendo accedida por otros clientes o concurrentemente por la misma conexión.
• Entre bases de datos :memory: y bases de datos en disco.

Hacer directamente una copia del fichero file.db (desde el propio sistema operativo) también es una opción rápida para disponer de copias de seguridad.

Información de filas¶

Cuando ejecutamos una sentencia de modificación sobre la base de datos podemos obtener el número de filas afectadas. Este dato lo sacamos del atributo rowcount del cursor correspondiente.

Podemos comprobarlo en el siguiente ejemplo de versiones de Python:

>>> con = sqlite3.connect('python.db')
>>> cur = con.cursor()

>>> cur.execute('SELECT * FROM pyversions').fetchall()
[('2.6', 2008, 10, 'Barry Warsaw'),
 ('2.7', 2010, 7, 'Benjamin Peterson'),
 ('3.0', 2008, 12, 'Barry Warsaw'),
 ('3.1', 2009, 6, 'Benjamin Peterson'),
 ('3.2', 2011, 2, 'Georg Brandl'),
 ('3.3', 2012, 9, 'Georg Brandl'),
 ('3.4', 2014, 3, 'Larry Hastings'),
 ('3.5', 2015, 9, 'Larry Hastings'),
 ('3.6', 2016, 12, 'Ned Deily'),
 ('3.7', 2018, 6, 'Ned Deily'),
 ('3.8', 2019, 10, 'Łukasz Langa'),
 ('3.9', 2020, 10, 'Łukasz Langa'),
 ('3.10', 2021, 10, 'Pablo Galindo Salgado'),
 ('3.11', 2022, 10, 'Pablo Galindo Salgado'),
 ('3.12', 2023, 10, 'Thomas Wouters'),
 ('3.13', 2024, 10, 'Thomas Wouters')]

>>> cur.execute('UPDATE pyversions SET released_at_year=2000')
<sqlite3.Cursor at 0x105593dc0>

>>> cur.rowcount#(1)!
16

1. 16 filas afectadas por la sentencia UPDATE de actualización de registros.

Igualmente al insertar un registro en la base de datos podemos obtener cuál es el identificador de la últila fila insertada:

>>> cur.execute('INSERT INTO pyversions VALUES ("3.20", 2031, 10, "Guido van Rossum")')
<sqlite3.Cursor at 0x105593dc0>

>>> cur.lastrowid#(1)!
17

1. • El identificador de la última fila insertada es 17.
   • También funciona si utilizamos una clave primaria INTEGER personalizada e insertamos un valor «manualmente» en dicha columna.

Ejecución de scripts¶

¿Qué pasaría si intentamos ejecutar varias sentencias SQL a la vez con las herramientas que hemos visto hasta ahora?

A vueltas con el ejemplo de las versiones de Python tendríamos lo siguiente:

>>> con = sqlite3.connect(':memory:')
>>> cur = con.cursor()

>>> sql = """
... CREATE TABLE pyversions (
...     branch TEXT PRIMARY KEY,
...     released_at_year INTEGER,
...     released_at_month INTEGER,
...     release_manager TEXT
... );
...
... INSERT INTO pyversions VALUES("3.10", 2021, 10, "Pablo Galindo Salgado");
... INSERT INTO pyversions VALUES("3.11", 2022, 10, "Pablo Galindo Salgado");
... INSERT INTO pyversions VALUES("3.12", 2023, 10, "Thomas Wouters");
... INSERT INTO pyversions VALUES("3.13", 2024, 10, "Thomas Wouters");
... """

>>> cur.execute(sql)#(1)!
Traceback (most recent call last):
  Cell In[4], line 1
    cur.execute(sql)
ProgrammingError: You can only execute one statement at a time.

1. Lo que ocurre es que obtenemos un error indicando que sólo se puede ejecutar una sentencia cada vez.

Para resolver este problema disponemos de la función executescript() que permite ejecutar varias sentencias SQL de una sola vez:

Volvemos a lanzar la misma sentencia SQL pero ahora utilizando este nuevo mecanismo:

>>> cur.executescript(sql)#(1)!
<sqlite3.Cursor object at 0x1034a95c0>

>>> cur.execute('SELECT * FROM pyversions').fetchall()#(2)!
[('3.10', 2021, 10, 'Pablo Galindo Salgado'),
 ('3.11', 2022, 10, 'Pablo Galindo Salgado'),
 ('3.12', 2023, 10, 'Thomas Wouters'),
 ('3.13', 2024, 10, 'Thomas Wouters')]

1. Ejecutamos el script «de una sola vez».
2. Comprobamos que los datos se han insertado correctamente.

Ejercicios¶

1. pypas   todo
2. pypas   twitter