SQLAlchemy Setup (Response to "ANN: Pylons 0.9.4 Released")
__wyatt
group, so I'm creating a new post.
On Dec 30 2006, 12:56 am, Ben Bangert <b...@groovie.org> wrote:
> On Dec 29, 2006, at 11:08 PM, Jose Galvez wrote:
>
> > Any chance we could get a short example showing how to use the new
> >sqlalchemyfeatures
>
> Sure, the two methods of interest are in pylons.database here:http://pylonshq.com/docs/0.9.4/module-pylons.database.html#create_enginehttp://pylonshq.com/docs/0.9.4/module-pylons.database.html#make_session
>
> Since aSQLAlchemyengine is a pooled connection, only one of them
> needs to be created for your application. The create_engine stores
> the connection pool in your 'g' object.
>From my reading of the code, ``create_engine`` *doesn't* store the
connection pool in ``g`` but ``make_session`` does. Also, from what I
can tell from your example, you never use ``create_engine`` (the one
from pylons.database), and it's not clear where you create your
engine.
I think that's because when pylons.database is imported,
``pylons.database.session_context`` is automatically created and in
the process calls ``make_session``, which in turn calls
``create_engine``. That engine is accessible by doing something like
this:
from pylons.database import session_context
engine = session_context.current.bind_to
I do that in my model so I can call metadata.connect(model.engine) in
my ``BaseController`` (aside: Elixir's metadata currently requires
doing this).
It seems like (in a basic setup) all you have to do is import
pylons.database
at some point during app initialization (probably in your model) and
clear
``pylons.database.session_context.current`` on every request, using
one of
these methods:
pylons.database.session_context.current = make_session()
# This next one is suggested by pjenvey in this post:
# http://groups.google.com/group/pylons-discuss/msg/0fb2b9f789b341ea
# It has the advantage of not having to import
# ``pylons.database.make_session``
del pylons.database.session_context.current
# May be slower than the two above
pylons.database.session_context.current.clear()
You can import ``session_context`` in your ``models.__init__`` to make
things a little simpler. This is what my current setup looks like:
# development.ini
[app:main]
# other stuff
sqlalchemy.dburi = mysql://wyatt:password@localhost/wyatt_dev
sqlalchemy.echo = true
# models/__init__.py
from pylons.database import session_context
from elixir import metadata
engine = session_context.current.bind_to
class SomeEntity(Entity):
has_field('title', String)
# etc.
# lib/base.py
def __call__(self, environ, start_response):
del model.session_context.current
model.metadata.connect(model.engine)
return WSGIController.__call__(self, environ, start_response)
And that's everything I have related to setting up SQLAlchemy for use
in my app. I struggled with this a bit, so I hope this helps somebody
(and I hope it really works!). Any corrections appreciated.
__wyatt
> I primarily useSQLAlchemywith the SessionContext plugin, which is
> what the QuickWiki example uses. I also use the assign_mapper plugin
> so that I don't need to use methods off the session directly, and can
> just query my model classes like so:
>
> c.violation = model.Violation.get_by(code=code)
>
> I'm using the ORM capability of SA, if you're not, the create_engine
> function should still come in handy.
>
> So first, here's how I setup my models, it's pretty easy to deviate
> from this as desired. I've put the SA metadata in its own module to
> avoid circular import issues since the other models all need the
> app's metadata object. It also makes it easier to cut-down on the
> imports in the individual model modules. I rather like having each
> model with its table in its own module as my ORM classes grow rather
> large with various methods to retrieve different sets of data. If you
> have more basic models, I'd suggest throwing them all in one module.
>
> models/
> __init__.py
> metadata.py
> restaurants.py
> violations.py
>
> # __init__.py
> # import the ctx and meta here so its in the controllers under model.
> from metadata import ctx, meta
>
> from restaurants import Restaurant
> from violations import Violation
>
> # metadata.py
> fromsqlalchemyimport *
> fromsqlalchemy.ext.assignmapper import assign_mapper
> from pylons.database import create_engine, session_context
>
> meta = MetaData()
> ctx = session_context
>
> # restaurants.py
> from metadata import *
>
> from inspections import Inspection, inspection_table,
> inspectionviolations_table
> from violations import Violation, violation_table
>
> restaurant_table= Table('restaurants', meta,
> Column('id', Integer, primary_key=True),
> Column('name', String(200), nullable=False),
> Column('address1', String, nullable=False),
> ...
> Column('franchise_id', Integer, ForeignKey('franchises.id')),
> )
>
> class Restaurant(object):
> def __init__(self, name, in_store=False, **kargs):
> self.name = name
> self.in_store = in_store
> for k, v in kargs.iteritems():
> if k in self.c.keys():
> setattr(self, k, v)
>
> assign_mapper(ctx, Restaurant, restaurant_table, properties=dict(
> inspections=relation(Inspection, lazy=False, cascade="all,
> delete-orphan",
> order_by=desc(Inspection.c.date), backref="restaurant"),
> )
> )
>
> # violations.py
> from metadata import *
>
> violation_table = Table('violations', meta,
> Column('id', Integer, primary_key=True),
> Column('code', String(10), nullable=False),
> Column('description', String, nullable=False),
> Column('correction', String),
> Column('county', String),
> )
>
> class Violation(object):
> def __init__(self, code, description, correction, county):
> self.code = code
> self.description = description
> self.correction = correction
> self.county = county
>
> assign_mapper(ctx, Violation, violation_table)
>
> So that's all the model files, with the assign_mapper plugin. This
> works with the assumption that you define your SA connection in your
> config ini file as 'sqlalchemy.dburi', also note that you can set
> 'sqlalchemy.echo = True' in your ini file to have all the SQL echoed
> to the console while you're running the app (rather handy).
>
> Several people noted on the mail list that aSQLAlchemysession is
> persistent and needs to be cleared every request. Alternatively, you
> can create a new SA session every request, which according to Mike
> Bayer is slightly faster. This is what my BaseController looks like:
>
> # add to the imports:
> from pylons.database importmake_session
>
> class BaseController(WSGIController):
> def __call__(self, environ, start_response):
> c.model = model
> model.ctx.current =make_session()
> return WSGIController.__call__(self, environ, start_response)
>
> At this point, you're ready to go with accessing your models while
> your app is running. For example, here's one of my controllers:
> class RestaurantController(BaseController):
> def detail(self, id):
> c.restaurant = model.Restaurant.get_by(id=id)
> if not c.restaurant:
> return render_response('/restaurant/notfound.html')
> return render_response('/restaurant/detail.html')
>
> For reference, here are the 2SQLAlchemyplugins being used:http://www.sqlalchemy.org/docs/plugins.myt#plugins_sessioncontexthttp://www.sqlalchemy.org/docs/plugins.myt#plugins_assignmapper
>
> Finally, you will want to create your tables during websetup.py.
> Here's what my websetup.py looks like:
> fromsqlalchemyimport *
> import YOURPROJ.models as model
> from paste.deploy import appconfig
>
> def setup_config(command, filename, section, vars):
> app_conf = appconfig('config:'+filename)
> if not app_conf.has_key('sqlalchemy.dburi'):
> raise KeyError("Nosqlalchemydatabase config found!")
> print "Connecting to database %s..."%repr(app_conf
> ['sqlalchemy.dburi'])
> engine = create_engine(app_conf['sqlalchemy.dburi'])
>
> model.meta.drop_all(engine)
>
> print "Creating tables"
> model.meta.create_all(engine)
> print "Successfully setup"
>
> You should be set from here on out.
>
> Cheers,
> Ben
__wyatt
that it looks like part of Ben's message. So, in case it's not clear,
my response starts at "From my reading of the code, ``create_engine``
*doesn't* store the...".
__wyatt
On Mar 23, 7:37 pm, "__wyatt" <wyatt.lee.bald...@gmail.com> wrote:
> Note: I couldn't reply directly to the original message[1] in the
> group, so I'm creating a new post.
>
> [1]http://groups.google.com/group/pylons-discuss/browse_frm/thread/1f05f...
>
> On Dec 30 2006, 12:56 am, Ben Bangert <b...@groovie.org> wrote:
>
> > On Dec 29, 2006, at 11:08 PM, Jose Galvez wrote:
>
> > > Any chance we could get a short example showing how to use the new
> > >sqlalchemyfeatures
>
> > Sure, the two methods of interest are in pylons.database here:http://pylonshq.com/docs/0.9.4/module-pylons.database.html#create_eng...
> #http://groups.google.com/group/pylons-discuss/msg/0fb2b9f789b341ea
> > For reference, here are the 2SQLAlchemyplugins being used:http://www.sqlalchemy.org/docs/plugins.myt#plugins_sessioncontexthttp...
__wyatt
> group, so I'm creating a new post.
>
>
> On Dec 30 2006, 12:56 am, Ben Bangert <b...@groovie.org> wrote:
>
> > On Dec 29, 2006, at 11:08 PM, Jose Galvez wrote:
>
> > > Any chance we could get a short example showing how to use the new
> > >sqlalchemyfeatures
>
Elixir has its own SessionContext that it uses with assign_mapper [1],
and what I wrote above doesn't seem to work very well with Elixir
(i.e., objects aren't refreshed). Here's what seems to be working now
(showing only the relevant parts):
> # old models/__init__.py
> from pylons.database import session_context
> from elixir import metadata
> engine = session_context.current.bind_to
> class SomeEntity(Entity):
> has_field('title', String)
> # etc.
# new & improved models/__init__.py
from elixir import metadata, objectstore
session_context = objectstore.context
class SomeEntity(Entity):
has_field('title', String)
# etc.
> # old lib/base.py
> def __call__(self, environ, start_response):
> del model.session_context.current
> model.metadata.connect(model.engine)
> return WSGIController.__call__(self, environ, start_response)
# new & improved lib/base.py
from pylons.database import make_session
def __call__(self, environ, start_response):
db_session = make_session()
session_context.current = db_session
metadata.connect(db_session.bind_to)
__wyatt