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
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
|
.. _databases:
Working with Databases, Transactions, and ORM's
===============================================
Pecan provides no opinionated support for working with databases, but
it's easy to hook into your ORM of choice. This article details best
practices for integrating the popular Python ORM, SQLAlchemy_, into
your Pecan project.
.. _SQLAlchemy: http://sqlalchemy.org
``init_model`` and Preparing Your Model
---------------------------------------
Pecan's default quickstart project includes an empty stub directory
for implementing your model as you see fit.
::
.
└── test_project
├── app.py
├── __init__.py
├── controllers
├── model
│ ├── __init__.py
└── templates
By default, this module contains a special method, :func:`init_model`.
::
from pecan import conf
def init_model():
"""
This is a stub method which is called at application startup time.
If you need to bind to a parse database configuration, set up tables or
ORM classes, or perform any database initialization, this is the
recommended place to do it.
For more information working with databases, and some common recipes,
see http://pecan.readthedocs.org/en/latest/databases.html
"""
pass
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.
Additionally, your project's :py:mod:`model` module can be used to define
functions for common binding operations, such as starting
transactions, committing or rolling back work, and clearing a session.
This is also the location in your project where object and relation
definitions should be defined. Here's what a sample Pecan
configuration file with database bindings might look like.
::
# Server Specific Configurations
server = {
...
}
# Pecan Application Configurations
app = {
...
}
# Bindings and options to pass to SQLAlchemy's ``create_engine``
sqlalchemy = {
'url' : 'mysql://root:@localhost/dbname?charset=utf8&use_unicode=0',
'echo' : False,
'echo_pool' : False,
'pool_recycle' : 3600,
'encoding' : 'utf-8'
}
And a basic model implementation that can be used to configure and
bind using SQLAlchemy.
::
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 commit():
Session.commit()
def rollback():
Session.rollback()
def clear():
Session.remove()
Binding Within the Application
------------------------------
There are several approaches to wrapping your application's requests
with calls to appropriate model function calls. One approach is WSGI
middleware. We also recommend Pecan :ref:`hooks`. Pecan comes with
:class:`TransactionHook`, a hook which can be used to wrap requests in
database transactions for you. To use it, simply include it in your
project's ``app.py`` file and pass it a set of functions related to
database binding.
::
from pecan import conf, make_app
from pecan.hooks import TransactionHook
from test_project import model
app = make_app(
conf.app.root,
static_root = conf.app.static_root,
template_path = conf.app.template_path,
debug = conf.app.debug,
hooks = [
TransactionHook(
model.start,
model.start_read_only,
model.commit,
model.rollback,
model.clear
)
]
)
In the above example, on HTTP ``POST``, ``PUT``, and ``DELETE``
requests, :class:`TransactionHook` takes care of the transaction
automatically by following these rules:
#. Before controller routing has been determined, :func:`model.start`
is called. This function should bind to the appropriate
SQLAlchemy engine and start a transaction.
#. Controller code is run and returns.
#. If your controller or template rendering fails and raises an
exception, :func:`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.
#. If the controller returns successfully, :func:`model.commit` and
:func:`model.clear` are called.
On idempotent operations (like HTTP ``GET`` and ``HEAD`` requests),
:class:`TransactionHook` handles transactions following different
rules.
#. ``model.start_read_only()`` is called. This function should bind
to your SQLAlchemy engine.
#. Controller code is run and returns.
#. If the controller returns successfully, ``model.clear()`` is
called.
Also note that there is a useful :func:`@after_commit` decorator provided
in :ref:`pecan_decorators`.
Splitting Reads and Writes
--------------------------
Employing the strategy above with :class:`TransactionHook` makes it very
simple to split database reads and writes based upon HTTP methods
(i.e., GET/HEAD requests are read-only and would potentially be routed
to a read-only database slave, while POST/PUT/DELETE requests require
writing, and would always bind to a master database with read/write
privileges). It's also possible to extend :class:`TransactionHook` or
write your own hook implementation for more refined control over where
and when database bindings are called.
|