Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Newer
Older
100644 106 lines (74 sloc) 2.571 kb
b704923 @ohsc edit
ohsc authored
1 ---
2 layout: default
3 title: sqlalchemy
4 ---
5
6 # sqlalchemy
7
d7497a6 edit
David Xie authored
8 ## 问题
b704923 @ohsc edit
ohsc authored
9
d7497a6 edit
David Xie authored
10 如何在web.py中使用sqlalchemy
b704923 @ohsc edit
ohsc authored
11
d7497a6 edit
David Xie authored
12 ## 方案
b704923 @ohsc edit
ohsc authored
13
d7497a6 edit
David Xie authored
14 创建一个钩子并使用sqlalchemy的scoped session
15 (http://www.sqlalchemy.org/docs/05/session.html#unitofwork_contextual)
b704923 @ohsc edit
ohsc authored
16
17
18 import string
19 import random
20 import web
21
22 from sqlalchemy.orm import scoped_session, sessionmaker
23 from models import *
24
25 urls = (
26 "/", "add",
27 "/view", "view"
28 )
29
30 def load_sqla(handler):
31 web.ctx.orm = scoped_session(sessionmaker(bind=engine))
32 try:
33 return handler()
34 except web.HTTPError:
35 web.ctx.orm.commit()
36 raise
37 except:
38 web.ctx.orm.rollback()
39 raise
a5135c7 edit
David Xie authored
40 finally:
b704923 @ohsc edit
ohsc authored
41 web.ctx.orm.commit()
42
43
44 app = web.application(urls, locals())
45 app.add_processor(load_sqla)
46
47
48 class add:
49 def GET(self):
50 web.header('Content-type', 'text/html')
51 fname = "".join(random.choice(string.letters) for i in range(4))
52 lname = "".join(random.choice(string.letters) for i in range(7))
53 u = User(name=fname
54 ,fullname=fname + ' ' + lname
55 ,password =542)
56 web.ctx.orm.add(u)
57 return "added:" + web.websafe(str(u)) \
58 + "<br/>" \
59 + '<a href="/view">view all</a>'
60
61 class view:
62 def GET(self):
63 web.header('Content-type', 'text/plain')
64 return "\n".join(map(str, web.ctx.orm.query(User).all()))
65
66
67 if __name__ == "__main__":
68 app.run()
69
70
71 ###models.py
72
73 from sqlalchemy import create_engine
74 from sqlalchemy import Column, Integer, String
75
76 engine = create_engine('sqlite:///mydatabase.db', echo=True)
77
78 from sqlalchemy.ext.declarative import declarative_base
79
80 Base = declarative_base()
81 class User(Base):
82 __tablename__ = 'users'
83
84 id = Column(Integer, primary_key=True)
85 name = Column(String)
86 fullname = Column(String)
87 password = Column(String)
88
89 def __init__(self, name, fullname, password):
90 self.name = name
91 self.fullname = fullname
92 self.password = password
93
94 def __repr__(self):
95 return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)
96
97
98 users_table = User.__table__
99 metadata = Base.metadata
100
101
102 if __name__ == "__main__":
103 metadata.create_all(engine)
104
105
d7497a6 edit
David Xie authored
106 在跑程序之前,运行'python models.py'来初始化一次数据库.
Something went wrong with that request. Please try again.