Best use of engine_from_config

[Andrew Martin]

I have a question about using creating engines along with pretty much any python web framework, but I'm specifically working with Pyramid. For obvious reasons. :) 

The sqlalchemy docs state this:

"The typical usage of create_engine() is once per particular database URL, held globally for the lifetime of a single application process. A single Engine manages many individual DBAPI connections on behalf of the process and is intended to be called upon in a concurrent fashion. The Engine is not synonymous to the DBAPI connect function, which represents just one connection resource - the Engine is most efficient when created just once at the module level of an application, not per-object or per-function call."

But with most frameworks I've worked with--and with Pyramid specifically--the best way to access the .ini settings is through the request object, which means that the engine only lives for the lifetime of the request? I think?

I'm unsure about this.

My sqlalchemy setup is a little weird (read: dumb) in the sense that I'm not using the ORM layer at all. I use the engine to execute functions on a postgres server which returns only a single json_aggs result. Then I either make it a dict if it's going to a template or stick it as json if it's going to an API that expects json. It probably sounds goofy, but it works extremely well. And it's fast as hell.

But I'm uncomfortable with the way I'm using engine_from_config. According to the docs, I should create one and only one engine that exists for the lifetime of the application. But the only reasonable way for me to access the settings object is to go through a request object. And I know I must be misunderstanding something here because, now that I think about it, there's no effing way any web framework is reading the config from a .ini file for every request. I mean, php would do that, but we are Python here.

So, all that aside, what's the best way to do this?

In my mind, if it's a per-request read of the settings, the it belongs inside an object as part of the constructor. But if it's not, where do I put it while keeping with best practices for web frameworks?

[Michał Dobrzański]

For Pyramid application I cache engines in global variable under connection string keys. I cache engines when they're created. This way each time I need to use the engine I reach to that dictionary instead of creating it with each request.

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

[Andrew Martin]

That’s a really interesting approach, and I think I might try it.

My concern here is whether I’m using things in the appropriate way. I think I’m failing to understand something about the way sqlalchemy works, and that’s the reason I’m asking this. For myself and for posterity. :)

-andrew

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/kD2cccX6fzY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Andrew Martin]

Also, do be clear, what I have works. I'm passing the request object to the model and then creating the engine based on the request. It works, but I have a nagging feeling that this is not good and definitely not best.

[Jonathan Vanasco]

You're doing it wrong, but it's not too hard to correct - it will mostly be shifting your existing code-blocks around as-is.

Check out the current version of Pyramid's scaffold for SqlAlchemy (I think the template change happened in Pyramid 1.6) , which uses a more streamlined approach that should make more sense than older methods.

Think of the SqlAlchemy "engine_from_config" as less of an engine, and more of something that manages a connection pool.  With that understanding, you basically want it to start up on application setup as part of your pyramid app's `__init__.py:main` def, where the `config` and `settings` are available -- otherwise you'll lose all the benefits of having a connection pool, as the pool/connections would be destroyed on every request.

In the current Pyramid scaffold, __init__.py includes models/__init__.py through a scan.  The models init does the following in the 'includeme' hook on startup : (https://github.com/Pylons/pyramid/blob/master/pyramid/scaffolds/alchemy/%2Bpackage%2B/models/__init__.py_tmpl)

1. create an engine from config includeme calls getengine which calls `engine_from_config`

2. sets up a request method that makes a memoized/reifed factory for the current transaction-aware dbSession onto the request