1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

from twisted.internet import reactor, threads

from sqlalchemy import *

meta = MetaData()

def init(uri, strategy):
    engine = create_engine('sqlite://%s' % uri, strategy=strategy)
    meta.bind = engine
    reactor.suggestThreadPoolSize(1)

def _transact(meta):
    """
    return an high level function bind to a particular sqlalchemy engine

    this function is suitable to use as a decorator

    a decorated function returns a Deferred while its body
    is running in a separate thread
    """
    def _transaction(f):
        # _transaction *is* the decorator
        def _wrap(*args, **kwargs):
            def _1():
                conn = meta.engine.connect()
                session = create_session(bind=conn)
                trans = session.create_transaction()
                try:
                    r = f(session, *args, **kwargs)
                    session.flush()
                except:
                    trans.rollback()
                    conn.close()
                    raise
                else:
                    trans.commit()
                    conn.close()
                    return r
            return threads.deferToThread(_1)
        _wrap.__name__ = f.__name__
        return _wrap
    return _transaction

users = Table('users', meta,
    Column('email', String(50), unique=True, nullable=False),
    Column('username', String(50), primary_key=True, nullable=False),
    Column('password', String(32), nullable=False),
    Column('role', String(30), nullable=False, default=u'user'),
    Column('enabled', Boolean, nullable=False, default=False)  
)

class User(object):
    def __init__(self, email, username, password):
        self.email = email
        self.username = username
        self.password = password

    def __str__(self):
        return "%s(%s)" % (self.__class__.__name__, self.username)

mapper(User, users)

transact = _transact(meta)

@transact
def doSomething(session):
    return session.query(User).select()

def presult(results):
    for user in results:
        print user

@transact
def setup(session):
    meta.create_all()
    user = User('dialtone@myemail.ye', 'dialtone', 'test')
    user2 = User('mark@polluti.on', 'mark', 'test2')
    session.save(user)
    session.save(user2)

if __name__ == '__main__':
    init('/:memory:', 'threadlocal')
    setup().addCallback(lambda _: doSomething()
          ).addCallback(presult
          ).addCallback(lambda _: reactor.stop())
    reactor.run()