commit | author | age
|
b731b5
|
1 |
.. _qtut_databases: |
PE |
2 |
|
b1b922
|
3 |
============================== |
PE |
4 |
19: Databases Using SQLAlchemy |
|
5 |
============================== |
|
6 |
|
8bca48
|
7 |
Store and retrieve data using the SQLAlchemy ORM atop the SQLite database. |
SP |
8 |
|
b1b922
|
9 |
|
PE |
10 |
Background |
|
11 |
========== |
|
12 |
|
8bca48
|
13 |
Our Pyramid-based wiki application now needs database-backed storage of pages. |
SP |
14 |
This frequently means an SQL database. The Pyramid community strongly supports |
|
15 |
the :ref:`SQLAlchemy <sqla:index_toplevel>` project and its |
|
16 |
:ref:`object-relational mapper (ORM) <sqla:ormtutorial_toplevel>` as a |
|
17 |
convenient, Pythonic way to interface to databases. |
b1b922
|
18 |
|
8bca48
|
19 |
In this step we hook up SQLAlchemy to a SQLite database table, providing |
SP |
20 |
storage and retrieval for the wiki pages in the previous step. |
b1b922
|
21 |
|
PE |
22 |
.. note:: |
|
23 |
|
28e688
|
24 |
The Pyramid cookiecutter ``pyramid-cookiecutter-starter`` is really |
f2520e
|
25 |
helpful for getting a SQLAlchemy project going, including generation of |
SM |
26 |
the console script. Since we want to see all the decisions, we will forgo |
|
27 |
convenience in this tutorial, and wire it up ourselves. |
8bca48
|
28 |
|
b1b922
|
29 |
|
PE |
30 |
Objectives |
|
31 |
========== |
|
32 |
|
8bca48
|
33 |
- Store pages in SQLite by using SQLAlchemy models. |
b1b922
|
34 |
|
8bca48
|
35 |
- Use SQLAlchemy queries to list/add/view/edit pages. |
b1b922
|
36 |
|
8bca48
|
37 |
- Provide a database-initialize command by writing a Pyramid *console script* |
SP |
38 |
which can be run from the command line. |
|
39 |
|
b1b922
|
40 |
|
PE |
41 |
Steps |
|
42 |
===== |
|
43 |
|
57d49f
|
44 |
#. We are going to use the forms step as our starting point: |
b1b922
|
45 |
|
57d49f
|
46 |
.. code-block:: bash |
b1b922
|
47 |
|
57d49f
|
48 |
cd ..; cp -r forms databases; cd databases |
b1b922
|
49 |
|
57d49f
|
50 |
#. We need to add some dependencies in ``databases/setup.py`` as well as an :term:`entry point` for the command-line script: |
b1b922
|
51 |
|
57d49f
|
52 |
.. literalinclude:: databases/setup.py |
SP |
53 |
:linenos: |
e73977
|
54 |
:emphasize-lines: 10-11, 13, 34-36 |
b1b922
|
55 |
|
57d49f
|
56 |
.. note:: We aren't yet doing ``$VENV/bin/pip install -e .`` because we need to write a script and update configuration first. |
b1b922
|
57 |
|
57d49f
|
58 |
#. Our configuration file at ``databases/development.ini`` wires together some new pieces: |
b1b922
|
59 |
|
57d49f
|
60 |
.. literalinclude:: databases/development.ini |
SP |
61 |
:language: ini |
b1b922
|
62 |
|
57d49f
|
63 |
#. This engine configuration now needs to be read into the application through changes in ``databases/tutorial/__init__.py``: |
b1b922
|
64 |
|
57d49f
|
65 |
.. literalinclude:: databases/tutorial/__init__.py |
SP |
66 |
:linenos: |
b1b922
|
67 |
|
57d49f
|
68 |
#. Make a command-line script at ``databases/tutorial/initialize_db.py`` to initialize the database: |
b1b922
|
69 |
|
57d49f
|
70 |
.. literalinclude:: databases/tutorial/initialize_db.py |
SP |
71 |
:linenos: |
b1b922
|
72 |
|
57d49f
|
73 |
#. Now that we've got all the pieces ready, and because we changed ``setup.py``, we now install all the goodies: |
b1b922
|
74 |
|
57d49f
|
75 |
.. code-block:: bash |
b1b922
|
76 |
|
57d49f
|
77 |
$VENV/bin/pip install -e . |
b1b922
|
78 |
|
57d49f
|
79 |
#. The script references some models in ``databases/tutorial/models.py``: |
b1b922
|
80 |
|
57d49f
|
81 |
.. literalinclude:: databases/tutorial/models.py |
SP |
82 |
:linenos: |
b1b922
|
83 |
|
57d49f
|
84 |
#. Let's run this console script, thus producing our database and table: |
b1b922
|
85 |
|
57d49f
|
86 |
.. code-block:: bash |
b1b922
|
87 |
|
57d49f
|
88 |
$VENV/bin/initialize_tutorial_db development.ini |
b1b922
|
89 |
|
57d49f
|
90 |
2016-04-16 13:01:33,055 INFO [sqlalchemy.engine.base.Engine][MainThread] SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1 |
SP |
91 |
2016-04-16 13:01:33,055 INFO [sqlalchemy.engine.base.Engine][MainThread] () |
|
92 |
2016-04-16 13:01:33,056 INFO [sqlalchemy.engine.base.Engine][MainThread] SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1 |
|
93 |
2016-04-16 13:01:33,056 INFO [sqlalchemy.engine.base.Engine][MainThread] () |
|
94 |
2016-04-16 13:01:33,057 INFO [sqlalchemy.engine.base.Engine][MainThread] PRAGMA table_info("wikipages") |
|
95 |
2016-04-16 13:01:33,057 INFO [sqlalchemy.engine.base.Engine][MainThread] () |
|
96 |
2016-04-16 13:01:33,058 INFO [sqlalchemy.engine.base.Engine][MainThread] |
|
97 |
CREATE TABLE wikipages ( |
9be675
|
98 |
uid INTEGER NOT NULL, |
SP |
99 |
title TEXT, |
|
100 |
body TEXT, |
|
101 |
PRIMARY KEY (uid), |
|
102 |
UNIQUE (title) |
57d49f
|
103 |
) |
b1b922
|
104 |
|
608f95
|
105 |
|
57d49f
|
106 |
2016-04-16 13:01:33,058 INFO [sqlalchemy.engine.base.Engine][MainThread] () |
SP |
107 |
2016-04-16 13:01:33,059 INFO [sqlalchemy.engine.base.Engine][MainThread] COMMIT |
|
108 |
2016-04-16 13:01:33,062 INFO [sqlalchemy.engine.base.Engine][MainThread] BEGIN (implicit) |
|
109 |
2016-04-16 13:01:33,062 INFO [sqlalchemy.engine.base.Engine][MainThread] INSERT INTO wikipages (title, body) VALUES (?, ?) |
|
110 |
2016-04-16 13:01:33,063 INFO [sqlalchemy.engine.base.Engine][MainThread] ('Root', '<p>Root</p>') |
|
111 |
2016-04-16 13:01:33,063 INFO [sqlalchemy.engine.base.Engine][MainThread] COMMIT |
608f95
|
112 |
|
57d49f
|
113 |
#. With our data now driven by SQLAlchemy queries, we need to update our ``databases/tutorial/views.py``: |
b1b922
|
114 |
|
57d49f
|
115 |
.. literalinclude:: databases/tutorial/views.py |
SP |
116 |
:linenos: |
b1b922
|
117 |
|
57d49f
|
118 |
#. Our tests in ``databases/tutorial/tests.py`` changed to include SQLAlchemy bootstrapping: |
b1b922
|
119 |
|
57d49f
|
120 |
.. literalinclude:: databases/tutorial/tests.py |
SP |
121 |
:linenos: |
b1b922
|
122 |
|
57d49f
|
123 |
#. Run the tests in your package using ``pytest``: |
b1b922
|
124 |
|
57d49f
|
125 |
.. code-block:: bash |
b1b922
|
126 |
|
57d49f
|
127 |
$VENV/bin/pytest tutorial/tests.py -q |
SP |
128 |
.. |
|
129 |
2 passed in 1.41 seconds |
b1b922
|
130 |
|
57d49f
|
131 |
#. Run your Pyramid application with: |
b1b922
|
132 |
|
57d49f
|
133 |
.. code-block:: bash |
b1b922
|
134 |
|
57d49f
|
135 |
$VENV/bin/pserve development.ini --reload |
b1b922
|
136 |
|
57d49f
|
137 |
#. Open http://localhost:6543/ in a browser. |
b1b922
|
138 |
|
8bca48
|
139 |
|
b1b922
|
140 |
Analysis |
PE |
141 |
======== |
|
142 |
|
8bca48
|
143 |
Let's start with the dependencies. We made the decision to use ``SQLAlchemy`` |
SP |
144 |
to talk to our database. We also, though, installed ``pyramid_tm`` and |
|
145 |
``zope.sqlalchemy``. Why? |
b1b922
|
146 |
|
PE |
147 |
Pyramid has a strong orientation towards support for ``transactions``. |
8bca48
|
148 |
Specifically, you can install a transaction manager into your application |
SP |
149 |
either as middleware or a Pyramid "tween". Then, just before you return the |
|
150 |
response, all transaction-aware parts of your application are executed. |
b1b922
|
151 |
|
8bca48
|
152 |
This means Pyramid view code usually doesn't manage transactions. If your view |
SP |
153 |
code or a template generates an error, the transaction manager aborts the |
|
154 |
transaction. This is a very liberating way to write code. |
b1b922
|
155 |
|
PE |
156 |
The ``pyramid_tm`` package provides a "tween" that is configured in the |
8bca48
|
157 |
``development.ini`` configuration file. That installs it. We then need a |
SP |
158 |
package that makes SQLAlchemy, and thus the RDBMS transaction manager, |
|
159 |
integrate with the Pyramid transaction manager. That's what ``zope.sqlalchemy`` |
|
160 |
does. |
b1b922
|
161 |
|
PE |
162 |
Where do we point at the location on disk for the SQLite file? In the |
8bca48
|
163 |
configuration file. This lets consumers of our package change the location in a |
SP |
164 |
safe (non-code) way. That is, in configuration. This configuration-oriented |
|
165 |
approach isn't required in Pyramid; you can still make such statements in your |
|
166 |
``__init__.py`` or some companion module. |
b1b922
|
167 |
|
8bca48
|
168 |
The ``initialize_tutorial_db`` is a nice example of framework support. You |
SP |
169 |
point your setup at the location of some ``[console_scripts]``, and these get |
|
170 |
generated into your virtual environment's ``bin`` directory. Our console script |
|
171 |
follows the pattern of being fed a configuration file with all the |
|
172 |
bootstrapping. It then opens SQLAlchemy and creates the root of the wiki, which |
|
173 |
also makes the SQLite file. Note the ``with transaction.manager`` part that |
|
174 |
puts the work in the scope of a transaction, as we aren't inside a web request |
|
175 |
where this is done automatically. |
b1b922
|
176 |
|
8bca48
|
177 |
The ``models.py`` does a little bit of extra work to hook up SQLAlchemy into |
SP |
178 |
the Pyramid transaction manager. It then declares the model for a ``Page``. |
b1b922
|
179 |
|
PE |
180 |
Our views have changes primarily around replacing our dummy |
8bca48
|
181 |
dictionary-of-dictionaries data with proper database support: list the rows, |
SP |
182 |
add a row, edit a row, and delete a row. |
b1b922
|
183 |
|
8bca48
|
184 |
|
SP |
185 |
Extra credit |
b1b922
|
186 |
============ |
PE |
187 |
|
8bca48
|
188 |
#. Why all this code? Why can't I just type two lines and have magic ensue? |
b1b922
|
189 |
|
PE |
190 |
#. Give a try at a button that deletes a wiki page. |