I have confirmed that it works from the sqlite3 interactive session: sqlite> insert into child values(33); Error: foreign key mismatch
I will try it with pysqlite...see what happens... On Apr 13, 9:59 am, "Michael Bayer" <[email protected]> wrote: > NickPerkins wrote: > > Thanks, I tried it...but could not get the desired result ( enforced > > FKs using SQLite ). > > Here is my test code: > > > #!/usr/bin/env python > > import sqlalchemy > > from sqlalchemy.interfaces import PoolListener > > > class MyListener(PoolListener): > > def connect(self, dbapi_con, con_record): > > dbapi_con.execute('PRAGMA foreign_keys = ON;') > > > engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True, > > listeners=[MyListener()]) > > > metadata = sqlalchemy.MetaData(bind=engine) > > > from sqlalchemy import Table, Column, ForeignKey, Integer > > > parent_table = Table('parent',metadata, > > Column('id',Integer,primary_key=True) > > ) > > > child_table = Table('child',metadata, > > Column('id',Integer,primary_key=True), > > > Column('parent_id',Integer,ForeignKey('parent.id')) > > ) > > > metadata.create_all(bind=engine) > > > engine.connect() > > engine.execute(parent_table.insert().values(id=44)) > > > try: > > engine.execute(child_table.insert().values(id=55,parent_id=33)) > > print 'Foreign Keys were NOT enforced!' > > except: > > print 'Foreign Keys were enforced.' > > have you confirmed this works with a raw sqlite3 connection ? > > > > > > > ------------------------------------ > > The result I get is :"not enforced". > > using SQLite 3.6.23 on Windows > > Am I doing it right? > > > On Apr 12, 8:26 pm, Michael Bayer <[email protected]> wrote: > >> On Apr 12, 2010, at 7:22 PM, NickPerkins wrote: > > >> > Apparently, new versions of SQLite will enforce Foreign Keys ( unlike > >> > previous versions ), but, for the sake of backwards-compatibility, you > >> > have to explicitly turn on foreign key enforcement by issuing: "pragma > >> > foreign_keys=on;". > > >> >http://www.sqlite.org/foreignkeys.html > > >> > I am new to SQLAlchemy -- how can I implement this in my program? > >> > The docs say that it must be turned on for each database connection > >> > separately. > > >> Apply a PoolListener to your engine which implements this call for each > >> new connection: > > >>http://www.sqlalchemy.org/docs/reference/sqlalchemy/interfaces.html?h... > > >> > -- > >> > You received this message because you are subscribed to the Google > >> Groups "sqlalchemy" group. > >> > To post to this group, send email to [email protected]. > >> > To unsubscribe from this group, send email to > >> [email protected]. > >> > For more options, visit this group > >> athttp://groups.google.com/group/sqlalchemy?hl=en. > > > -- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > For more options, visit this group at > >http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
