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

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

sqlite> PRAGMA foreign_keys = ON;

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

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

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

РЕДАКТИРОВАТЬ: Я думаю, что моя настоящая проблема заключается в том, что у меня установлено более одной версии SQLite, и Python не использует последнюю версию!

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

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

Источник

9 ответов

Решение

У меня сейчас это работает:

Загрузите последние сборки 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 для добавления () вещей мой импортный код неявно обрабатывал соединения отношений, поэтому никогда не мог потерпеть неудачу. Добавление '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) валяющихся вокруг.

Источник

Для последних версий (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)
Источник

Основываясь на ответах от 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()
Источник

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

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

Источник

Со страницы диалекта 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()
Источник

версия Однострочнаяответа Конни :

      from sqlalchemy import event

event.listen(engine, 'connect', lambda c, _: c.execute('pragma foreign_keys=on'))
Источник

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

  1. скачайте, соберите и установите последнюю версию sqlite здесь: http://www.sqlite.org/sqlite-amalgamation-3.6.23.1.tar.gz; до этого у меня был sqlite 3.6.16 на моей машине с Ubuntu; который еще не поддерживал внешние ключи; должно быть 3.6.19 или выше, чтобы они работали.

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

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

надеюсь, что это помогает, привет

Источник

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

Источник

Применять ограничения внешнего ключа для sqlite при использовании Flask + SQLAlchemy.

      from flask import Flask
from flask_sqlalchemy import SQLAlchemy

def create_app(config: str=None):
    app = Flask(__name__, instance_relative_config=True)
    if config is None:
        app.config.from_pyfile('dev.py')
    else:
        logger.debug('Using %s as configuration', config)
        app.config.from_pyfile(config)

    db.init_app(app)

    # Ensure FOREIGN KEY for sqlite3
    if 'sqlite' in app.config['SQLALCHEMY_DATABASE_URI']:
        def _fk_pragma_on_connect(dbapi_con, con_record):  # noqa
            dbapi_con.execute('pragma foreign_keys=ON')

        with app.app_context():
            from sqlalchemy import event
            event.listen(db.engine, 'connect', _fk_pragma_on_connect)

Источник: https://gist.github.com/asyd/a7aadcf07a66035ac15d284aef10d458 .

Источник
Другие вопросы по тегам