Proper use of sqlalchemy scoped_session and pyramid's models

Question

I'm having problem with finding a proper way of using db queries. I don't have much experience with web development, it's like 6 months of working afterhours (so it's like max 10h a week). I choose Pyramid because of Py3k support mainly. It doesn't have that much community support as django, nor the tutorials, so I might be doing things wrong. So...

I'm using Pyramid(1.4) and SQLAlchemy (0.8). I've created project just as described in Pyramid's tutorial.

I've already read this excellent post (https://stackoverflow.com/a/11547942/1498245) about using scoped_session.

But.. I've been struggling with proper (if such exist) approach to DB objects handling.

So here's what I begun with:

## Project layout
myproject
├── models
│   ├── somemodel1.py
│   ├── somemodel2.py
│   ├── __init__.py
│   ├── meta.py
│   └── somemodel3.py
├── schemas
│   ├── some_colander_schema1.py
│   ├── some_colander_schema2.py
│   └── some_colander_schema3.py
├── scripts
│   ├── __init__.py
│   └── initializedb.py
├── static
│   ├── bootstrap
│   ├── favicon.ico
│   ├── jquery-1.9.1.min.js
│   └── transparent.gif
├── templates
│   ├── some_chameleon_template.pt
│   ├── some_chameleon_template1.pt
│   ├── some_chameleon_template2.pt
│   ├── forbidden_view.pt
│   ├── global_layout_noauth.pt
│   └── global_layout.pt
├── views
│   ├── someviewclass1.py
│   ├── someviewclass2.py
│   └── someviewclass3.py
└── __init__.py


# models/meta.py PRE @contextmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker

DBSession   = scoped_session(sessionmaker())
Base        = declarative_base()

Now to the point.

In my views I was using this construct:

class SomeView1(Layout): #in Layout class request is saved to self.request
    @view_config(...)
    def list(self):
        DBSession()
        try:
            somethings = DBSession.query(SomeModel).options(eagerload(...)).filter(...).all()
        except: #it's bad to catch everything I know, but had to close session properly
            somethings = []
            DBSession.rollback()
        DBSession.remove()

        ... #do something more

        DBSession()
        try:
            somethingMores = DBSession.query(SomeModel2).options(eagerload(...)).filter(...).all()
        except: #it's bad to catch everything I know, but had to close session properly
            somethingMores = []
            DBSession.rollback()
        DBSession.remove()

        return {"values" : somethings,
                "others" : somethingMores}

    @view_config(...)
    def edit(self):
        # here's recently implemented "update"
        DBSession()
        try:
            somethings = DBSession.query(SomeModel1)...all()
            somethingsMore = DBSession.query(SomeModel2)...all()
        finally:
            DBSession.remove()
        return {"somethings":somethings,
                "somethingsMore": somethingsMore}

But then I realized I've got bunches of such queries.. so I moved them into Model.

In my view I just had to call:

somethings = SomeModel1.all()
#or
something = SomeModel.by_id(some_id)

and in that SomeModel1 I had for example:

class SomeModel(Base):
    __tablename__ = "somemodel"
    idsomeModel = Column(Integer, primary_key=True)

    @classmethod
    def all(cls):
        DBSession()
        try:
            retval = ...
        except:
            retval = None
        finally:
            DBSession.remove()
        return retval

It's all great (not really, but wait..) but it get's nasty when I have to for example put sort order or where clause. Then my def all(cls) turned into def all(cls, **kwargs) and lots of ifs, like:

query = DBSession.query(SomeModel)
if "forPeriod" in kwargs:
    query = query.filter(SomeModel.date > kwargs["forPeriod"])
if "sortOrder" in kwargs:
    query = query.order_by(kwargs["sort"])

retval = query.all()

Is it ok? or should I write queries in my views? I guess there's no "good way", but I feel like this approach is more maintainable.

And it's not good. Now I've read on sqlalchemy site, that it's far more better to pass session to model. In this way in my view I'd use it like:

@view_config(...)
def some_function(self):
    with get_db_session as session:
        somethings = SomeModel.all(session, some_arguments)

that function get_db_session is from new models/meta.py:

# models/meta.py
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
from contextlib import contextmanager

DBSession   = scoped_session(sessionmaker())
Base        = declarative_base()

@contextmanager
def get_scoped_session():
    try:
        yield DBSession()
    finally:
        DBSession.remove()

It's a hell of a post, but I want someone to tell me "Ok, you're going in good direction" or "No, f*ck man, what are you doing?"

So to sum everything up:

1. Is hiding queries in models a good idea? (sometimes with lots of ifs on kwargs; and obscuring exceptions - if such happens my return value is None, cause in view I don't care what was the reason, I got it logged (in model))

2. Is such use of scoped_session with @contextmanager and with statement a good approach?

3. No, there's no 3rd question.. so much text just to ask two questions :/

Answer 1

for #1:

I would use the scoped_session.query_property() for queries, and not hide them that much, setup your Base as follows:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker

DBSession = scoped_session(
    sessionmaker(
        extension='ZopeTransactionExtension'  # for your 2nd question
    )
)

class ORMClass(object):
    query = DBSession.query_property()

Base = declarative_base(cls=ORMClass)

and derive your models from this Base class as usual:

class MyModel(Base):
    ...

now you will be able to:

MyModel.query.filter_by(attr1='something').all()

for #2: I agree with umeboshi, use a transaction manager

Answer 2

As far as #2 is concerned, I use transaction

with transaction.manager:
    p = Page()
    p.title = "Page Title"
    p.content = "Page Content"
    dbsession.add(p)
p = dbsession.merge(p)