Merge remote-tracking branch 'refs/remotes/Pylons/master'

1 files changed, 198 insertions, 0 deletions

diff --git a/docs/quick_tutorial/databases.rst b/docs/quick_tutorial/databases.rst
new file mode 100644
index 000000000..c8d87c180
--- /dev/null
+++ b/docs/quick_tutorial/databases.rst
@@ -0,0 +1,198 @@
+.. _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 ``alchemy`` scaffold is really helpful for getting an 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
+   "entry point" for the command-line script:
+
+   .. literalinclude:: databases/setup.py
+    :linenos:
+
+   .. note::
+
+     We aren't yet doing ``$VENV/bin/pip install -e .`` as we will change 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
+    :linenos:
+
+#. Since ``setup.py`` changed, we now run it:
+
+   .. 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 ``py.test``:
+
+   .. code-block:: bash
+
+    $ $VENV/bin/py.test 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.