diff options
Diffstat (limited to 'docs/quick_tutorial/databases.rst')
| -rw-r--r-- | docs/quick_tutorial/databases.rst | 224 |
1 files changed, 114 insertions, 110 deletions
diff --git a/docs/quick_tutorial/databases.rst b/docs/quick_tutorial/databases.rst index 20b3cd46d..db75d70ce 100644 --- a/docs/quick_tutorial/databases.rst +++ b/docs/quick_tutorial/databases.rst @@ -4,183 +4,187 @@ 19: Databases Using SQLAlchemy ============================== -Store/retrieve data using the SQLAlchemy ORM atop the SQLite database. +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 a 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. +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 wikipages in the previous step. +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 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. + 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 +- Store pages in SQLite by using SQLAlchemy models. + +- Use SQLAlchemy queries to list/add/view/edit pages. -- 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. -- 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: +#. We are going to use the forms step as our starting point: + + .. code-block:: bash + + cd ..; cp -r forms databases; cd databases - .. code-block:: bash +#. We need to add some dependencies in ``databases/setup.py`` as well as an :term:`entry point` for the command-line script: - $ cd ..; cp -r forms databases; cd databases + .. literalinclude:: databases/setup.py + :linenos: + :emphasize-lines: 9-10, 12, 34-36 -#. We need to add some dependencies in ``databases/setup.py`` as well - as an "entry point" for the command-line script: + .. note:: We aren't yet doing ``$VENV/bin/pip install -e .`` because we need to write a script and update configuration first. - .. literalinclude:: databases/setup.py - :linenos: +#. Our configuration file at ``databases/development.ini`` wires together some new pieces: - .. note:: + .. literalinclude:: databases/development.ini + :language: ini - We aren't yet doing ``python3.3 setup.py develop`` as we - are changing it later. +#. This engine configuration now needs to be read into the application through changes in ``databases/tutorial/__init__.py``: -#. Our configuration file at ``databases/development.ini`` wires - together some new pieces: + .. literalinclude:: databases/tutorial/__init__.py + :linenos: - .. literalinclude:: databases/development.ini - :language: ini +#. Make a command-line script at ``databases/tutorial/initialize_db.py`` to initialize the database: -#. This engine configuration now needs to be read into the application - through changes in ``databases/tutorial/__init__.py``: + .. literalinclude:: databases/tutorial/initialize_db.py + :linenos: - .. literalinclude:: databases/tutorial/__init__.py - :linenos: +#. Now that we've got all the pieces ready, and because we changed ``setup.py``, we now install all the goodies: -#. Make a command-line script at ``databases/tutorial/initialize_db.py`` - to initialize the database: + .. code-block:: bash - .. literalinclude:: databases/tutorial/initialize_db.py + $VENV/bin/pip install -e . -#. Since ``setup.py`` changed, we now run it: +#. The script references some models in ``databases/tutorial/models.py``: - .. code-block:: bash + .. literalinclude:: databases/tutorial/models.py + :linenos: - $ $VENV/bin/python setup.py develop +#. Let's run this console script, thus producing our database and table: -#. The script references some models in ``databases/tutorial/models.py``: + .. code-block:: bash - .. literalinclude:: databases/tutorial/models.py - :linenos: + $VENV/bin/initialize_tutorial_db development.ini -#. Let's run this console script, thus producing our database and table: + 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) + ) - .. code-block:: bash - $ $VENV/bin/initialize_tutorial_db development.ini - 2013-09-06 15:54:08,050 INFO [sqlalchemy.engine.base.Engine][MainThread] PRAGMA table_info("wikipages") - 2013-09-06 15:54:08,050 INFO [sqlalchemy.engine.base.Engine][MainThread] () - 2013-09-06 15:54:08,051 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``: +#. With our data now driven by SQLAlchemy queries, we need to update our ``databases/tutorial/views.py``: - .. literalinclude:: databases/tutorial/views.py + .. literalinclude:: databases/tutorial/views.py + :linenos: -#. Our tests in ``databases/tutorial/tests.py`` changed to include - SQLAlchemy bootstrapping: +#. Our tests in ``databases/tutorial/tests.py`` changed to include SQLAlchemy bootstrapping: - .. literalinclude:: databases/tutorial/tests.py - :linenos: + .. literalinclude:: databases/tutorial/tests.py + :linenos: -#. Run the tests in your package using ``nose``: +#. Run the tests in your package using ``pytest``: .. code-block:: bash - $ $VENV/bin/nosetests . + $VENV/bin/pytest tutorial/tests.py -q .. - ----------------------------------------------------------------- - Ran 2 tests in 1.141s + 2 passed in 1.41 seconds - OK +#. Run your Pyramid application with: -#. Run your Pyramid application with: + .. code-block:: bash - .. code-block:: bash + $VENV/bin/pserve development.ini --reload - $ $VENV/bin/pserve development.ini --reload +#. Open http://localhost:6543/ in a browser. -#. 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? +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 app -application, either as middleware or a Pyramid "tween". Then, -just before you return the response, all transaction-aware parts of -your application are executed. +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. +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. +``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 virtualenv'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 extra work to hook up SQLAlchemy -into the Pyramid transaction manager. It then declares the model for a -``Page``. +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. +dictionary-of-dictionaries data with proper database support: list the rows, +add a row, edit a row, and delete a row. + -Extra Credit +Extra credit ============ -#. Why all this code? Why can't I just type 2 lines and have magic ensue? +#. 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. |