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
sqlalchemy features
Sure, the two methods of interest are in pylons.database here:
http://pylonshq.com/docs/0.9.4/module-pylons.database.html#create_engine
http://pylonshq.com/docs/0.9.4/module-pylons.database.html#make_session
Since a SQLAlchemy engine 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.
I primarily use SQLAlchemy with 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
from sqlalchemy import *
from sqlalchemy.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 a SQLAlchemy session 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 import make_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 2 SQLAlchemy plugins being used:
http://www.sqlalchemy.org/docs/plugins.myt#plugins_sessioncontext
http://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:
from sqlalchemy import *
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("No sqlalchemy database 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
>
