SQLAlchemy object
Michael Bayer
based on his proposal for a facade that deals with the various session
(context)/engine/metadata mixtures. I think this would be a good
idea for the very reason that it encapsulates all the details of how
engines/metadata/session/etc get created and used. right now we have
tutorials and such all throwing out BoundMetaDatas and Sessions with
bind_to's and a lot of mixing up of paradigms. by putting it all
inside a single facade, nobody has to make those decisions anymore if
they dont want to. the facade would be configurable to support a few
different operational paradigms, including engines bound to tables,
or engines bound to sessions...each of which has its pros and cons
depending on the type of app youre writing (i.e. one app with one
database, one app with multiple databases for different objects, one
app with multiple databases for different use cases (e.g. separate
readonly/write db's), multiple apps on multiple databases, multiple
apps sharing common tables across multiple databases). by default it
would internally bind engines to metadatas which is the most flexible
configuration for a single application that may persist different
model classes to different dbs.
this object would just give you everything you need: metadata,
engine, session. how they got constructed, which one connects to
what, you dont care. because really, most people dont. qualified
method accessors engine/metadata for alternate connection names would
be provided. multiple applications that talk to different
tablesets, i.e. for middleware that has its own deal going on, just
makes its own SAContext. this probably would obviate the need for a
"global engine registry" at all (though im not sure).
from pylons.database import SAContext
sac = SAContext() # connects to engine in the .ini file by default
sac = SAContext(url='sqlite://', engine_args={}) # or send a url,
args in
# create a table. metadata is a BoundMetaData to the default engine.
users = Table('users', sac.metadata, Column(...)...)
# alternatively, we could have a table creation function on the
object w/o the metadata argument (opinions?)
users = sac.define_table('users', Column(...)...)
# add extra engine
sac.add_engine('pgconn', url='postgres://', engine_args={})
# table on alt engine. metadata is a BoundMetaData to the 'pgconn'
engine.
remote_users = Table('remote_users', sac.get_metadata('pgconn'),
Column(...)...)
# mappers can bind to the sessioncontext via the SAContext directly
# (its got a SessionContext inside, sends out SessionContext.ext out
# via "ext")
mapper(User, users, extension=sac.ext)
mapper(RemoteUser, remote_users, extension=sac.ext)
# query accessor
sac.query(Users).filter_by(...).list()
# session. gets pulled from the SessionContext. note you never deal
# with the context itself.
# session doesnt even have a bind_to, its just using the engines for
# each table it gets.
sac.session.save(User())
sac.session.save(RemoteUser())
# that way this statement saves each user to its correct DB without
# any issues.
sac.session.flush()
if we get everyone on a simple object like this, then we can plug in
strategies for other scenarios, build up clustering strategies, etc.
Christoph Haas
> at Mike Orr's prodding, ive worked up a SQLAlchemy context object
> based on his proposal for a facade that deals with the various session
> (context)/engine/metadata mixtures. I think this would be a good
> idea for the very reason that it encapsulates all the details of how
>
> from pylons.database import SAContext
> sac = SAContext() # connects to engine in the .ini file by default
I like your prosal a lot. It addresses everything that has annoyed me so
far. But why is it limited to pylons.database? Elixir tries to make SA
simpler. Can't the session context itself be jazzed up so thar
pylons.database does little more than read the sqlalchemy.dburi and
provide a session context?
Christoph
Michael Bayer
i consider this facade to be a framework feature. it has an opinion
about how things should be organized. SA itself isnt a framework and
doesnt want strong opinions about how things should be organized, and
SessionContext is just a small building block towards an end-user
solution. im pretty sure making a rich facade out of SessionContext
would throw the zalchemy guys for a loop, for example.
thats not to say SAContext cant be bundled with SQLAlchemy though but
at the moment it "feels" like it should be part of pylons.
Mike Orr
I also think something like SAContext would make a good optional
feature for SQLAlchemy, but I'd rather see it proven in Pylons first.
There's also a get_engine_args() function which Michael didn't say
much about but it's going to be a large percentage of the code. It's
not just a matter of "sqlalchemy.dburi". There's also
"sqlalchemy.pool_recycle" which is critical for MySQL, as well as the
other engine options (or at least the scalar ones). It will also
scale to multiple engines along the lines of
"sqlalchemy.engine_key.dburi". This code is very Pylons specific, or
at least it is until other frameworks/applications start using the
same config file format.
--
Mike Orr <slugg...@gmail.com>
Mike Orr
> There's also a get_engine_args() function which Michael didn't say
> much about but it's going to be a large percentage of the code. It's
> not just a matter of "sqlalchemy.dburi". There's also
> "sqlalchemy.pool_recycle" which is critical for MySQL, as well as the
> other engine options (or at least the scalar ones). It will also
> scale to multiple engines along the lines of
> "sqlalchemy.engine_key.dburi". This code is very Pylons specific, or
> at least it is until other frameworks/applications start using the
> same config file format.
By the way, I'm hoping to get a higher-level config-parsing function
or two into Pylons as part of this. Both SAContext and Buffet need to
read multiple keys with a common prefix and convert int/boolean
values, and perhaps recognize a subkey in between
("sqlalchemy.engine2.dburi"). This shouldn't have to be reinvented in
every library and user application, it should be part of Pylons or
ideally Paste.
By the way, what's the official home of CONFIG? paste.deploy.CONFIG?
paste.deploy.config.CONFIG? paste.config.CONFIG? I thought
paste.deploy.CONFIG was the current recommendation, put
pylons.util.config_get() thinks it's paste.config.CONFIG. But how can
CONFIG be a paste.config thing if it's part of PasteDeploy and not
Paste?
--
Mike Orr <slugg...@gmail.com>
Jose Galvez
great! do you think your proposal will work with the assignmapper extension?
Jose
Mike Orr
proposed SAContext module actually exists. His is a one-engine
PostgreSQL situation with straightforward relationships.
=== models/init.py
from pylons.database import session_context as ctx
engine = ctx.current.bind_to
meta = BoundMetaData(engine)
table1 = Table("Table1", meta, ...)
class Class1(object):
pass
mapper = Class1, table1)
def high_level_access_function():
return ctx.current.query(Class1).filter(...).order_by(...).list()
def another_access_function():
"""Returns the Query so the caller can call .list(), .count(), etc
as it wishes from the same query.
"""
return ctx.current.query(Class1).filter(...).order_by(...)
===
Three things to note here:
(1) Pylons applications do not need DynamicMetaData; using it adds
compilcation to no advantage. BoundMetaData works for both autoloaded
tables and conventionally-defined tables. Both BoundMetaData and the
engine are thread safe. ("SQLAlchemy for People in a Hurry" is wrong
in this regard.)
(2) This configuration honors 'sqlalchemy.dburi' and 'sqlalchemy.echo'
in the config file, but there is no way to pass other engine options.
If you need to pass other options like "pool_recycle" for MySQL, it's
easiest right now to copy pylons.database to myapp.lib.database and
hardcode the desired options into the create_engine() function. Then
use your module instead of pylons.database.
(3) Proper SQLAlchemy as articulated by Michael Bayer would be to
define the engine first, then a BoundMetaData, then a session_context
with no special arguments (i.e,. no custom make_session). Binding a
session to an engine would not be necessary because all tables are
permanently bound to the engine via BoundMetaData. However,
pylons.database currently takes the opposite approach, predefining a
session_context so you have to use session_context.current.bind_to to
access the engine, or create an identical engine with create_engine().
Yet on the other hand, pylons.database conveniently parses the dburi
for you, and it handles the app_scope() apparently needed for
session_context, so it's easier to modify a copy of pylons.database
than to write everything from scratch.
--
Mike Orr <slugg...@gmail.com>
Mike Orr
> engine = ctx.current.bind_to
> meta = BoundMetaData(engine)
> table1 = Table("Table1", meta, ...)
> class Class1(object):
> pass
> mapper = Class1, table1)
Sorry, that should be:
mapper(Class1, table1)
--
Mike Orr <slugg...@gmail.com>