Best way to set up sqlite_pragma for all applications

[Bao Niu]

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

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?

Hopefully some pros can give me some simple but practice advice here. Many thanks.

[Simon King]

On Tue, May 6, 2014 at 10:14 AM, Bao Niu <niub...@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

[Bao Niu]

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!!

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.