| 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: |
| beeb8e | 54 | :emphasize-lines: 9-10, 12, 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. |
