Sqlite/SQLAlchemy: как применять внешние ключи?

Новая версия SQLite имеет возможность принудительно вводить ограничения внешнего ключа, но для обратной совместимости вам необходимо включить ее для каждого подключения к базе данных отдельно!

sqlite> PRAGMA foreign_keys = ON;

Я использую SQLAlchemy - как я могу убедиться, что это всегда включается? Я пробовал это:

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

... но он не работает!... Что мне не хватает?

EDIT: Я думаю, что моя реальная проблема в том, что у меня установлена ​​более одной версии SQLite, а Python не использует последнюю версию!

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

Но я только что загрузил 3.6.23 и поместил exe в свой каталог проектов! Как я могу выяснить, какой из них использовать .exe и изменить его?

Ответы

Ответ 1

Теперь у меня есть это работает:

Загрузите последние сборки sqlite и pysqlite2, как описано выше: убедитесь, что 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

Затем добавьте 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()])

Тогда будьте осторожны, как вы проверяете, работают ли внешние ключи: у меня была некоторая путаница здесь. При использовании sqlalchemy ORM для вещей add() мой импортный код неявно обрабатывал соединения отношений, поэтому никогда не мог потерпеть неудачу. Добавление nullable=False к некоторым ForeignKey() заявлениям помогло мне здесь.

Чтобы проверить поддержку внешнего ключа sqlalchemy sqlite, нужно выполнить ручную вставку из декларативного класса 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)

Здесь wall_id и type_id оба являются ForeignKey(), и sqlite теперь правильно генерирует исключение, если пытается подключить недействительные fkeys. Так что это работает! Если вы удалите прослушиватель, sqlalchemy с радостью добавит неверные записи.

Я считаю, что основная проблема может быть несколько sqlite3.dll (или .so) валяется.

Ответ 2

В последних версиях (SQLAlchemy ~ 0.7) страница SQLAlchemy говорит:

PoolListener устарел. См. PoolEvents.

Тогда пример CarlS становится:

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)

Ответ 3

Основываясь на ответах от conny и shadowmatter, здесь код, который будет проверять, используете ли вы SQLite3 перед выпуском инструкции 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()

Ответ 4

Со страницы диалекта SQLite:

SQLite поддерживает синтаксис FOREIGN KEY при отправке операторов CREATE для таблиц, однако по умолчанию эти ограничения не влияют на работу таблицы.

Проверка ограничений в SQLite имеет три предварительных условия:

  • По крайней мере, версия 3.6.19 SQLite должна быть в использовании
  • Библиотека SQLite должна быть скомпилирована без включенных символов SQLITE_OMIT_FOREIGN_KEY или SQLITE_OMIT_TRIGGER.
  • Оператор PRAGMA foreign_keys = ON должен передаваться во все соединения перед использованием.

SQLAlchemy позволяет автоматически генерировать оператор PRAGMA для новых соединений с использованием событий:

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()

Ответ 5

В качестве более простого подхода, если создание вашего сеанса централизовано за вспомогательной функцией Python (вместо непосредственного предоставления движка SQLA), вы можете просто выполнить session.execute('pragma foreign_keys=on') перед возвратом только что созданного сеанса.

Подход прослушивателя пула нужен только в том случае, если произвольные части вашего приложения могут создавать сеансы SQLA с базой данных.

Ответ 6

У меня была такая же проблема, прежде чем (скрипты с ограничениями внешних ключей проходили, но принудительные ограничения не были применены механизмом sqlite); он решил:

  • загрузка, создание и установка последней версии sqlite отсюда: sqlite-sqlite-amalgamation; до этого у меня был sqlite 3.6.16 на моей машине ubuntu; которые еще не поддерживали внешние ключи; для работы они должны быть 3.6.19 или выше.

  • установка последней версии pysqlite отсюда: pysqlite-2.6.0

после этого я начал получать исключения, когда сбой ограничения внешнего ключа

надеюсь, что это поможет, считает

Ответ 7

Если вам нужно выполнить что-то для настройки в каждом соединении, используйте PoolListener.