.. _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:orm_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.Engine][MainThread] SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
        2016-04-16 13:01:33,055 INFO  [sqlalchemy.engine.Engine][MainThread] ()
        2016-04-16 13:01:33,056 INFO  [sqlalchemy.engine.Engine][MainThread] SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
        2016-04-16 13:01:33,056 INFO  [sqlalchemy.engine.Engine][MainThread] ()
        2016-04-16 13:01:33,057 INFO  [sqlalchemy.engine.Engine][MainThread] PRAGMA table_info("wikipages")
        2016-04-16 13:01:33,057 INFO  [sqlalchemy.engine.Engine][MainThread] ()
        2016-04-16 13:01:33,058 INFO  [sqlalchemy.engine.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.Engine][MainThread] ()
        2016-04-16 13:01:33,059 INFO  [sqlalchemy.engine.Engine][MainThread] COMMIT
        2016-04-16 13:01:33,062 INFO  [sqlalchemy.engine.Engine][MainThread] BEGIN (implicit)
        2016-04-16 13:01:33,062 INFO  [sqlalchemy.engine.Engine][MainThread] INSERT INTO wikipages (title, body) VALUES (?, ?)
        2016-04-16 13:01:33,063 INFO  [sqlalchemy.engine.Engine][MainThread] ('Root', '<p>Root</p>')
        2016-04-16 13:01:33,063 INFO  [sqlalchemy.engine.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.