Uso de SQLite 3 con Flask

En Flask puedes implementar fácilmente la apertura de conexiones a la base de datos bajo demanda y el cierre de las mismas cuando el contexto muere (normalmente al final de la petición).

Aquí hay un ejemplo sencillo de cómo se puede utilizar SQLite 3 con Flask:

import sqlite3
from flask import g

DATABASE = '/path/to/database.db'

def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE)
    return db

@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()

Ahora, para usar la base de datos, la aplicación debe tener un contexto de aplicación activo (que siempre es cierto si hay una petición en vuelo) o crear un contexto de aplicación por sí misma. En ese momento se puede utilizar la función get_db para obtener la conexión a la base de datos actual. Siempre que el contexto se destruya, la conexión a la base de datos se terminará.

Ejemplo:

@app.route('/')
def index():
    cur = get_db().cursor()
    ...

Nota

Ten en cuenta que las funciones teardown request y appcontext se ejecutan siempre, incluso si un manejador before-request falló o nunca se ejecutó. Debido a esto tenemos que asegurarnos aquí de que la base de datos está ahí antes de cerrarla.

Conectar a la carta

La ventaja de este enfoque (conectar en el primer uso) es que esto sólo abrirá la conexión si es realmente necesario. Si quieres usar este código fuera de un contexto de petición puedes usarlo en un shell de Python abriendo el contexto de aplicación a mano:

with app.app_context():
    # now you can use get_db()

Consulta fácil

Ahora en cada función de manejo de peticiones puedes acceder a get_db() para obtener la conexión actual de la base de datos abierta. Para simplificar el trabajo con SQLite, es útil una función de fábrica de filas. Se ejecuta para cada resultado devuelto por la base de datos para convertir el resultado. Por ejemplo, para obtener diccionarios en lugar de tuplas, esto podría ser insertado en la función get_db que creamos anteriormente:

def make_dicts(cursor, row):
    return dict((cursor.description[idx][0], value)
                for idx, value in enumerate(row))

db.row_factory = make_dicts

Esto hará que el módulo sqlite3 devuelva dicts para esta conexión de base de datos, que son mucho más agradables de manejar. Incluso más simple, podríamos colocar esto en get_db en su lugar:

db.row_factory = sqlite3.Row

Esto utilizaría objetos Row en lugar de dicts para devolver los resultados de las consultas. Son tuplas con nombre, por lo que podemos acceder a ellas por índice o por clave. Por ejemplo, suponiendo que tenemos un sqlite3.Row llamado r para las filas id, FirstName, LastName, y MiddleInitial:

>>> # You can get values based on the row's name
>>> r['FirstName']
John
>>> # Or, you can get them based on index
>>> r[1]
John
# Row objects are also iterable:
>>> for value in r:
...     print(value)
1
John
Doe
M

Además, es una buena idea proporcionar una función de consulta que combine la obtención del cursor, la ejecución y la obtención de los resultados:

def query_db(query, args=(), one=False):
    cur = get_db().execute(query, args)
    rv = cur.fetchall()
    cur.close()
    return (rv[0] if rv else None) if one else rv

Esta pequeña y práctica función, en combinación con una fábrica de filas, hace que el trabajo con la base de datos sea mucho más agradable de lo que es utilizando sólo los objetos de cursor y conexión en bruto.

Así es como se puede utilizar:

for user in query_db('select * from users'):
    print(user['username'], 'has the id', user['user_id'])

O si sólo quieres un único resultado:

user = query_db('select * from users where username = ?',
                [the_username], one=True)
if user is None:
    print('No such user')
else:
    print(the_username, 'has the id', user['user_id'])

Para pasar partes de variables a la sentencia SQL, utilice un signo de interrogación en la sentencia y pase los argumentos como una lista. Nunca los añada directamente a la sentencia SQL con formato de cadena porque esto hace posible atacar la aplicación usando Inyecciones SQL.

Esquemas iniciales

Las bases de datos relacionales necesitan esquemas, por lo que las aplicaciones suelen enviar un archivo schema.sql que crea la base de datos. Es una buena idea proporcionar una función que cree la base de datos basada en ese esquema. Esta función puede hacerlo por ti:

def init_db():
    with app.app_context():
        db = get_db()
        with app.open_resource('schema.sql', mode='r') as f:
            db.cursor().executescript(f.read())
        db.commit()

A continuación, puede crear dicha base de datos desde el shell de Python:

>>> from yourapplication import init_db
>>> init_db()