sqlite3 — Base de datos relacional integrada

Propósito:Implementa una base de datos relacional incorporada con soporte de SQL.

El módulo sqlite3 implementa una interfaz compatible a Python DB-API 2.0 a SQLite, una base de datos relacional en proceso. SQLite está diseñada para integrarse en aplicaciones, en lugar de utilizar un programa de servidor de base de datos como MySQL, PostgreSQL u Oracle. Es rápida, rigurosamente probada y flexible, lo que la hace adecuada para crear prototipos y despliegue de producción para algunas aplicaciones.

Crear una base de datos

Una base de datos SQLite se almacena como un solo archivo en el sistema de archivos. La biblioteca gestiona el acceso al archivo, incluido el bloqueo para evitar corrupción cuando varios escritores lo utilizan. La base de datos se crea la primera vez que se accede al archivo, pero la aplicación es responsable de gestionar las definiciones de tabla, o esquema, dentro de la base de datos.

Este ejemplo busca el archivo de base de datos antes de abrirlo con connect() para saber cuándo crear el esquema para una nueva bases de datos.

sqlite3_createdb.py
import os
import sqlite3

db_filename = 'todo.db'

db_is_new = not os.path.exists(db_filename)

conn = sqlite3.connect(db_filename)

if db_is_new:
    print('Need to create schema')
else:
    print('Database exists, assume schema does, too.')

conn.close()

Ejecutar el script dos veces muestra que crea el archivo vacío si no existe.

$ ls *.db

ls: *.db: No such file or directory

$ python3 sqlite3_createdb.py

Need to create schema

$ ls *.db

todo.db

$ python3 sqlite3_createdb.py

Database exists, assume schema does, too.

Después de crear el nuevo archivo de base de datos, el siguiente paso es crear el esquema para definir las tablas dentro de la base de datos. Los ejemplos restantes en esta sección usan todos el mismo esquema de base de datos con tablas para gestionar tareas. Los detalles del esquema de la base de datos se presentan en table:La tabla project y the table below.

La tabla project
Columna Tipo Descripción
name texto Nombre del proyecto
description texto Descripción larga del proyecto
deadline fecha Fecha de vencimiento de todo el proyecto
La tabla task
Columna Tipo Descripción
id número Identificador único de tarea
priority entero Prioridad numérica, menor es más importante
details texto Detalles completos de la tarea
status texto Estado de la tarea (uno de “new”, “pending”, “done” o “canceled”)
deadline fecha Fecha de vencimiento para esta tarea
completed_on feche Cuando se completó la tarea
project texto El nombre del proyecto para esta tarea

Las declaraciones del lenguaje de definición de datos (DDL) para crear las tablas son:

todo_schema.sql
-- Schema for to-do application examples.

-- Projects are high-level activities made up of tasks
create table project (
    name        text primary key,
    description text,
    deadline    date
);

-- Tasks are steps that can be taken to complete a project
create table task (
    id           integer primary key autoincrement not null,
    priority     integer default 1,
    details      text,
    status       text,
    deadline     date,
    completed_on date,
    project      text not null references project(name)
);

El método executescript() de la Connection puede ser utilizado para ejecutar las instrucciones DDL para crear el esquema.

sqlite3_create_schema.py
import os
import sqlite3

db_filename = 'todo.db'
schema_filename = 'todo_schema.sql'

db_is_new = not os.path.exists(db_filename)

with sqlite3.connect(db_filename) as conn:
    if db_is_new:
        print('Creating schema')
        with open(schema_filename, 'rt') as f:
            schema = f.read()
        conn.executescript(schema)

        print('Inserting initial data')

        conn.executescript("""
        insert into project (name, description, deadline)
        values ('pymotw', 'Python Module of the Week',
                '2016-11-01');

        insert into task (details, status, deadline, project)
        values ('write about select', 'done', '2016-04-25',
                'pymotw');

        insert into task (details, status, deadline, project)
        values ('write about random', 'waiting', '2016-08-22',
                'pymotw');

        insert into task (details, status, deadline, project)
        values ('write about sqlite3', 'active', '2017-07-31',
                'pymotw');
        """)
    else:
        print('Database exists, assume schema does, too.')

Después de crear las tablas, unas pocas instrucciones insert crean un proyecto de muestra y tareas relacionadas. El programa de línea de comandos sqlite3 puede ser utilizado para examinar el contenido de la base de datos.

$ rm -f todo.db
$ python3 sqlite3_create_schema.py

Creating schema
Inserting initial data

$ sqlite3 todo.db 'select * from task'

1|1|write about select|done|2016-04-25||pymotw
2|1|write about random|waiting|2016-08-22||pymotw
3|1|write about sqlite3|active|2017-07-31||pymotw

Recuperar datos

Para recuperar los valores guardados en la tabla task desde un un programa Python, crea un Cursor desde una conexión de base de datos. Un cursor produce una vista consistente de los datos, y es el principal medio de interactuar con un sistema de base de datos transaccional como SQLite.

sqlite3_select_tasks.py
import sqlite3

db_filename = 'todo.db'

with sqlite3.connect(db_filename) as conn:
    cursor = conn.cursor()

    cursor.execute("""
    select id, priority, details, status, deadline from task
    where project = 'pymotw'
    """)

    for row in cursor.fetchall():
        task_id, priority, details, status, deadline = row
        print('{:2d} [{:d}] {:<25} [{:<8}] ({})'.format(
            task_id, priority, details, status, deadline))

La consulta es un proceso de dos pasos. Primero, ejecuta la consulta con el el método execute() del cursor para decirle al motor de la base de datos qué datos coleccionar. Luego, usa fetchall() para recuperar los resultados. El valor de retorno es una secuencia de tuplas que contiene los valores para las columnas incluidas en la cláusula select de la consulta.

$ python3 sqlite3_select_tasks.py

 1 [1] write about select        [done    ] (2016-04-25)
 2 [1] write about random        [waiting ] (2016-08-22)
 3 [1] write about sqlite3       [active  ] (2017-07-31)

Los resultados se pueden recuperar uno a la vez con fetchone(), o en lotes de tamaño fijo con fetchmany().

sqlite3_select_variations.py
import sqlite3

db_filename = 'todo.db'

with sqlite3.connect(db_filename) as conn:
    cursor = conn.cursor()

    cursor.execute("""
    select name, description, deadline from project
    where name = 'pymotw'
    """)
    name, description, deadline = cursor.fetchone()

    print('Project details for {} ({})\n  due {}'.format(
        description, name, deadline))

    cursor.execute("""
    select id, priority, details, status, deadline from task
    where project = 'pymotw' order by deadline
    """)

    print('\nNext 5 tasks:')
    for row in cursor.fetchmany(5):
        task_id, priority, details, status, deadline = row
        print('{:2d} [{:d}] {:<25} [{:<8}] ({})'.format(
            task_id, priority, details, status, deadline))

