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
|
==============================
19: Databases Using SQLAlchemy
==============================
Store/retrieve data using the SQLAlchemy ORM atop the SQLite database.
Background
==========
Our Pyramid-based wiki application now needs database-backed storage of
pages. This frequently means a SQL database. The Pyramid community
strongly supports the
:ref:`SQLAlchemy <sqla:index_toplevel>` project and its
:ref:`object-relational mapper (ORM) <sqla:ormtutorial_toplevel>`
as a convenient, Pythonic way to interface to databases.
In this step we hook up SQLAlchemy to a SQLite database table,
providing storage and retrieval for the wikipages in the previous step.
.. note::
The ``alchemy`` scaffold is really helpful for getting a
SQLAlchemy project going, including generation of the console
script. Since we want to see all the decisions, we will forgo
convenience in this tutorial and wire it up ourselves.
Objectives
==========
- Store pages in SQLite by using SQLAlchemy models
- Use SQLAlchemy queries to list/add/view/edit pages
- Provide a database-initialize command by writing a Pyramid *console
script* which can be run from the command line
Steps
=====
#. We are going to use the forms step as our starting point:
.. code-block:: bash
(venv)$ cd ..; cp -r forms databases; cd databases
#. We need to add some dependencies in ``databases/setup.py`` as well
as an "entry point" for the command-line script:
.. literalinclude:: databases/setup.py
:linenos:
.. note::
We aren't yet doing ``python3.3 setup.py develop`` as we
are changing it later.
#. Our configuration file at ``databases/development.ini`` wires
together some new pieces:
.. literalinclude:: databases/development.ini
:language: ini
#. This engine configuration now needs to be read into the application
through changes in ``databases/tutorial/__init__.py``:
.. literalinclude:: databases/tutorial/__init__.py
:linenos:
#. Make a command-line script at ``databases/tutorial/initialize_db.py``
to initialize the database:
.. literalinclude:: databases/tutorial/initialize_db.py
#. Since ``setup.py`` changed, we now run it:
.. code-block:: bash
(venv)$ python setup.py develop
#. The script references some models in ``databases/tutorial/models.py``:
.. literalinclude:: databases/tutorial/models.py
:linenos:
#. Let's run this console script, thus producing our database and table:
.. code-block:: bash
(venv)$ initialize_tutorial_db development.ini
2013-09-06 15:54:08,050 INFO [sqlalchemy.engine.base.Engine][MainThread] PRAGMA table_info("wikipages")
2013-09-06 15:54:08,050 INFO [sqlalchemy.engine.base.Engine][MainThread] ()
2013-09-06 15:54:08,051 INFO [sqlalchemy.engine.base.Engine][MainThread]
CREATE TABLE wikipages (
uid INTEGER NOT NULL,
title TEXT,
body TEXT,
PRIMARY KEY (uid),
UNIQUE (title)
)
#. With our data now driven by SQLAlchemy queries, we need to update
our ``databases/tutorial/views.py``:
.. literalinclude:: databases/tutorial/views.py
#. Our tests in ``databases/tutorial/tests.py`` changed to include
SQLAlchemy bootstrapping:
.. literalinclude:: databases/tutorial/tests.py
:linenos:
#. Run the tests in your package using ``nose``:
.. code-block:: bash
(venv)$ nosetests .
..
-----------------------------------------------------------------
Ran 2 tests in 1.141s
OK
#. Run your Pyramid application with:
.. code-block:: bash
(venv)$ pserve development.ini --reload
#. Open http://localhost:6543/ in a browser.
Analysis
========
Let's start with the dependencies. We made the decision to use
``SQLAlchemy`` to talk to our database. We also, though, installed
``pyramid_tm`` and ``zope.sqlalchemy``. Why?
Pyramid has a strong orientation towards support for ``transactions``.
Specifically, you can install a transaction manager into your app
application, either as middleware or a Pyramid "tween". Then,
just before you return the response, all transaction-aware parts of
your application are executed.
This means Pyramid view code usually doesn't manage transactions. If
your view code or a template generates an error, the transaction manager
aborts the transaction. This is a very liberating way to write code.
The ``pyramid_tm`` package provides a "tween" that is configured in the
``development.ini`` configuration file. That installs it. We then need
a package that makes SQLAlchemy and thus the RDBMS transaction manager
integrate with the Pyramid transaction manager. That's what
``zope.sqlalchemy`` does.
Where do we point at the location on disk for the SQLite file? In the
configuration file. This lets consumers of our package change the
location in a safe (non-code) way. That is, in configuration. This
configuration-oriented approach isn't required in Pyramid; you can
still make such statements in your ``__init__.py`` or some companion
module.
The ``initialize_tutorial_db`` is a nice example of framework support.
You point your setup at the location of some ``[console_scripts]`` and
these get generated into your virtualenv's ``bin`` directory. Our
console script follows the pattern of being fed a configuration file
with all the bootstrapping. It then opens SQLAlchemy and creates the
root of the wiki, which also makes the SQLite file. Note the
``with transaction.manager`` part that puts the work in the scope of a
transaction (as we aren't inside a web request where this is done
automatically.)
The ``models.py`` does a little bit extra work to hook up SQLAlchemy
into the Pyramid transaction manager. It then declares the model for a
``Page``.
Our views have changes primarily around replacing our dummy
dictionary-of-dictionaries data with proper database support: list the
rows, add a row, edit a row, and delete a row.
Extra Credit
============
#. Why all this code? Why can't I just type 2 lines and have magic ensue?
#. Give a try at a button that deletes a wiki page.
|
