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