El valor pasado a fetchmany() es el número máximo de elementos a regresar. Si hay menos artículos disponibles, la secuencia devuelta será más pequeña que el valor máximo.

$ python3 sqlite3_select_variations.py

Project details for Python Module of the Week (pymotw)
  due 2016-11-01

Next 5 tasks:
 1 [1] write about select        [done    ] (2016-04-25)
 2 [1] write about random        [waiting ] (2016-08-22)
 3 [1] write about sqlite3       [active  ] (2017-07-31)

Consulata de metadatos

La especificación DB-API 2.0 dice que después de que execute() ha sido llamado, el Cursor debe establecer que su atributo description contenga información sobre los datos que serán devueltos por los métodos fetch. La especificación de la interfaz de programación dice que el valor descripción es una secuencia de tuplas que contienen el nombre de columna, tipo, tamaño visible, tamaño interno, precisión, escala y una bandera que dice si se aceptan valores nulos.

sqlite3_cursor_description.py
import sqlite3

db_filename = 'todo.db'

with sqlite3.connect(db_filename) as conn:
    cursor = conn.cursor()

    cursor.execute("""
    select * from task where project = 'pymotw'
    """)

    print('Task table has these columns:')
    for colinfo in cursor.description:
        print(colinfo)

Porque sqlite3 no impone restricciones de tipo o tamaño en datos insertados en una base de datos, sólo se llena el valor del nombre de columna.

$ python3 sqlite3_cursor_description.py

Task table has these columns:
('id', None, None, None, None, None, None)
('priority', None, None, None, None, None, None)
('details', None, None, None, None, None, None)
('status', None, None, None, None, None, None)
('deadline', None, None, None, None, None, None)
('completed_on', None, None, None, None, None, None)
('project', None, None, None, None, None, None)

Objetos de fila

De forma predeterminada, los valores devueltos por los métodos fetch como «filas» de La base de datos son tuplas. La persona que llama es responsable de conocer el orden de las columnas en la consulta y de la extracción de valores individuales de la tupla. Cuando el número de valores en una consulta crece, o el código que trabaja con los datos se extiende en una biblioteca, por lo general es más fácil trabajar con un objeto y acceder a los valores utilizando sus nombres de columna. De esa manera, el número y orden de los contenidos de la tupla pueden cambiar con el tiempo a medida que se edita la consulta, y el código que depende de los resultados de la consulta es menos probable que se rompa.

Los objetos Connection tienen una propiedad row_factory que permite que el código de llamada controle el tipo de objeto creado para representar cada fila en el conjunto de resultados de la consulta. sqlite3 también incluye una clase Row destinada a ser utilizada como una fábrica de filas. Se puede acceder a los valores de columna a través de instancias Row usando el índice o el nombre de la columna.

sqlite3_row_factory.py
import sqlite3

db_filename = 'todo.db'

with sqlite3.connect(db_filename) as conn:
    # Change the row factory to use Row
    conn.row_factory = sqlite3.Row

    cursor = conn.cursor()

    cursor.execute("""
    select name, description, deadline from project
    where name = 'pymotw'
    """)
    name, description, deadline = cursor.fetchone()

    print('Project details for {} ({})\n  due {}'.format(
        description, name, deadline))

    cursor.execute("""
    select id, priority, status, deadline, details from task
    where project = 'pymotw' order by deadline
    """)

    print('\nNext 5 tasks:')
    for row in cursor.fetchmany(5):
        print('{:2d} [{:d}] {:<25} [{:<8}] ({})'.format(
            row['id'], row['priority'], row['details'],
            row['status'], row['deadline'],
        ))

Esta versión del ejemplo sqlite3_select_variations.py ha sido reescrita usando instancias Row en lugar de tuplas. La fila desde la tabla del proyecto todavía se imprime accediendo a los valores de columna a través de la posición, pero la declaración print para tareas usa búsqueda de claves en su lugar, por lo que no importa que el orden de las columnas en la consulta han cambiado.

$ python3 sqlite3_row_factory.py

Project details for Python Module of the Week (pymotw)
  due 2016-11-01

Next 5 tasks:
 1 [1] write about select        [done    ] (2016-04-25)
 2 [1] write about random        [waiting ] (2016-08-22)
 3 [1] write about sqlite3       [active  ] (2017-07-31)

Usar variables con consultas

El uso de consultas definidas como cadenas literales incrustadas en un programa es inflexible. Por ejemplo, cuando se agrega otro proyecto a la base de datos, la consulta para mostrar las cinco tareas principales deben actualizarse para trabajar con cualquier proyecto. Una forma de añadir más flexibilidad es construir una declaración SQL con la consulta deseada combinando valores en Python. Sin embargo, construir una cadena de consulta de esta manera es peligroso, y debería ser evitado. El no poder escapar correctamente los caracteres especiales en el las partes variables de la consulta pueden dar como resultado errores de análisis de SQL, o peor aún, una clase de vulnerabilidades de seguridad conocida como ataques de inyección de SQL, que permiten a intrusos ejecutar instrucciones SQL arbitrarias en la base de datos.

La forma correcta de usar valores dinámicos con consultas es a través de variables host pasadas a execute() junto con la instrucción SQL. Un valor de marcador de posición en el SQL se reemplaza con el valor de la variable host cuando se ejecuta la instrucción. Usando variables de host en lugar de insertar valores arbitrarios en el SQL antes de que se analice evita ataques de inyección porque no hay posibilidad de que los valores no confiables afectará cómo se analiza el SQL. SQLite soporta dos formas de consultas con marcadores de posición, posicionales y con nombre.

Parámetros posicionales

Un signo de interrogación (?) denota un argumento posicional, pasado a execute() como miembro de una tupla.

sqlite3_argument_positional.py
import sqlite3
import sys

db_filename = 'todo.db'
project_name = sys.argv[1]

with sqlite3.connect(db_filename) as conn:
    cursor = conn.cursor()

    query = """
    select id, priority, details, status, deadline from task
    where project = ?
    """

    cursor.execute(query, (project_name,))

    for row in cursor.fetchall():
        task_id, priority, details, status, deadline = row
        print('{:2d} [{:d}] {:<25} [{:<8}] ({})'.format(
            task_id, priority, details, status, deadline))

El argumento de la línea de comando se pasa de forma segura a la consulta como un argumento posicional, y no hay posibilidad de que datos incorrectos corrompan la base de datos.

