pecan model with sqlalchemy

By | 2016/11/30

pecan model with sqlalchemy

官方文档:Working with Databases, Transactions, and ORM’s

  • 第一步就是要修改init_model方法


The purpose of this method is to determine bindings from your configuration file and create necessary engines, pools, etc. according to your ORM or database toolkit of choice.

from pecan import conf
from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import scoped_session, sessionmaker

Session = scoped_session(sessionmaker())
metadata = MetaData()

def _engine_from_config(configuration):
    configuration = dict(configuration)
    url = configuration.pop('url')
    return create_engine(url, **configuration)

def init_model():
    conf.sqlalchemy.engine = _engine_from_config(conf.sqlalchemy)

def start():
    Session.bind = conf.sqlalchemy.engine
    metadata.bind = Session.bind

def start_read_only():
    Session.bind = conf.sqlalchemy.engine
    metadata.bind = Session.bind

def commit():

def rollback():

def clear():

  • 第二步就是修改app.py文件,加上hooks
from pecan import make_app
from pecan.hooks import TransactionHook
from pecan_model2 import model

def setup_app(config):

    app_conf = dict(

    return make_app(
        logging=getattr(config, 'logging', {}),

on HTTP POST, PUT, and DELETE requests, TransactionHook takes care of the transaction automatically by following these rules

  1. Before controller routing has been determined, model.start() is called. This function should bind to the appropriate SQLAlchemy engine and start a transaction.
  2. Controller code is run and returns.
  3. If your controller or template rendering fails and raises an exception, model.rollback() is called and the original exception is re-raised. This allows you to rollback your database transaction to avoid committing work when exceptions occur in your application code.
  4. If the controller returns successfully, model.commit() and model.clear() are called.

On idempotent operations (like HTTP GET and HEAD requests), TransactionHook handles transactions following different rules.

  1. model.start_read_only() is called. This function should bind to your SQLAlchemy engine.
  2. Controller code is run and returns.
  3. If the controller returns successfully, model.clear() is called.
  • 然后我在model/user.py里面创建了一个简单的User
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext import declarative

Base = declarative.declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False)
    password = Column(String(64), nullable=False)
    email = Column(String(64))

    def __json__(self):
        return dict(

  • 最后就可以在controller里面调用
import pecan
from pecan import conf
from pecan import expose, redirect
from webob.exc import status_map

from pecan_model2.model import Session, MetaData
from pecan_model2.model.user import User

class RootController(object):
    @expose(generic=True, template='json')
    def index(self): 
        query = Session.query(User)
        users = query.all()
        names = [ for user in users]
        return {"users": users}

    def index_post(self):
        username = pecan.request.POST.get('username')
        password = pecan.request.POST.get('password')
        email = pecan.request.POST.get('email')

        user = User() = username
        user.password = password
        if email:
   = email
        return {"message": "OKKKKK"}
  • 这样就可以在页面上调用请求
  "users": [


Leave a Reply

Your email address will not be published. Required fields are marked *