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.