$ python3 sqlite3_argument_positional.py pymotw

 1 [1] write about select        [done    ] (2016-04-25)
 2 [1] write about random        [waiting ] (2016-08-22)
 3 [1] write about sqlite3       [active  ] (2017-07-31)

Parámetros con nombre

Utiliza parámetros con nombre para consultas más complejas con muchos parámetros, o donde algunos parámetros se repiten varias veces dentro de la consulta. Los parámetros con nombre tienen un prefijo con dos puntos (por ejemplo, :param_name).

sqlite3_argument_named.py
import sqlite3
import sys

db_filename = 'todo.db'
project_name = sys.argv[1]

with sqlite3.connect(db_filename) as conn:
    cursor = conn.cursor()

    query = """
    select id, priority, details, status, deadline from task
    where project = :project_name
    order by deadline, priority
    """

    cursor.execute(query, {'project_name': project_name})

    for row in cursor.fetchall():
        task_id, priority, details, status, deadline = row
        print('{:2d} [{:d}] {:<25} [{:<8}] ({})'.format(
            task_id, priority, details, status, deadline))

Ni los parámetros posicionales ni los con nombre deben ser citados o escapados, ya que se les da un tratamiento especial por el analizador de consultas.

$ python3 sqlite3_argument_named.py pymotw

 1 [1] write about select        [done    ] (2016-04-25)
 2 [1] write about random        [waiting ] (2016-08-22)
 3 [1] write about sqlite3       [active  ] (2017-07-31)

Los parámetros de consulta se pueden usar con instrucciones select, insert, y update. Pueden aparecer en cualquier parte de la consulta donde un valor literal es legal.

sqlite3_argument_update.py
import sqlite3
import sys

db_filename = 'todo.db'
id = int(sys.argv[1])
status = sys.argv[2]

with sqlite3.connect(db_filename) as conn:
    cursor = conn.cursor()
    query = "update task set status = :status where id = :id"
    cursor.execute(query, {'status': status, 'id': id})

Esta instrucción update usa dos parámetros con nombre. El valor id se usa para encontrar la fila correcta para modificar, y el valor status se escribe en la tabla.

$ python3 sqlite3_argument_update.py 2 done
$ python3 sqlite3_argument_named.py pymotw

 1 [1] write about select        [done    ] (2016-04-25)
 2 [1] write about random        [done    ] (2016-08-22)
 3 [1] write about sqlite3       [active  ] (2017-07-31)

Carga a granel

Para aplicar la misma instrucción SQL a un gran conjunto de datos, usa executemany(). Esto es útil para cargar datos, ya que evita recorrer las entradas en Python y permite que la biblioteca subyacente aplique optimizaciones de bucle. Este programa de ejemplo lee una lista de tareas desde un archivo de valores separados por comas usando el módulo csv y los carga en la base de datos.

sqlite3_load_csv.py
import csv
import sqlite3
import sys

db_filename = 'todo.db'
data_filename = sys.argv[1]

SQL = """
insert into task (details, priority, status, deadline, project)
values (:details, :priority, 'active', :deadline, :project)
"""

with open(data_filename, 'rt') as csv_file:
    csv_reader = csv.DictReader(csv_file)

    with sqlite3.connect(db_filename) as conn:
        cursor = conn.cursor()
        cursor.executemany(SQL, csv_reader)

El archivo de datos de muestra tasks.csv contiene:

deadline,project,priority,details
2016-11-30,pymotw,2,"finish reviewing markup"
2016-08-20,pymotw,2,"revise chapter intros"
2016-11-01,pymotw,1,"subtitle"

Ejecutar el programa produce:

$ python3 sqlite3_load_csv.py tasks.csv
$ python3 sqlite3_argument_named.py pymotw

 1 [1] write about select        [done    ] (2016-04-25)
 5 [2] revise chapter intros     [active  ] (2016-08-20)
 2 [1] write about random        [done    ] (2016-08-22)
 6 [1] subtitle                  [active  ] (2016-11-01)
 4 [2] finish reviewing markup   [active  ] (2016-11-30)
 3 [1] write about sqlite3       [active  ] (2017-07-31)

Definir nuevos tipos de columnas

SQLite tiene soporte nativo para columnas de enteros, números de coma flotante y texto. Datos de estos tipos se convierten automáticamente por sqlite3 de la representación de Python a un valor que puede ser almacenado en la base de datos, y viceversa, según sea necesario. Los valores enteros son cargado desde la base de datos en las variables int o long, dependiendo del tamaño del valor. El texto se guarda y se recupera como str, a menos que text_factory para la Connection ha sido cambiado.

Aunque SQLite solo admite algunos tipos de datos internamente, sqlite3 incluye facilidades para definir tipos personalizados para permitir que una aplicación Python almacene cualquier tipo de datos en una columna. La conversión para tipos distintos a los admitidos por defecto está habilitada en la conexión de la base de datos usando la bandera detect_types. Utiliza PARSE_DECLTYPES si la columna fue declarada usando el tipo deseado cuándo se definió la tabla.

sqlite3_date_types.py
import sqlite3
import sys

db_filename = 'todo.db'

sql = "select id, details, deadline from task"


def show_deadline(conn):
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute(sql)
    row = cursor.fetchone()
    for col in ['id', 'details', 'deadline']:
        print('  {:<8}  {!r:<26} {}'.format(
            col, row[col], type(row[col])))
    return


print('Without type detection:')
with sqlite3.connect(db_filename) as conn:
    show_deadline(conn)

print('\nWith type detection:')
with sqlite3.connect(db_filename,
                     detect_types=sqlite3.PARSE_DECLTYPES,
                     ) as conn:
    show_deadline(conn)

sqlite3 proporciona convertidores para las columnas de fecha y hora, usando las clases date y datetime del módulo datetime para representar los valores en Python. Ambos convertidores relacionados con la fecha se activan automáticamente cuando la detección del tipo está activa.

$ python3 sqlite3_date_types.py

Without type detection:
  id        1                          <class 'int'>
  details   'write about select'       <class 'str'>
  deadline  '2016-04-25'               <class 'str'>

With type detection:
  id        1                          <class 'int'>
  details   'write about select'       <class 'str'>
  deadline  datetime.date(2016, 4, 25) <class 'datetime.date'>

Se deben registrar dos funciones para definir un nuevo tipo. El adaptador toma el objeto Python como entrada y devuelve una cadena de bytes que se puede almacenar en la base de datos. El convertidor recibe el cadena de la base de datos y devuelve un objeto Python. Utiliza register_adapter() para definir la función adaptador, y register_converter() para la función convertidor.

sqlite3_custom_type.py
import pickle
import sqlite3

db_filename = 'todo.db'


