Re: [sqlalchemy] Do we have a sqlalchemy code or migration script to enforce foreign key constraint for sqlite3?

[Audrius Kažukauskas]

On Wed, 2012-12-05 at 09:07:20 -0800, junepeach wrote:
> I just did a testing, basically copied and ran the code of below link:
> http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.html
> in the middle of process, I copied and ran below code:
> 
> 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()
> 
> then insert a couple rows to both user and addresses tables.  I didn't
> insert user id '3', however I could insert foreign key user id '2'
> into addresses table. So foreign key constraint was not enforced.

You need to register the event handler before you make any attempts to
connect to your database.  See attached the example that works as
expected (fails with IntegrityError exception due to foreign key
constraint).

-- 
Audrius Kažukauskas
http://neutrino.lt/

from sqlalchemy import *
from sqlalchemy import event
from sqlalchemy.orm import *
from sqlalchemy.engine import Engine
from sqlalchemy.ext.declarative import declarative_base


db_engine = create_engine('sqlite:///:memory:')

Session = sessionmaker(bind=db_engine)
db_session = Session()


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


Base = declarative_base()


class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True, nullable=False)


class Email(Base):
    __tablename__ = 'emails'
    id = Column(Integer, primary_key=True)
    address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey(User.id))
    user = relationship(User, backref='emails')


Base.metadata.create_all(bind=db_engine)

email = Email()
email.user_id = 42
email.address = u'f...@example.org'
db_session.add(email)
# Will raise IntegrityError exception.
db_session.commit()

pgprKFlSuHYfk.pgp
Description: PGP signature