Sqlite / SQLAlchemy: ¿cómo hacer cumplir las claves externas?

La nueva versión de SQLite tiene la capacidad de hacer cumplir las restricciones de la clave externa, pero por el bien de la compatibilidad con versiones anteriores, debe activarlo para cada conexión de base de datos por separado.

sqlite> PRAGMA foreign_keys = ON; 

Estoy usando SQLAlchemy. ¿Cómo puedo asegurarme de que esto 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 problema real es que tengo más de una versión de SQLite instalada, y Python no está usando la última versión.

 >>> 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?

Ahora tengo este trabajo:

Descargue las últimas comstackciones de sqlite y pysqlite2 tal como se describió anteriormente: asegúrese de que las versiones correctas estén siendo utilizadas en tiempo de ejecución por python.

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

A continuación, 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()]) 

Entonces, tenga cuidado con la forma en que prueba si las claves externas están funcionando: aquí tuve algunas confusiones. Cuando utilizo sqlalchemy ORM para agregar () cosas, mi código de importación estaba manejando implícitamente las conexiones de la relación, por lo que nunca podría fallar. Agregar ‘nullable = False’ a algunas declaraciones de ForeignKey () me ayudó aquí.

La forma en que pruebo sqlalchemy sqlite la compatibilidad con la clave externa está habilitada es hacer una inserción manual de una clase declarativa ORM:

 # 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 ForeignKey () ‘s y sqlite lanza una excepción correctamente ahora si está intentando conectar fkeys inválidas. ¡Así funciona! Si elimina la escucha, sqlalchemy felizmente agregará entradas no válidas.

Creo que el problema principal puede ser el de varios sqlite3.dll (o .so).

Para las 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) 

Sobre la base de las respuestas de conny y shadowmatter, aquí hay un código que verificará si está utilizando SQLite3 antes de emitir la statement 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() 

Desde la página del dialecto SQLite :

SQLite admite la syntax de FOREIGN KEY cuando se emiten sentencias CREATE para tablas, sin embargo, de forma predeterminada, estas restricciones no afectan 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 de SQLite debe comstackrse 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 sentencia PRAGMA se emita automáticamente para nuevas conexiones mediante el 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() 

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 contra la base de datos.

Tuve el mismo problema antes (los scripts con restricciones de clave externa estaban pasando, pero el motor sqlite no impuso restricciones de activación); lo resolvió por:

  1. descargando, construyendo e instalando la última versión de sqlite desde aquí: sqlite-sqlite-amalgamation ; antes de esto tuve sqlite 3.6.16 en mi máquina ubuntu; que no soporta claves extranjeras todavía; 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 empecé a recibir excepciones cada vez que fallaba la restricción de clave externa

Espero que esto ayude, saludos.

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