.. _qtut_databases:
|
|
==============================
|
19: Databases Using SQLAlchemy
|
==============================
|
|
Store and 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 an 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 wiki pages in the previous step.
|
|
.. note::
|
|
The Pyramid cookiecutter ``pyramid-cookiecutter-starter`` 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
|
|
cd ..; cp -r forms databases; cd databases
|
|
#. We need to add some dependencies in ``databases/setup.py`` as well as an :term:`entry point` for the command-line script:
|
|
.. literalinclude:: databases/setup.py
|
:linenos:
|
:emphasize-lines: 9-10, 12, 34-36
|
|
.. note:: We aren't yet doing ``$VENV/bin/pip install -e .`` because we need to write a script and update configuration first.
|
|
#. 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
|
:linenos:
|
|
#. Now that we've got all the pieces ready, and because we changed ``setup.py``, we now install all the goodies:
|
|
.. code-block:: bash
|
|
$VENV/bin/pip install -e .
|
|
#. 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/bin/initialize_tutorial_db development.ini
|
|
2016-04-16 13:01:33,055 INFO [sqlalchemy.engine.base.Engine][MainThread] SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
|
2016-04-16 13:01:33,055 INFO [sqlalchemy.engine.base.Engine][MainThread] ()
|
2016-04-16 13:01:33,056 INFO [sqlalchemy.engine.base.Engine][MainThread] SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
|
2016-04-16 13:01:33,056 INFO [sqlalchemy.engine.base.Engine][MainThread] ()
|
2016-04-16 13:01:33,057 INFO [sqlalchemy.engine.base.Engine][MainThread] PRAGMA table_info("wikipages")
|
2016-04-16 13:01:33,057 INFO [sqlalchemy.engine.base.Engine][MainThread] ()
|
2016-04-16 13:01:33,058 INFO [sqlalchemy.engine.base.Engine][MainThread]
|
CREATE TABLE wikipages (
|
uid INTEGER NOT NULL,
|
title TEXT,
|
body TEXT,
|
PRIMARY KEY (uid),
|
UNIQUE (title)
|
)
|
|
|
2016-04-16 13:01:33,058 INFO [sqlalchemy.engine.base.Engine][MainThread] ()
|
2016-04-16 13:01:33,059 INFO [sqlalchemy.engine.base.Engine][MainThread] COMMIT
|
2016-04-16 13:01:33,062 INFO [sqlalchemy.engine.base.Engine][MainThread] BEGIN (implicit)
|
2016-04-16 13:01:33,062 INFO [sqlalchemy.engine.base.Engine][MainThread] INSERT INTO wikipages (title, body) VALUES (?, ?)
|
2016-04-16 13:01:33,063 INFO [sqlalchemy.engine.base.Engine][MainThread] ('Root', '<p>Root</p>')
|
2016-04-16 13:01:33,063 INFO [sqlalchemy.engine.base.Engine][MainThread] COMMIT
|
|
#. With our data now driven by SQLAlchemy queries, we need to update our ``databases/tutorial/views.py``:
|
|
.. literalinclude:: databases/tutorial/views.py
|
:linenos:
|
|
#. 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 ``pytest``:
|
|
.. code-block:: bash
|
|
$VENV/bin/pytest tutorial/tests.py -q
|
..
|
2 passed in 1.41 seconds
|
|
#. Run your Pyramid application with:
|
|
.. code-block:: bash
|
|
$VENV/bin/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 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 virtual environment'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 of 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 two lines and have magic ensue?
|
|
#. Give a try at a button that deletes a wiki page.
|