def adapter_func(obj):
    """Convert from in-memory to storage representation.
    """
    print('adapter_func({})\n'.format(obj))
    return pickle.dumps(obj)


def converter_func(data):
    """Convert from storage to in-memory representation.
    """
    print('converter_func({!r})\n'.format(data))
    return pickle.loads(data)


class MyObj:

    def __init__(self, arg):
        self.arg = arg

    def __str__(self):
        return 'MyObj({!r})'.format(self.arg)


# Register the functions for manipulating the type.
sqlite3.register_adapter(MyObj, adapter_func)
sqlite3.register_converter("MyObj", converter_func)

# Create some objects to save.  Use a list of tuples so
# the sequence can be passed directly to executemany().
to_save = [
    (MyObj('this is a value to save'),),
    (MyObj(42),),
]

with sqlite3.connect(
        db_filename,
        detect_types=sqlite3.PARSE_DECLTYPES) as conn:
    # Create a table with column of type "MyObj"
    conn.execute("""
    create table if not exists obj (
        id    integer primary key autoincrement not null,
        data  MyObj
    )
    """)
    cursor = conn.cursor()

    # Insert the objects into the database
    cursor.executemany("insert into obj (data) values (?)",
                       to_save)

    # Query the database for the objects just saved
    cursor.execute("select id, data from obj")
    for obj_id, obj in cursor.fetchall():
        print('Retrieved', obj_id, obj)
        print('  with type', type(obj))
        print()

Este ejemplo usa pickle para guardar un objeto en una cadena que puede almacenarse en la base de datos, una técnica útil para almacenar objetos arbitrarios, pero una que no permite la consulta basada en atributos del objeto. Un mapeador de objeto relacional real, como SQLAlchemy, que almacena los valores de los atributos en sus propias columnas será más útil para grandes cantidades de datos.

$ python3 sqlite3_custom_type.py

adapter_func(MyObj('this is a value to save'))

adapter_func(MyObj(42))

