That helps a lot! Every time it is so enjoyable reading your reply, it's so clear and cogent. Thank you very much Simon! TWO THUMBS UP!!
On Tue, May 6, 2014 at 3:00 AM, Simon King <si...@simonking.org.uk> wrote: > On Tue, May 6, 2014 at 10:14 AM, Bao Niu <niuba...@gmail.com> wrote: > > > > I am seeking some advice on best practice regarding setting up > sqlite_pragma > > across applications. > > > > I have the following code which I currently put in each and every module > > across my applications, wherever such module uses SQLAlchemy. > >> > >> @sqlalchemy.event.listens_for(sqlalchemy.engine.Engine, "connect") > >> def set_sqlite_pragma(dbapi_connection, connection_record): > >> cursor = dbapi_connection.cursor() > >> cursor.execute("PRAGMA foreign_keys=ON") > >> cursor.close() > > > > In the example above, you are attaching an event listener to the > Engine *class*, which means it will be called any time any engine in > your application connects to the database. If you wanted, you could > restrict it to a single engine by attaching the event to the engine > instance instead, something like: > > engine = create_engine(dburi) > @sqlalchemy.event.listens_for(engine, 'connect'): > def handle_connect(dbapi_connection, connection_record): > # your code here > > > > > > > > It does the job, but is there a way to centralize this effort so I can > have > > this snippet only one place for all modules? > > I'm not sure if simply factoring the above snippet out and making it a > > separate module in itself will do the job, because each module will use > > independent sqlalchemy module, right? So setting ModuleA's sqlite_pragma > > doesn't have any effect on ModuleB's sqlite_pragma. Am I right here? > > I'm not sure I understand what you are saying here. Within a single > process, the sqlalchemy library will only be loaded once, no matter > how many different modules "import sqlalchemy". By attaching an event > handler to sqlalchemy.engine.Engine, you are asking for that code to > run for any engine in that process. > > > > > Hopefully some pros can give me some simple but practice advice here. > Many > > thanks. > > > > There shouldn't be anything wrong with putting that event handler in a > library module, as long as you are sure to import that module from > somewhere else in your application. > > People are often (rightfully) bothered by code that has import-time > side-effects. In this case, simply by importing your library module > you would be globally altering the sqlite behaviour for your process. > It might be nicer if your library module looked more like this: > > > def _set_sqlite_pragma(dbapi_connection, connection_record): > cursor = dbapi_connection.cursor() > cursor.execute("PRAGMA foreign_keys=ON") > cursor.close() > > def enable_sqlite_foreign_keys(): > sqlalchemy.event.listen(sqlalchemy.engine.Engine, 'connect', > _set_sqlite_pragma) > > > ...and then call the enable_sqlite_foreign_keys function somewhere in > your application setup code. > > Hope that helps, > > Simon > > -- > You received this message because you are subscribed to a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/lYzM5RwmGAw/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.