python - create_engine - sqlalchemy sqlite

Sqlite/SQLAlchemy: ¿cómo hacer cumplir las claves foráneas?

Question

La nueva versión de SQLite tiene la capacidad de aplicar restricciones de clave externa, pero en aras de la compatibilidad con versiones anteriores, ¡debe activarla para cada conexión de base de datos por separado!

sqlite> PRAGMA foreign_keys = ON;

Estoy usando SQLAlchemy, ¿cómo puedo asegurarme de que siempre esté activado? Lo que he intentado es esto:

engine = sqlalchemy.create_engine(''sqlite:///:memory:'', echo=True)
engine.execute(''pragma foreign_keys=on'')

... pero no está funcionando! ... ¿Qué me estoy perdiendo?

EDITAR: ¡Creo que mi verdadero problema es que tengo más de una versión de SQLite instalada y Python no está utilizando la última!

>>> import sqlite3
>>> print sqlite3.sqlite_version
3.3.4

¡Pero acabo de descargar 3.6.23 y poner el exe en mi directorio de proyectos! ¿Cómo puedo averiguar qué .exe está usando y cambiarlo?

Answer 1

Ahora tengo esto funcionando:

Descargue las últimas compilaciones sqlite y pysqlite2 como se describe arriba: asegúrese de que Python esté utilizando las versiones correctas en tiempo de ejecución.

import sqlite3   
import pysqlite2 
print sqlite3.sqlite_version   # should be 3.6.23.1
print pysqlite2.__path__       # eg C://Python26//lib//site-packages//pysqlite2

Luego agregue un PoolListener:

from sqlalchemy.interfaces import PoolListener
class ForeignKeysListener(PoolListener):
    def connect(self, dbapi_con, con_record):
        db_cursor = dbapi_con.execute(''pragma foreign_keys=ON'')

engine = create_engine(database_url, listeners=[ForeignKeysListener()])

Luego, tenga cuidado de cómo probar si las claves externas funcionan: aquí tuve algo de confusión. Al usar sqlalchemy ORM para agregar () elementos, mi código de importación manejaba implícitamente las conexiones de relación, por lo que nunca podría fallar. Agregar ''nullable = False'' a algunas declaraciones ForeignKey () me ayudó aquí.

La manera en que pruebo el soporte de clave externa sqlalchemy sqlite está habilitado para hacer una inserción manual desde una clase ORM declarativa:

# example
ins = Coverage.__table__.insert().values(id = 99,
                                    description = ''Wrong'',
                                    area = 42.0,
                                    wall_id = 99,  # invalid fkey id
                                    type_id = 99)  # invalid fkey_id
session.execute(ins) 

Aquí ''wall_id'' y ''type_id'' son ambos ForeignKey () y sqlite arroja una excepción correctamente ahora si se trata de conectar fkeys inválidos. ¡Así que funciona! Si elimina el oyente, sqlalchemy agregará entradas inválidas.

Creo que el problema principal puede ser múltiples sqlite3.dll (o .so) por ahí.

Answer 2

Basándose en las respuestas de conny y shadowmatter, aquí hay un código que comprobará si está utilizando SQLite3 antes de emitir la declaración PRAGMA:

from sqlalchemy import event
from sqlalchemy.engine import Engine
from sqlite3 import Connection as SQLite3Connection

@event.listens_for(Engine, "connect")
def _set_sqlite_pragma(dbapi_connection, connection_record):
    if isinstance(dbapi_connection, SQLite3Connection):
        cursor = dbapi_connection.cursor()
        cursor.execute("PRAGMA foreign_keys=ON;")
        cursor.close()

Answer 3

Como un enfoque más simple si la creación de su sesión está centralizada detrás de una función auxiliar de Python (en lugar de exponer el motor SQLA directamente), puede emitir "session.execute (''pragma foreign_keys = on'')" antes de devolver la sesión recién creada.

Solo necesita el enfoque de escucha de grupo si partes arbitrarias de su aplicación pueden crear sesiones SQLA en la base de datos.

Answer 4

Desde la página del dialecto SQLite :

SQLite admite la sintaxis FOREIGN KEY al emitir instrucciones CREATE para tablas, sin embargo, por defecto estas restricciones no tienen efecto en el funcionamiento de la tabla.

La comprobación de restricciones en SQLite tiene tres requisitos previos:

• Al menos la versión 3.6.19 de SQLite debe estar en uso
• La biblioteca SQLite debe compilarse sin los símbolos SQLITE_OMIT_FOREIGN_KEY o SQLITE_OMIT_TRIGGER habilitados.
• La instrucción PRAGMA foreign_keys = ON debe emitirse en todas las conexiones antes de su uso.

SQLAlchemy permite que la declaración PRAGMA se emita automáticamente para nuevas conexiones a través del uso de eventos:

from sqlalchemy.engine import Engine
from sqlalchemy import event

@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()

Answer 5

Para versiones recientes (SQLAlchemy ~ 0.7), la página de inicio de SQLAlchemy dice:

PoolListener está en desuso. Por favor refiérase a PoolEvents .

Entonces el ejemplo de CarlS se convierte en:

engine = create_engine(database_url)

def _fk_pragma_on_connect(dbapi_con, con_record):
    dbapi_con.execute(''pragma foreign_keys=ON'')

from sqlalchemy import event
event.listen(engine, ''connect'', _fk_pragma_on_connect)

Answer 6

Si necesita ejecutar algo para la configuración en cada conexión, use un PoolListener .

Answer 7

Tuve el mismo problema antes (las secuencias de comandos con restricciones de claves externas estaban pasando, pero las restricciones de real no se aplicaban por el motor sqlite); lo soluciono por:

1. descargando, construyendo e instalando la última versión de sqlite desde aquí: sqlite-sqlite-amalgamation ; antes de esto tenía sqlite 3.6.16 en mi máquina ubuntu; que aún no soportaba claves extranjeras; debería ser 3.6.19 o superior para que funcionen.

2. instalando la última versión de pysqlite desde aquí: pysqlite-2.6.0

después de eso comencé a recibir excepciones cada vez que fallaba la restricción de clave externa

espero que esto ayude, saludos