converter_func(b'\x80\x03c__main__\nMyObj\nq\x00)\x81q\x01}q\x02X\x0
3\x00\x00\x00argq\x03X\x17\x00\x00\x00this is a value to saveq\x04sb
.')

converter_func(b'\x80\x03c__main__\nMyObj\nq\x00)\x81q\x01}q\x02X\x0
3\x00\x00\x00argq\x03K*sb.')

Retrieved 1 MyObj('this is a value to save')
  with type <class '__main__.MyObj'>

Retrieved 2 MyObj(42)
  with type <class '__main__.MyObj'>

Determinación de tipos para columnas

Hay dos fuentes de información de tipos sobre los datos para una consulta. La declaración original de la tabla se puede utilizar para identificar el tipo de una columna real, como se muestra anteriormente. Un especificador de tipo también puede ser incluido en la cláusula select de la propia consulta usando la forma as "nombre [tipo]".

sqlite3_custom_type_column.py
import pickle
import sqlite3

db_filename = 'todo.db'


def adapter_func(obj):
    """Convert from in-memory to storage representation.
    """
    print('adapter_func({})\n'.format(obj))
    return pickle.dumps(obj)


def converter_func(data):
    """Convert from storage to in-memory representation.
    """
    print('converter_func({!r})\n'.format(data))
    return pickle.loads(data)


class MyObj:

    def __init__(self, arg):
        self.arg = arg

    def __str__(self):
        return 'MyObj({!r})'.format(self.arg)


# Register the functions for manipulating the type.
sqlite3.register_adapter(MyObj, adapter_func)
sqlite3.register_converter("MyObj", converter_func)

# Create some objects to save.  Use a list of tuples so we
# can pass this sequence directly to executemany().
to_save = [
    (MyObj('this is a value to save'),),
    (MyObj(42),),
]

with sqlite3.connect(
        db_filename,
        detect_types=sqlite3.PARSE_COLNAMES) as conn:
    # Create a table with column of type "text"
    conn.execute("""
    create table if not exists obj2 (
        id    integer primary key autoincrement not null,
        data  text
    )
    """)
    cursor = conn.cursor()

    # Insert the objects into the database
    cursor.executemany("insert into obj2 (data) values (?)",
                       to_save)

    # Query the database for the objects just saved,
    # using a type specifier to convert the text
    # to objects.
    cursor.execute(
        'select id, data as "pickle [MyObj]" from obj2',
    )
    for obj_id, obj in cursor.fetchall():
        print('Retrieved', obj_id, obj)
        print('  with type', type(obj))
        print()

Utiliza la bandera PARSE_COLNAMES de detect_types cuando el tipo es parte de la consulta en lugar de la definición original de tabla.

$ python3 sqlite3_custom_type_column.py

adapter_func(MyObj('this is a value to save'))

adapter_func(MyObj(42))

converter_func(b'\x80\x03c__main__\nMyObj\nq\x00)\x81q\x01}q\x02X\x0
3\x00\x00\x00argq\x03X\x17\x00\x00\x00this is a value to saveq\x04sb
.')

converter_func(b'\x80\x03c__main__\nMyObj\nq\x00)\x81q\x01}q\x02X\x0
3\x00\x00\x00argq\x03K*sb.')

Retrieved 1 MyObj('this is a value to save')
  with type <class '__main__.MyObj'>

Retrieved 2 MyObj(42)
  with type <class '__main__.MyObj'>

Transacciones

Una de las características clave de las bases de datos relacionales es el uso de transacciones para mantener un estado interno consistente. Con transacciones habilitadas, se pueden realizar varios cambios a través de una conexión sin afectar a otros usuarios hasta que los resultados son confirmados y vaciados a la base de datos real.

Preservar Cambios

Cambios en la base de datos, ya sea a través de las instrucciones insert o update necesitan guardarse llamando explícitamente commit(). Este requisito le da a la aplicación la oportunidad de hacer varios cambios relacionados juntos, para que se almacenan atómicamente en lugar de incrementalmente, y evita una situación en que las actualizaciones parciales son vistas por diferentes clientes que se conectan a la base de datos simultáneamente.

El efecto de llamar a commit() se puede ver con un programa que utiliza varias conexiones a la base de datos. Se inserta una nueva fila con la primera conexión, y luego se hacen dos intentos para volver a leerla utilizando conexiones separadas.

sqlite3_transaction_commit.py
import sqlite3

db_filename = 'todo.db'


def show_projects(conn):
    cursor = conn.cursor()
    cursor.execute('select name, description from project')
    for name, desc in cursor.fetchall():
        print('  ', name)


with sqlite3.connect(db_filename) as conn1:
    print('Before changes:')
    show_projects(conn1)

    # Insert in one cursor
    cursor1 = conn1.cursor()
    cursor1.execute("""
    insert into project (name, description, deadline)
    values ('virtualenvwrapper', 'Virtualenv Extensions',
            '2011-01-01')
    """)

    print('\nAfter changes in conn1:')
    show_projects(conn1)

    # Select from another connection, without committing first
    print('\nBefore commit:')
    with sqlite3.connect(db_filename) as conn2:
        show_projects(conn2)

    # Commit then select from another connection
    conn1.commit()
    print('\nAfter commit:')
    with sqlite3.connect(db_filename) as conn3:
        show_projects(conn3)

Cuando se llama a show_projects() antes de que conn1 haya sido confirmada, los resultados dependen de qué conexión se utiliza. Ya que el cambio se realizó a través de conn1, ésta ve los datos alterados. Sin embargo, conn2 no lo hace. Tras confiar, la nueva conexión, conn3 ve la fila insertada.

$ python3 sqlite3_transaction_commit.py

Before changes:
   pymotw

After changes in conn1:
   pymotw
   virtualenvwrapper

Before commit:
   pymotw

After commit:
   pymotw
   virtualenvwrapper

Descartar Cambios

Los cambios no confirmados también se pueden descartar por completo usando rollback(). Los métodos commit() y rollback() se suelen llamar desde diferentes partes del mismo bloque try:except, con errores que provocan una reversión.

sqlite3_transaction_rollback.py
import sqlite3

db_filename = 'todo.db'


def show_projects(conn):
    cursor = conn.cursor()
    cursor.execute('select name, description from project')
    for name, desc in cursor.fetchall():
        print('  ', name)


with sqlite3.connect(db_filename) as conn:

    print('Before changes:')
    show_projects(conn)

    try:

        # Insert
        cursor = conn.cursor()
        cursor.execute("""delete from project
                       where name = 'virtualenvwrapper'
                       """)

        # Show the settings
        print('\nAfter delete:')
        show_projects(conn)

        # Pretend the processing caused an error
        raise RuntimeError('simulated error')

    except Exception as err:
        # Discard the changes
        print('ERROR:', err)
        conn.rollback()

    else:
        # Save the changes
        conn.commit()

    # Show the results
    print('\nAfter rollback:')
    show_projects(conn)

Después de llamar a rollback(), los cambios en la base de datos ya no están presentes.

$ python3 sqlite3_transaction_rollback.py

Before changes:
   pymotw
   virtualenvwrapper

After delete:
   pymotw
ERROR: simulated error

After rollback:
   pymotw
   virtualenvwrapper

Niveles de Aislamiento

sqlite3 soporta tres modos de bloqueo, llamados niveles de aislamiento, que controlan la técnica utilizada para prevenir cambios incompatibles entre conexiones. El nivel de aislamiento se establece al pasar una cadena como el argumento isolation_level cuando se abre una conexión, así diferentes conexiones pueden usar diferentes valores.

Este programa demuestra el efecto de diferentes niveles de aislamiento en el orden de los eventos en hilos que utilizan conexiones separadas a la misma base de datos. Se crean cuatro hilos. Dos hilos escriben cambios en la base de datos mediante la actualización de las filas existentes. Los otros dos hilos intentan leer todas las filas de la tabla task.

sqlite3_isolation_levels.py
import logging
import sqlite3
import sys
import threading
import time

logging.basicConfig(
    level=logging.DEBUG,
    format='%(asctime)s (%(threadName)-10s) %(message)s',
)

db_filename = 'todo.db'
isolation_level = sys.argv[1]


def writer():
    with sqlite3.connect(
            db_filename,
            isolation_level=isolation_level) as conn:
        cursor = conn.cursor()
        cursor.execute('update task set priority = priority + 1')
        logging.debug('waiting to synchronize')
        ready.wait()  # synchronize threads
        logging.debug('PAUSING')
        time.sleep(1)
        conn.commit()
        logging.debug('CHANGES COMMITTED')


def reader():
    with sqlite3.connect(
            db_filename,
            isolation_level=isolation_level) as conn:
        cursor = conn.cursor()
        logging.debug('waiting to synchronize')
        ready.wait()  # synchronize threads
        logging.debug('wait over')
        cursor.execute('select * from task')
        logging.debug('SELECT EXECUTED')
        cursor.fetchall()
        logging.debug('results fetched')


if __name__ == '__main__':
    ready = threading.Event()

    threads = [
        threading.Thread(name='Reader 1', target=reader),
        threading.Thread(name='Reader 2', target=reader),
        threading.Thread(name='Writer 1', target=writer),
        threading.Thread(name='Writer 2', target=writer),
    ]

    [t.start() for t in threads]

    time.sleep(1)
    logging.debug('setting ready')
    ready.set()

    [t.join() for t in threads]

Los hilos se sincronizan usando un objeto Event del módulo threading. La función writer() se conecta y realiza cambios en la base de datos, pero no se confirma antes de que se lance el evento La función reader() se conecta, luego espera para consultar la base de datos hasta después de que ocurra el evento de sincronización.

Diferido

El nivel de aislamiento predeterminado es DEFERRED. Usando el modo diferido bloquea la base de datos, pero solo una vez que se inicia un cambio. Todos los ejemplos anteriores utilizan el modo diferido.

$ python3 sqlite3_isolation_levels.py DEFERRED

2016-08-20 17:46:26,972 (Reader 1  ) waiting to synchronize
2016-08-20 17:46:26,972 (Reader 2  ) waiting to synchronize
2016-08-20 17:46:26,973 (Writer 1  ) waiting to synchronize
2016-08-20 17:46:27,977 (MainThread) setting ready
2016-08-20 17:46:27,979 (Reader 1  ) wait over
2016-08-20 17:46:27,979 (Writer 1  ) PAUSING
2016-08-20 17:46:27,979 (Reader 2  ) wait over
2016-08-20 17:46:27,981 (Reader 1  ) SELECT EXECUTED
2016-08-20 17:46:27,982 (Reader 1  ) results fetched
2016-08-20 17:46:27,982 (Reader 2  ) SELECT EXECUTED
2016-08-20 17:46:27,982 (Reader 2  ) results fetched
2016-08-20 17:46:28,985 (Writer 1  ) CHANGES COMMITTED
2016-08-20 17:46:29,043 (Writer 2  ) waiting to synchronize
2016-08-20 17:46:29,043 (Writer 2  ) PAUSING
2016-08-20 17:46:30,044 (Writer 2  ) CHANGES COMMITTED

Inmediato

El modo inmediato bloquea la base de datos tan pronto como comienza un cambio y evita que otros cursores realicen cambios hasta que la transacción sea confirmada. Es adecuado para una base de datos con escrituras complicadas, pero más lectores que escritores, ya que los lectores no están bloqueados mientras que la transacción está en curso.

$ python3 sqlite3_isolation_levels.py IMMEDIATE

2016-08-20 17:46:30,121 (Reader 1  ) waiting to synchronize
2016-08-20 17:46:30,121 (Reader 2  ) waiting to synchronize
2016-08-20 17:46:30,123 (Writer 1  ) waiting to synchronize
2016-08-20 17:46:31,122 (MainThread) setting ready
2016-08-20 17:46:31,122 (Reader 1  ) wait over
2016-08-20 17:46:31,122 (Reader 2  ) wait over
2016-08-20 17:46:31,122 (Writer 1  ) PAUSING
2016-08-20 17:46:31,124 (Reader 1  ) SELECT EXECUTED
2016-08-20 17:46:31,124 (Reader 2  ) SELECT EXECUTED
2016-08-20 17:46:31,125 (Reader 2  ) results fetched
2016-08-20 17:46:31,125 (Reader 1  ) results fetched
2016-08-20 17:46:32,128 (Writer 1  ) CHANGES COMMITTED
2016-08-20 17:46:32,199 (Writer 2  ) waiting to synchronize
2016-08-20 17:46:32,199 (Writer 2  ) PAUSING
2016-08-20 17:46:33,200 (Writer 2  ) CHANGES COMMITTED

Exclusivo

El modo exclusivo bloquea la base de datos a todos los lectores y escritores. Su uso debe ser limitado en situaciones donde el rendimiento de la base de datos es importante, ya que cada conexión exclusiva bloquea a todos los demás usuarios.

$ python3 sqlite3_isolation_levels.py EXCLUSIVE

2016-08-20 17:46:33,320 (Reader 1  ) waiting to synchronize
2016-08-20 17:46:33,320 (Reader 2  ) waiting to synchronize
2016-08-20 17:46:33,324 (Writer 2  ) waiting to synchronize
2016-08-20 17:46:34,323 (MainThread) setting ready
2016-08-20 17:46:34,323 (Reader 1  ) wait over
2016-08-20 17:46:34,323 (Writer 2  ) PAUSING
2016-08-20 17:46:34,323 (Reader 2  ) wait over
2016-08-20 17:46:35,327 (Writer 2  ) CHANGES COMMITTED
2016-08-20 17:46:35,368 (Reader 2  ) SELECT EXECUTED
2016-08-20 17:46:35,368 (Reader 2  ) results fetched
2016-08-20 17:46:35,369 (Reader 1  ) SELECT EXECUTED
2016-08-20 17:46:35,369 (Reader 1  ) results fetched
2016-08-20 17:46:35,385 (Writer 1  ) waiting to synchronize
2016-08-20 17:46:35,385 (Writer 1  ) PAUSING
2016-08-20 17:46:36,386 (Writer 1  ) CHANGES COMMITTED

Porque el primer escritor ha comenzado a hacer cambios, los lectores y el segundo escritor bloqueen hasta que se confirme. La llamada sleep() introduce un retraso artificial en el hilo del escritor para resaltar el hecho que las otras conexiones están bloqueando.

Auto-confirmar

El parámetro isolation_level para la conexión también se puede establecer en None para habilitar el modo de confirmación automática. Con la confirmación automática activada, cada llamada execute() se confirma inmediatamente cuando la instrucción termina. El modo de confirmación automática es adecuado para transacciones cortas, como aquellos que insertan una pequeña cantidad de datos en una sola tabla. La base de datos está bloqueada por el menor tiempo posible, por lo que hay menos posibilidad de contienda entre hilos.

En sqlite3_autocommit.py, la llamada explícita a commit() ha sido eliminada y el nivel de aislamiento está establecido en None, pero por lo demás es lo mismo que sqlite3_isolation_levels.py. La salida es diferente, sin embargo, ya que ambos hilos de escritura terminan su trabajo antes que cualquiera de que los lectores comiencen a consultar.

$ python3 sqlite3_autocommit.py

2016-08-20 17:46:36,451 (Reader 1  ) waiting to synchronize
2016-08-20 17:46:36,451 (Reader 2  ) waiting to synchronize
2016-08-20 17:46:36,455 (Writer 1  ) waiting to synchronize
2016-08-20 17:46:36,456 (Writer 2  ) waiting to synchronize
2016-08-20 17:46:37,452 (MainThread) setting ready
2016-08-20 17:46:37,452 (Reader 1  ) wait over
2016-08-20 17:46:37,452 (Writer 2  ) PAUSING
2016-08-20 17:46:37,452 (Reader 2  ) wait over
2016-08-20 17:46:37,453 (Writer 1  ) PAUSING
2016-08-20 17:46:37,453 (Reader 1  ) SELECT EXECUTED
2016-08-20 17:46:37,454 (Reader 2  ) SELECT EXECUTED
2016-08-20 17:46:37,454 (Reader 1  ) results fetched
2016-08-20 17:46:37,454 (Reader 2  ) results fetched

Bases de datos en memoria

SQLite admite la gestión de una base de datos completa en RAM, en lugar de confiar en un archivo de disco. Las bases de datos en memoria son útiles para pruebas automatizadas, donde la base de datos no necesita ser preservada entre ejecuciones de prueba, o al experimentar con un esquema u otras características de la base de datos. Para abrir una base de datos en memoria, use la cadena ':memory:' en lugar de un nombre de archivo al crear la Connection. Cada conexión ':memory:' crea una instancia de base de datos independiente, por lo que los cambios realizados por un cursor en uno no efectúa otras conexiones.

Exportar el contenido de una base de datos

El contenido de una base de datos en memoria se puede guardar usando el método iterdump() de Connection. El iterador devuelto por iterdump() produce una serie de cadenas que juntas construyen instrucciones SQL para recrear el estado de la base de datos.

sqlite3_iterdump.py
import sqlite3

schema_filename = 'todo_schema.sql'

with sqlite3.connect(':memory:') as conn:
    conn.row_factory = sqlite3.Row

    print('Creating schema')
    with open(schema_filename, 'rt') as f:
        schema = f.read()
    conn.executescript(schema)

    print('Inserting initial data')
    conn.execute("""
    insert into project (name, description, deadline)
    values ('pymotw', 'Python Module of the Week',
            '2010-11-01')
    """)
    data = [
        ('write about select', 'done', '2010-10-03',
         'pymotw'),
        ('write about random', 'waiting', '2010-10-10',
         'pymotw'),
        ('write about sqlite3', 'active', '2010-10-17',
         'pymotw'),
    ]
    conn.executemany("""
    insert into task (details, status, deadline, project)
    values (?, ?, ?, ?)
    """, data)

    print('Dumping:')
    for text in conn.iterdump():
        print(text)

iterdump() también se puede usar con bases de datos guardadas en archivos, pero es más útil para preservar una base de datos que de otra manera no serían salvado. Esta salida ha sido editada para caber en la página mientras es sintácticamente correcta.

$ python3 sqlite3_iterdump.py

Creating schema
Inserting initial data
Dumping:
BEGIN TRANSACTION;
CREATE TABLE project (
    name        text primary key,
    description text,
    deadline    date
);
INSERT INTO "project" VALUES('pymotw','Python Module of the
Week','2010-11-01');
DELETE FROM "sqlite_sequence";
INSERT INTO "sqlite_sequence" VALUES('task',3);
CREATE TABLE task (
    id           integer primary key autoincrement not null,
    priority     integer default 1,
    details      text,
    status       text,
    deadline     date,
    completed_on date,
    project      text not null references project(name)
);
INSERT INTO "task" VALUES(1,1,'write about
select','done','2010-10-03',NULL,'pymotw');
INSERT INTO "task" VALUES(2,1,'write about
random','waiting','2010-10-10',NULL,'pymotw');
INSERT INTO "task" VALUES(3,1,'write about
sqlite3','active','2010-10-17',NULL,'pymotw');
COMMIT;

Usar funciones de Python en SQL

La sintaxis SQL admite llamar funciones durante las consultas, ya sea en la lista de columnas o la cláusula where de la instrucción select. Esta característica hace posible procesar datos antes de devolverlos desde la consulta, y se puede utilizar para convertir entre diferentes formatos, realizar cálculos que serían torpes en SQL puro, y reutilizar código de aplicación.

sqlite3_create_function.py
import codecs
import sqlite3

db_filename = 'todo.db'


def encrypt(s):
    print('Encrypting {!r}'.format(s))
    return codecs.encode(s, 'rot-13')


def decrypt(s):
    print('Decrypting {!r}'.format(s))
    return codecs.encode(s, 'rot-13')


with sqlite3.connect(db_filename) as conn:

    conn.create_function('encrypt', 1, encrypt)
    conn.create_function('decrypt', 1, decrypt)
    cursor = conn.cursor()

    # Raw values
    print('Original values:')
    query = "select id, details from task"
    cursor.execute(query)
    for row in cursor.fetchall():
        print(row)

    print('\nEncrypting...')
    query = "update task set details = encrypt(details)"
    cursor.execute(query)

    print('\nRaw encrypted values:')
    query = "select id, details from task"
    cursor.execute(query)
    for row in cursor.fetchall():
        print(row)

    print('\nDecrypting in query...')
    query = "select id, decrypt(details) from task"
    cursor.execute(query)
    for row in cursor.fetchall():
        print(row)

    print('\nDecrypting...')
    query = "update task set details = decrypt(details)"
    cursor.execute(query)

Las funciones se exponen utilizando el método create_function() de la Conexión. Los parámetros son el nombre de la función (como debe utilizarse desde dentro de SQL), el número de argumentos que toma la función, y la función de Python que se expone.

$ python3 sqlite3_create_function.py

Original values:
(1, 'write about select')
(2, 'write about random')
(3, 'write about sqlite3')
(4, 'finish reviewing markup')
(5, 'revise chapter intros')
(6, 'subtitle')

Encrypting...
Encrypting 'write about select'
Encrypting 'write about random'
Encrypting 'write about sqlite3'
Encrypting 'finish reviewing markup'
Encrypting 'revise chapter intros'
Encrypting 'subtitle'

Raw encrypted values:
(1, 'jevgr nobhg fryrpg')
(2, 'jevgr nobhg enaqbz')
(3, 'jevgr nobhg fdyvgr3')
(4, 'svavfu erivrjvat znexhc')
(5, 'erivfr puncgre vagebf')
(6, 'fhogvgyr')

Decrypting in query...
Decrypting 'jevgr nobhg fryrpg'
Decrypting 'jevgr nobhg enaqbz'
Decrypting 'jevgr nobhg fdyvgr3'
Decrypting 'svavfu erivrjvat znexhc'
Decrypting 'erivfr puncgre vagebf'
Decrypting 'fhogvgyr'
(1, 'write about select')
(2, 'write about random')
(3, 'write about sqlite3')
(4, 'finish reviewing markup')
(5, 'revise chapter intros')
(6, 'subtitle')

Decrypting...
Decrypting 'jevgr nobhg fryrpg'
Decrypting 'jevgr nobhg enaqbz'
Decrypting 'jevgr nobhg fdyvgr3'
Decrypting 'svavfu erivrjvat znexhc'
Decrypting 'erivfr puncgre vagebf'
Decrypting 'fhogvgyr'

Consulta con expresiones regulares

SQLite soporta varias funciones especiales de usuario que están asociadas con sintaxis SQL. Por ejemplo, una función regexp puede ser usada en una consulta para comprobar si el valor de cadena de una columna coincide con una expresión regular utilizando la siguiente sintaxis.

SELECT * FROM table
WHERE column REGEXP '.*pattern.*'

Este ejemplo asocia una función con regexp() para probar valores usando el modulo re de Python.

sqlite3_regex.py
import re
import sqlite3

db_filename = 'todo.db'


def regexp(pattern, input):
    return bool(re.match(pattern, input))


with sqlite3.connect(db_filename) as conn:
    conn.row_factory = sqlite3.Row
    conn.create_function('regexp', 2, regexp)
    cursor = conn.cursor()

    pattern = '.*[wW]rite [aA]bout.*'

    cursor.execute(
        """
        select id, priority, details, status, deadline from task
        where details regexp :pattern
        order by deadline, priority
        """,
        {'pattern': pattern},
    )

    for row in cursor.fetchall():
        task_id, priority, details, status, deadline = row
        print('{:2d} [{:d}] {:<25} [{:<8}] ({})'.format(
            task_id, priority, details, status, deadline))

La salida es todas las tareas donde la columna de detalles coincide con el patrón.

$ python3 sqlite3_regex.py

 1 [9] write about select        [done    ] (2016-04-25)
 2 [9] write about random        [done    ] (2016-08-22)
 3 [9] write about sqlite3       [active  ] (2017-07-31)

Agregación personalizada

Una función de agregación recopila muchas piezas de datos individuales y las resume de alguna manera. Ejemplos de funciones de agregación incorporadas son avg() (promedio), min(), max(), y count().

La interfaz de programación para agregadores utilizada por sqlite3 se define en términos de una clase con dos métodos. El método step() se llama una vez para cada valor de datos a medida que se procesa la consulta. El método finalize() se llama una vez al final de la consulta y debe devolver el valor agregado. Este ejemplo implementa un agregador para el modo aritmético. Devuelve el valor que aparece con más frecuencia en la entrada.

sqlite3_create_aggregate.py
import sqlite3
import collections

db_filename = 'todo.db'


class Mode:

    def __init__(self):
        self.counter = collections.Counter()

    def step(self, value):
        print('step({!r})'.format(value))
        self.counter[value] += 1

    def finalize(self):
        result, count = self.counter.most_common(1)[0]
        print('finalize() -> {!r} ({} times)'.format(
            result, count))
        return result


with sqlite3.connect(db_filename) as conn:
    conn.create_aggregate('mode', 1, Mode)

    cursor = conn.cursor()
    cursor.execute("""
    select mode(deadline) from task where project = 'pymotw'
    """)
    row = cursor.fetchone()
    print('mode(deadline) is:', row[0])

La clase agregadora se registra con el método create_aggregate() de la Connection. Los parámetros son el nombre de la función (como se debe utilizar desde dentro de SQL), el número de argumentos que el método step() toma, y la clase a usar.

$ python3 sqlite3_create_aggregate.py

step('2016-04-25')
step('2016-08-22')
step('2017-07-31')
step('2016-11-30')
step('2016-08-20')
step('2016-11-01')
finalize() -> '2016-11-01' (1 times)
mode(deadline) is: 2016-11-01

Compartir hilos y conexiones

Por razones históricas que tienen que ver con versiones antiguas de SQLite, los objetos Connection no pueden ser compartidos entre hilos. Cada hilo debe crear su propia conexión a la base de datos.

sqlite3_threading.py
import sqlite3
import sys
import threading
import time

db_filename = 'todo.db'
isolation_level = None  # autocommit mode


def reader(conn):
    print('Starting thread')
    try:
        cursor = conn.cursor()
        cursor.execute('select * from task')
        cursor.fetchall()
        print('results fetched')
    except Exception as err:
        print('ERROR:', err)


if __name__ == '__main__':
    with sqlite3.connect(db_filename,
                         isolation_level=isolation_level,
                         ) as conn:
        t = threading.Thread(name='Reader 1',
                             target=reader,
                             args=(conn,),
                             )
        t.start()
        t.join()

Los intentos de compartir una conexión entre subprocesos dan como resultado una excepción.

$ python3 sqlite3_threading.py

Starting thread
ERROR: SQLite objects created in a thread can only be used in that
same thread.The object was created in thread id 140735234088960
and this is thread id 123145307557888

Restricción del acceso a los datos

Aunque SQLite no tiene controles de acceso de usuario encontrados en otras, bases de datos relacionales más grandes, tiene un mecanismo para limitar el acceso a columnas. Cada conexión puede instalar una función autorizadora para otorgar o denegar el acceso a columnas en tiempo de ejecución en función de un criterio deseado. La función autorizadora se invoca durante el el análisis de las instrucciones de SQL, y se le pasan cinco argumentos. El primero es un código de acción que indica el tipo de operación que se está realizando (leer, escribir, borrar, etc.). El resto de los argumentos dependen del código de acción. Para las operaciones SQLITE_READ, los argumentos son el nombre de la tabla, el nombre de la columna, la ubicación en el SQL donde se está produciendo el acceso (consulta principal, trigger, etc.), y None.

sqlite3_set_authorizer.py
import sqlite3

db_filename = 'todo.db'


def authorizer_func(action, table, column, sql_location, ignore):
    print('\nauthorizer_func({}, {}, {}, {}, {})'.format(
        action, table, column, sql_location, ignore))

    response = sqlite3.SQLITE_OK  # be permissive by default

    if action == sqlite3.SQLITE_SELECT:
        print('requesting permission to run a select statement')
        response = sqlite3.SQLITE_OK

    elif action == sqlite3.SQLITE_READ:
        print('requesting access to column {}.{} from {}'.format(
            table, column, sql_location))
        if column == 'details':
            print('  ignoring details column')
            response = sqlite3.SQLITE_IGNORE
        elif column == 'priority':
            print('  preventing access to priority column')
            response = sqlite3.SQLITE_DENY

    return response


with sqlite3.connect(db_filename) as conn:
    conn.row_factory = sqlite3.Row
    conn.set_authorizer(authorizer_func)

    print('Using SQLITE_IGNORE to mask a column value:')
    cursor = conn.cursor()
    cursor.execute("""
    select id, details from task where project = 'pymotw'
    """)
    for row in cursor.fetchall():
        print(row['id'], row['details'])

    print('\nUsing SQLITE_DENY to deny access to a column:')
    cursor.execute("""
    select id, priority from task where project = 'pymotw'
    """)
    for row in cursor.fetchall():
        print(row['id'], row['details'])

Este ejemplo usa SQLITE_IGNORE para hacer que las cadenas de la columna task.details sean reemplazadas por valores nulos en los resultados de la consulta. También evita todo acceso a la columna task.priority devolviendo SQLITE_DENY, que a su vez hace que SQLite elevar una excepción

$ python3 sqlite3_set_authorizer.py

Using SQLITE_IGNORE to mask a column value:

authorizer_func(21, None, None, None, None)
requesting permission to run a select statement

authorizer_func(20, task, id, main, None)
requesting access to column task.id from main

authorizer_func(20, task, details, main, None)
requesting access to column task.details from main
  ignoring details column

authorizer_func(20, task, project, main, None)
requesting access to column task.project from main
1 None
2 None
3 None
4 None
5 None
6 None

Using SQLITE_DENY to deny access to a column:

authorizer_func(21, None, None, None, None)
requesting permission to run a select statement

authorizer_func(20, task, id, main, None)
requesting access to column task.id from main

authorizer_func(20, task, priority, main, None)
requesting access to column task.priority from main
  preventing access to priority column
Traceback (most recent call last):
  File "sqlite3_set_authorizer.py", line 53, in <module>
    """)
sqlite3.DatabaseError: access to task.priority is prohibited

Los posibles códigos de acción están disponibles como constantes en sqlite3, con nombres con el prefijo SQLITE_. Cada tipo de instrucción SQL se puede marcar y el acceso a columnas individuales también puede ser controlado.

Ver también

  • Standard library documentation for sqlite3
  • PEP 249 – Especificación DB API 2.0 (Una interfaz estandarida para módulos que proporcionan acceso a bases de datos.)
  • SQLite – El sitio oficial de la biblioteca SQLite.
  • shelve – Almacén de valor-clave para guardar objetos Python arbitrarios.
  • SQLAlchemy – Un popular mapeador de objetos relacionales que soporta SQLite entre muchas otras bases de datos relacionales.