that it looks like part of Ben's message. So, in case it's not clear,
*doesn't* store the...".
> 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...
> > 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...
> > 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
|
