Default way of using SQLAlchemy with Pylons
Karlo Lozovina
http://wiki.pylonshq.com/display/pylonsdocs/Using+SQLAlchemy+with+Pylons
the new default, preferred way of using SQLalchemy with Pylons? It
seems now I have to add something like:
engine = engine_from_config(config, 'sqlalchemy.')
init_model(engine)
otherwise I get errors like:
<class 'sqlalchemy.exceptions.ProgrammingError'>: (ProgrammingError)
SQLite objects created in a thread can only be used in that same
thread.The object was created in thread id -1224172656 and this is
thread id -1249350768 None [{}]
Thanks for your input.
Karlo Lozovina
On May 20, 11:18 am, Karlo Lozovina <karlo.lozov...@gmail.com> wrote:
> the new default, preferred way of using SQLalchemy with Pylons? It
> seems now I have to add something like:
> engine = engine_from_config(config, 'sqlalchemy.')
> init_model(engine)
every action of my controllers. That seems somehow redundant to me...
Luis Bruno
>> engine = engine_from_config(config, 'sqlalchemy.')
>> init_model(engine)
>>
> Silly me :). What I wanted to say, I have to add that piece of code to
> every action of my controllers. That seems somehow redundant to me...
>
I use an WSGI fake-middleware, which fires init_model and then returns
the next-app. Here' some code, below. There's not much point in yielding
or splitting the work into two functions... This is meant for multiple
connections, to be used like this in paster. If you don't need multiple
engines, you can simplify greatly by using engine_from_config() instead
of reinventing the wheel.
---8<---
[filter:database]
use = egg:OurCompany#sqlalchemy
trac = postgres://trac:password@trachost/trac
trac.models = company.dubiousapp.model company.reportingapp.model
shipping = mssql://user:pw@BigHonkingBox/company
shipping.schema = shipping
shipping.models = company.shipping.model
shipping.module = pymssql
---8<---
---8<---
def parse(config):
engines = dict()
for key, info in config.items():
name = key.split('.')[0]
engines.setdefault(name, dict(uri=None, args=dict()))
if '.' in key:
key = key.split('.', 1)[1:2][0]
assert key, key
engines[name]['args'][key] = info
else:
engines[name]['uri'] = info
return engines
def walk(engines):
for _, info in engines.items():
assert 'args' in info, info
assert 'uri' in info, info
models = info['args'].pop('models')
schema = info['args'].pop('schema', None)
module = info['args'].pop('module', None)
if module:
module = resolver.resolve(module)
info['args']['module'] = module
engine = sa.create_engine(info['uri'], **info['args'])
for model in models.split():
model = resolver.resolve(model)
yield engine, model, schema
def sqlalchemy_filter_app_factory(app, _, **config):
for engine, model, schema in walk(parse(config)):
model.__init__(engine, schema = schema)
return app
---8<---
If anyone cares enough to rework this crap a bit and post it, I'd be
delighted!
Kumar McMillan
>>>
>>> engine = engine_from_config(config, 'sqlalchemy.')
>>> init_model(engine)
>>>
>> every action of my controllers. That seems somehow redundant to me...
>>
I also was wondering if this was the "right thing to do." I solve it
by putting this in myapp.lib.base :
class BaseController(WSGIController):
def __call__(self, environ, start_response):
model.db.connect(config['sqlalchemy.default.uri'])
try:
return WSGIController.__call__(self, environ, start_response)
finally:
model.db.session.remove()
... where model/db.py is more or less this:
from sqlalchemy import create_engine
from elixir import session, metadata
def connect(dsn):
bind = create_engine(dsn)
session.bind = bind
metadata.bind = bind
Luis Bruno
> class BaseController(WSGIController):
> def __call__(self, environ, start_response):
> model.db.connect(config['sqlalchemy.default.uri'])
> try:
> return WSGIController.__call__(self, environ, start_response)
> finally:
> model.db.session.remove()
>
I must have missed this: why connect-and-bind() on each request?
.oO(yeah, I'd forgot about Session.remove() after each request; that was
painful)
Kumar McMillan
> Kumar McMillan escreveu:
>>
>> class BaseController(WSGIController):
>> def __call__(self, environ, start_response):
>> model.db.connect(config['sqlalchemy.default.uri'])
>> try:
>> return WSGIController.__call__(self, environ, start_response)
>> finally:
>> model.db.session.remove()
>>
>
> I must have missed this: why connect-and-bind() on each request?
because otherwise I get an error similar to above: that no engine has
been defined for the current thread. BTW, I am using mod_wsgi on
nginx so I believe it uses a pool of worker threads for each request,
spawning new ones as needed.
Luis Bruno
> On Tue, May 20, 2008 at 11:32 AM, Luis Bruno <m...@lbruno.org> wrote:
>
>> I must have missed this: why connect-and-bind() on each request?
>>
> because otherwise I get an error similar to above: that no engine has
> been defined for the current thread.
Yes, I vaguely recall a similar error when playing with SQLite and a
multi-threaded WSGI server.
I can't recall if create_engine(..., strategy="threadlocal") was meant
for these cases; I apologize in advance if the suggestion is stupid: I
can't understand if SQLAlchemy meant to deal with multithreading issues
or if it punts to the app developer.
I don't create connections (or pools) myself; I'm using the magic
Session.mapper() functionality instead of orm.mapper(), so I don't know
these details.
Mike Orr
<karlo.l...@gmail.com> wrote:
>
> Hi all, is this:
>
> http://wiki.pylonshq.com/display/pylonsdocs/Using+SQLAlchemy+with+Pylons
>
> the new default, preferred way of using SQLalchemy with Pylons?
Yes. If you create a new app in Pylons' development version, it will
ask if you want SQLAlchemy, and if so it will preconfigure the model
according to that article.
> It
> seems now I have to add something like:
>
> engine = engine_from_config(config, 'sqlalchemy.')
> init_model(engine)
This is done in environment.py, as shown in "The Engine" section of
the article. It is not normally done in the base controller.
> otherwise I get errors like:
>
> <class 'sqlalchemy.exceptions.ProgrammingError'>: (ProgrammingError)
> SQLite objects created in a thread can only be used in that same
> thread.The object was created in thread id -1224172656 and this is
> thread id -1249350768 None [{}]
SQLite does use one connection per thread, while other engines may
give out multiple connections in one thread. You shouldn't have to
use the "threadlocal" engine strategy; it's built into the SQLite
engine.
But I don't know why it would use the wrong connnection in a thread.
Nobody else has reported this problem. When exactly does this occur,
and what kinds of queries does your application contain?
--
Mike Orr <slugg...@gmail.com>
Luis Bruno
> If you create a new app in Pylons' development version, it will
> ask if you want SQLAlchemy, and if so it will preconfigure the model
> according to that article.
>
below, the session itself doesn't need to know what bind= it will use.
Doing it the way in the pylonsdocs hinders multiple databases in the
same session (but in different MetaData()s.
---8<---
def __init__(bind):
metadata.bind = bind
def __setup__(bind):
metadata.bind = bind
metadata.create_all()
# XXX: hookup sa-migrate
metadata = sa.MetaData()
Session = scoped_session(sessionmaker(transactional=True, autoflush=True))
table = Table(...)
class Stuff(object):
pass
Session.mapper(Stuff, table)
---8<---
Mike Orr
The default model was chosen after extensive discussions with Michael
Bayer, SQLAlchemy's creator, when SQLAlchemy 0.4 was in beta. He
regretted putting bound metadata in tutorials because people were
blindly using it without really understanding it. (He also dropped a
DynamicMetaData which was even more misused.) Mike recommended bound
sessions instead, so I have merely followed his advice.
This was actually just one of several issues where SQLAlchemy 0.3 had
gotten too magical and messy ("several ways to do it"), and the Pylons
0.9.5 default model was very bad. So we all agreed on certain best
practices for SQLAlchemy 0.4, which guided this article and the
default model.
I should mention that the separate 'meta' module and the init_model()
function were not part of the original recommendation, since they were
invented later.
In the SQLAlchemy manual Mike has kept silent about some of his
opinions. Several features in SQLAlchemy remain only because of user
demand, and not because Mike thinks they're the best way. For
instance, Session.mapper(). So he moved metadata.bind from the
tutorial to a later section but doesn't argue for or against it. I
personally think he should make his recommendations explicit in the
manual, but instead he's chosen to neutrally support everything that's
not outright bad.
There's one practical problem with bound metadata. If you use
transactional sessions and non-ORM commands simultaneously, the
non-ORM commands are outside the transaction. That was part of the
motivation for recommending bound sessions. Mike added
Session.execute(...) to allow non-ORM commands to be executed within
the transaction, so the article recommends this.
If you have multiple databases, you can bind different tables to
different engines using the binds={...} argument to sessionmaker().
You can also change the bindings at any time via
"Session.configure(binds=)" or "session = Session(); session.binds =
{...}". I hinted that in the article but did not go into details
because advanced users will find it, along with metadata.bind, in the
SQLAlchemy manual. If you think multiple databases should be
supported more in the article, please add a comment to the article and
it will be considered for a future version.
--
Mike Orr <slugg...@gmail.com>
Kumar McMillan
>> engine = engine_from_config(config, 'sqlalchemy.')
>> init_model(engine)
>
> This is done in environment.py, as shown in "The Engine" section of
> the article. It is not normally done in the base controller.
>
>> otherwise I get errors like:
>>
>> <class 'sqlalchemy.exceptions.ProgrammingError'>: (ProgrammingError)
>> SQLite objects created in a thread can only be used in that same
>> thread.The object was created in thread id -1224172656 and this is
>> thread id -1249350768 None [{}]
>
> SQLite does use one connection per thread, while other engines may
> give out multiple connections in one thread. You shouldn't have to
> use the "threadlocal" engine strategy; it's built into the SQLite
> engine.
>
> But I don't know why it would use the wrong connnection in a thread.
> Nobody else has reported this problem. When exactly does this occur,
> and what kinds of queries does your application contain?
I see the same kind of error when using postgres. As I understand it,
strategy='threadlocal' just allows you to share the connection per
*thread*, like you mentioned. However, the problem is that in
mod_wsgi when you are running a multi-threaded server your application
object is actually passed to each new thread, which then does
application(environ, start_response). As far as I can tell,
load_environment() is not run in each thread, only once when the
server starts up. To illustrate it, here is what my app.wsgi file
looks like:
import os
from paste.deploy import loadapp
import paste.script.appinstall
config = os.environ['PYLONS_CONFIG_PATH']
cmd = paste.script.appinstall.SetupCommand('setup-app')
cmd.run([config])
application = loadapp('config:' + config)
The fix is to create and bind a new engine object per __call__ in
BaseController and I suppose the thread of this email is whether or
not that's the right thing to do. Seems ok to me. You wouldn't want
the same engine object to be shared across each thread anyway since
simultaneous things would be happening in a heavily loaded site. A
secondary topic here might be whether or not the sqlalchemy docs
should be updated for this scenario. I'm not sure that a
multi-threaded mod_wsgi server setup is in the scope of that article
or not. I suppose it should be documented somewhere ;)
hmm, I just found this:
http://code.google.com/p/modwsgi/wiki/IntegrationWithPylons
... and noticed the suggested app.wsgi script does not explicitly call
'setup-app' like mine does. I have no idea where load_environment()
would be called in that case then.
K