SQLAlchemy, Again

On 12/11/06, Sean Davis <seandavigmail.com> wrote:
> Despite a couple of days of reading and typing, I
remain at a loss as to how
> to go about implementing SQLalchemy as a model using
reflection.  Does
> anyone have any complete examples of doing so?  The
quickwiki example is
> close, but there are so many posts about how it should
have been done that I
> fear that I lost the forest for the trees (or perhaps
vice-versa).

I do have a complete example.  Did you read my document?

http://pylonshq.com/project/pylonshq/wiki/SqlAlche
myWithPylons

Best Regards,
-jj

-- 
http://jjinux.blogspot.co
m/

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the
Google Groups "pylons-discuss" group.
To post to this group, send email to pylons-discussgooglegroups.com
To unsubscribe from this group, send email to
pylons-discuss-unsubscribegooglegroups.com
For more options, visit this group at h
ttp://groups.google.com/group/pylons-discuss?hl=en
-~----------~----~----~----~------~----~------~--~---

On Dec 11, 2006, at 3:25 PM, Sean Davis wrote:

> Unfortunately, I did.
>
> I just got a working example running without any of the
config  
> stuff working--the model has a hard-coded dsn in it
right now.  I  
> can work backwards from that toward your example.

I think it probably doesn't help that SQLAlchemy is
extremely  
flexible (plugins and such that each allow different ways to
use it),  
coupled with Pylons being rather flexible. It can be really
tough to  
figure out the "right" way should such a way exist
for your app.

I've put two functions to help maintain a SQLAlchemy db
engine pool  
properly in Pylons (attach the pool to your apps 'g'
object), and  
create a sqlalchemy session. They are however only in the
latest dev  
version at the moment. Once 0.9.4 is out, there'll be some
new docs  
with it to illustrate using it.

I'll write up an example of my setup in a few hours when I
get home.

HTH,
Ben

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the
Google Groups "pylons-discuss" group.
To post to this group, send email to pylons-discussgooglegroups.com
To unsubscribe from this group, send email to
pylons-discuss-unsubscribegooglegroups.com
For more options, visit this group at h
ttp://groups.google.com/group/pylons-discuss?hl=en
-~----------~----~----~----~------~----~------~--~---

On 12/11/06, Sean Davis <seandavigmail.com> wrote:
> On 12/11/06, Shannon -jj Behrens <jjinuxgmail.com> wrote:
> > On 12/11/06, Sean Davis <seandavigmail.com> wrote:
> > > Despite a couple of days of reading and
typing, I remain at a loss as to
> how
> > > to go about implementing SQLalchemy as a
model using reflection.  Does
> > > anyone have any complete examples of doing
so?  The quickwiki example is
> > > close, but there are so many posts about how
it should have been done
> that I
> > > fear that I lost the forest for the trees (or
perhaps vice-versa).
> >
> > I do have a complete example.  Did you read my
document?
> >
> >
> http://pylonshq.com/project/pylonshq/wiki/SqlAlche
myWithPylons
>
>
> Unfortunately, I did.
>
> I just got a working example running without any of the
config stuff
> working--the model has a hard-coded dsn in it right
now.  I can work
> backwards from that toward your example.

If you want a copy of my demo app, I'm happy to send you a
copy.

-jj

-- 
http://jjinux.blogspot.co
m/

--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the
Google Groups "pylons-discuss" group.
To post to this group, send email to pylons-discussgooglegroups.com
To unsubscribe from this group, send email to
pylons-discuss-unsubscribegooglegroups.com
For more options, visit this group at h
ttp://groups.google.com/group/pylons-discuss?hl=en
-~----------~----~----~----~------~----~------~--~---

I just came across this thread. I'm not sure if this will
help anyone,
but this is the implementation I came up with when I was
playing around
with Pylons + SQLAlchemy a couple of months ago. My
experience with
Pylons has been limited, and this hasn't been tested under
heavy load,
but it may help. This is a simplification of the system I
built to
bridge SQLAlchemy into Zope 3 apps. The Zope 3 bridge does a
little bit
more work, primarily involving tying into Zope 3's
transaction manager.

Note: This is with SQLAlchemy 0.2.8, I believe.

I have an application called 'pyldo', (pylons todo). In its
'lib'
directory I added a module, 'db.py', with the following
code::

#--- begin 'lib/db.py'
__docformat__ = 'restructuredtext'
from threading import local
import sqlalchemy

from pyldo.models.tables import meta

_STORAGE = local()
_engine = None

def engine(dburi=''):
    global _engine
    if _engine is None:
        if not dburi:
            from paste.deploy import CONFIG
            config = CONFIG['app_conf']
            dburi = config.get("sqlalchemy.dburi")

        if not dburi:
            raise KeyError("No SQLAlchemy config found:
'sqlalchemy.dburi'")

        _engine = sqlalchemy.create_engine(dburi)

    if not meta.is_bound():
        meta.connect(_engine)

    return _engine

def session():
    # TODO: transactions...?
    session = getattr(_STORAGE, 'session', None)
    if session:
        return session

    _STORAGE.session =
sqlalchemy.create_session(bind_to=engine())
    session = _STORAGE.session

    return session

def closeSession():
    if getattr(_STORAGE, 'session', None) is not None:
        _STORAGE.session.close()
    _STORAGE.session = None
#--- end 'lib/db.py'

_STORAGE is a thread local manager. The `engine()` function
gets a
`sqlalchemy.dburi` key from the main config file and then
lets
SQLAlchemy run wild with the engine it creates. In Zope 3,
the Alchemy
engine is stored in a thread local as well. I'm not sure why
I didn't
do that here...

In my Zope 3 implementation I also specify the path to the
application's SQLAlchemy 'meta' (a DynamicMetaData object)
object in
the configuration step, but in this pylons case I explicitly
import it
out of a module in my models package.

session() is what I use to do most everything. Again, the
Zope 3
version I have is richer, but the fundamentals are the same:
look for
the current session off of _STORAGE, and establish the
session if it
hasn't been created yet for this thread. `closeSession()`
closes it and
clears it.

My example application has only one model and controller, so
I didn't
go out of my way to set up a richer system than I'm about to
show. One
could easily abstract a lot of this out into an
'AlchemyController'
base, or use middleware or transactions or events to do some
of the
setup.

In 'controllers/todo.py'::

#--- begin 'controllers/todo.py'
from sqlalchemy import sql
from pyldo.lib.base import *
from pyldo.lib import db
from pyldo.models.todo import Todo, todoMapper

class TodoController(BaseController):

    def __before__(self):
        self.session = db.session()
        self.query = self.session.query(Todo)

    def __after__(self):
        db.closeSession()

    def _save(self, todo):
        self.session.save(todo)
        self.session.flush()

    # Main controller actions
    def index(self):
        return self.list()

    def list(self):
        """ Lists the todos... all of 'em
"""
        todos = self.query.select()
        c.complete = [ t for t in todos if t.done ]
        c.incomplete = [ t for t in todos if not t.done ]

        return render_response('/listing.myt')

    def complete(self, id):
        todo = self.query.get(int(id))
        todo.done = True
        self._save(todo)
        return redirect_to(action='list', id=None)

    def incomplete(self, id):
        todo = self.query.get(int(id))
        todo.done = False
        self._save(todo)
        return redirect_to(action='list', id=None)

    def add(self):
        description = request.params.get('description',
'').strip()
        if not description:
            raise ValueError("Description
required")

        todo = Todo()
        todo.description = description
        self._save(todo)
        return redirect_to(action='list', id=None)
#--- end 'controllers/todo.py'

So I just make sure I start or get the current Alchemy
session in
'before' and close it in 'after'.

Again, most of this is based off of my experiences on
another platform.
I feel fairly comfortable with it. There's no init_model
like in the
wiki - I use DynamicMetaData and have a 'tables' module to
which I can
refer with ease. In a large application I find this to be
very useful,
especially when one has to do complex queries that are
outside of the
scope of the modelled/mapped classes.

--
J.Shell


--~--~---------~--~----~------------~-------~--~----~
 You received this message because you are subscribed to the
Google Groups "pylons-discuss" group.
To post to this group, send email to pylons-discussgooglegroups.com
To unsubscribe from this group, send email to
pylons-discuss-unsubscribegooglegroups.com
For more options, visit this group at h
ttp://groups.google.com/group/pylons-discuss?hl=en
-~----------~----~----~----~------~----~------~--~---