Defining the Domain Model

The first change we'll make to our stock cookiecutter-generated application will be to define a wiki page domain model.

Note

There is nothing special about the filename user.py or page.py except that they are Python modules. A project may have many models throughout its codebase in arbitrarily named modules. Modules implementing models often have model in their names or they may live in a Python subpackage of your application package named models (as we've done in this tutorial), but this is only a convention and not a requirement.

Declaring dependencies in our setup.py file

The models code in our application will depend on a package which is not a dependency of the original "tutorial" application. The original "tutorial" application was generated by the cookiecutter; it doesn't know about our custom application requirements.

We need to add a dependency, the bcrypt package, to our tutorial package's setup.py file by assigning this dependency to the requires parameter in the setup() function.

Open tutorial/setup.py and edit it to look like the following:

 1import os
 2
 3from setuptools import setup, find_packages
 4
 5here = os.path.abspath(os.path.dirname(__file__))
 6with open(os.path.join(here, 'README.txt')) as f:
 7    README = f.read()
 8with open(os.path.join(here, 'CHANGES.txt')) as f:
 9    CHANGES = f.read()
10
11requires = [
12    'alembic',
13    'bcrypt',
14    'plaster_pastedeploy',
15    'pyramid >= 1.9',
16    'pyramid_debugtoolbar',
17    'pyramid_jinja2',
18    'pyramid_retry',
19    'pyramid_tm',
20    'SQLAlchemy',
21    'transaction',
22    'zope.sqlalchemy',
23    'waitress',
24]
25
26tests_require = [
27    'WebTest >= 1.3.1',  # py3 compat
28    'pytest>=3.7.4',
29    'pytest-cov',
30]
31
32setup(
33    name='tutorial',
34    version='0.0',
35    description='myproj',
36    long_description=README + '\n\n' + CHANGES,
37    classifiers=[
38        'Programming Language :: Python',
39        'Framework :: Pyramid',
40        'Topic :: Internet :: WWW/HTTP',
41        'Topic :: Internet :: WWW/HTTP :: WSGI :: Application',
42    ],
43    author='',
44    author_email='',
45    url='',
46    keywords='web pyramid pylons',
47    packages=find_packages(),
48    include_package_data=True,
49    zip_safe=False,
50    extras_require={
51        'testing': tests_require,
52    },
53    install_requires=requires,
54    entry_points={
55        'paste.app_factory': [
56            'main = tutorial:main',
57        ],
58        'console_scripts': [
59            'initialize_tutorial_db = tutorial.scripts.initialize_db:main',
60        ],
61    },
62)

Only the highlighted line needs to be added.

Note

We are using the bcrypt package from PyPI to hash our passwords securely. There are other one-way hash algorithms for passwords if bcrypt is an issue on your system. Just make sure that it's an algorithm approved for storing passwords versus a generic one-way hash.

Running pip install -e .

Since a new software dependency was added, you will need to run pip install -e . again inside the root of the tutorial package to obtain and register the newly added dependency distribution.

Make sure your current working directory is the root of the project (the directory in which setup.py lives) and execute the following command.

On Unix:

$VENV/bin/pip install -e .

On Windows:

%VENV%\Scripts\pip install -e .

Success executing this command will end with a line to the console something like the following.

Successfully installed bcrypt-3.1.4 cffi-1.11.5 pycparser-2.18 tutorial

Remove mymodel.py

Let's delete the file tutorial/models/mymodel.py. The MyModel class is only a sample and we're not going to use it.

Add user.py

Create a new file tutorial/models/user.py with the following contents:

 1import bcrypt
 2from sqlalchemy import (
 3    Column,
 4    Integer,
 5    Text,
 6)
 7
 8from .meta import Base
 9
10
11class User(Base):
12    """ The SQLAlchemy declarative model class for a User object. """
13    __tablename__ = 'users'
14    id = Column(Integer, primary_key=True)
15    name = Column(Text, nullable=False, unique=True)
16    role = Column(Text, nullable=False)
17
18    password_hash = Column(Text)
19
20    def set_password(self, pw):
21        pwhash = bcrypt.hashpw(pw.encode('utf8'), bcrypt.gensalt())
22        self.password_hash = pwhash.decode('utf8')
23
24    def check_password(self, pw):
25        if self.password_hash is not None:
26            expected_hash = self.password_hash.encode('utf8')
27            return bcrypt.checkpw(pw.encode('utf8'), expected_hash)
28        return False

This is a very basic model for a user who can authenticate with our wiki.

We discussed briefly in the previous chapter that our models will inherit from an SQLAlchemy sqlalchemy.ext.declarative.declarative_base(). This will attach the model to our schema.

As you can see, our User class has a class-level attribute __tablename__ which equals the string users. Our User class will also have class-level attributes named id, name, password_hash, and role (all instances of sqlalchemy.schema.Column). These will map to columns in the users table. The id attribute will be the primary key in the table. The name attribute will be a text column, each value of which needs to be unique within the column. The password_hash is a nullable text attribute that will contain a securely hashed password. Finally, the role text attribute will hold the role of the user.

There are two helper methods that will help us later when using the user objects. The first is set_password which will take a raw password and transform it using bcrypt into an irreversible representation, a process known as "hashing". The second method, check_password, will allow us to compare the hashed value of the submitted password against the hashed value of the password stored in the user's record in the database. If the two hashed values match, then the submitted password is valid, and we can authenticate the user.

We hash passwords so that it is impossible to decrypt them and use them to authenticate in the application. If we stored passwords foolishly in clear text, then anyone with access to the database could retrieve any password to authenticate as any user.

Add page.py

Create a new file tutorial/models/page.py with the following contents:

 1from sqlalchemy import (
 2    Column,
 3    ForeignKey,
 4    Integer,
 5    Text,
 6)
 7from sqlalchemy.orm import relationship
 8
 9from .meta import Base
10
11
12class Page(Base):
13    """ The SQLAlchemy declarative model class for a Page object. """
14    __tablename__ = 'pages'
15    id = Column(Integer, primary_key=True)
16    name = Column(Text, nullable=False, unique=True)
17    data = Column(Text, nullable=False)
18
19    creator_id = Column(ForeignKey('users.id'), nullable=False)
20    creator = relationship('User', backref='created_pages')

As you can see, our Page class is very similar to the User defined above, except with attributes focused on storing information about a wiki page, including id, name, and data. The only new construct introduced here is the creator_id column, which is a foreign key referencing the users table. Foreign keys are very useful at the schema-level, but since we want to relate User objects with Page objects, we also define a creator attribute as an ORM-level mapping between the two tables. SQLAlchemy will automatically populate this value using the foreign key referencing the user. Since the foreign key has nullable=False, we are guaranteed that an instance of page will have a corresponding page.creator, which will be a User instance.

Edit models/__init__.py

Since we are using a package for our models, we also need to update our __init__.py file to ensure that the models are attached to the metadata.

Open the tutorial/models/__init__.py file and edit it to look like the following:

 1from sqlalchemy import engine_from_config
 2from sqlalchemy.orm import sessionmaker
 3from sqlalchemy.orm import configure_mappers
 4import zope.sqlalchemy
 5
 6# import or define all models here to ensure they are attached to the
 7# Base.metadata prior to any initialization routines
 8from .page import Page  # flake8: noqa
 9from .user import User  # flake8: noqa
10
11# run configure_mappers after defining all of the models to ensure
12# all relationships can be setup
13configure_mappers()
14
15
16def get_engine(settings, prefix='sqlalchemy.'):
17    return engine_from_config(settings, prefix)
18
19
20def get_session_factory(engine):
21    factory = sessionmaker()
22    factory.configure(bind=engine)
23    return factory
24
25
26def get_tm_session(session_factory, transaction_manager):
27    """
28    Get a ``sqlalchemy.orm.Session`` instance backed by a transaction.
29
30    This function will hook the session to the transaction manager which
31    will take care of committing any changes.
32
33    - When using pyramid_tm it will automatically be committed or aborted
34      depending on whether an exception is raised.
35
36    - When using scripts you should wrap the session in a manager yourself.
37      For example::
38
39          import transaction
40
41          engine = get_engine(settings)
42          session_factory = get_session_factory(engine)
43          with transaction.manager:
44              dbsession = get_tm_session(session_factory, transaction.manager)
45
46    """
47    dbsession = session_factory()
48    zope.sqlalchemy.register(
49        dbsession, transaction_manager=transaction_manager)
50    return dbsession
51
52
53def includeme(config):
54    """
55    Initialize the model for a Pyramid app.
56
57    Activate this setup using ``config.include('tutorial.models')``.
58
59    """
60    settings = config.get_settings()
61    settings['tm.manager_hook'] = 'pyramid_tm.explicit_manager'
62
63    # use pyramid_tm to hook the transaction lifecycle to the request
64    config.include('pyramid_tm')
65
66    # use pyramid_retry to retry a request when transient exceptions occur
67    config.include('pyramid_retry')
68
69    session_factory = get_session_factory(get_engine(settings))
70    config.registry['dbsession_factory'] = session_factory
71
72    # make request.dbsession available for use in Pyramid
73    config.add_request_method(
74        # r.tm is the transaction manager used by pyramid_tm
75        lambda r: get_tm_session(session_factory, r.tm),
76        'dbsession',
77        reify=True
78    )

Here we align our imports with the names of the models, Page and User.

Migrate the database with Alembic

Now that we have written our models, we need to modify the database schema to reflect the changes to our code. Let's generate a new revision, then upgrade the database to the latest revision (head).

On Unix:

$VENV/bin/alembic -c development.ini revision --autogenerate \
    -m "use new models Page and User"
$VENV/bin/alembic -c development.ini upgrade head

On Windows:

%VENV%\Scripts\alembic -c development.ini revision \
    --autogenerate -m "use new models Page and User"
%VENV%\Scripts\alembic -c development.ini upgrade head

Success executing these commands will generate output similar to the following.

2018-06-29 01:28:42,407 INFO  [sqlalchemy.engine.base.Engine:1254][MainThread] SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2018-06-29 01:28:42,407 INFO  [sqlalchemy.engine.base.Engine:1255][MainThread] ()
2018-06-29 01:28:42,408 INFO  [sqlalchemy.engine.base.Engine:1254][MainThread] SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2018-06-29 01:28:42,408 INFO  [sqlalchemy.engine.base.Engine:1255][MainThread] ()
2018-06-29 01:28:42,409 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread] PRAGMA table_info("alembic_version")
2018-06-29 01:28:42,409 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ()
2018-06-29 01:28:42,410 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread] SELECT alembic_version.version_num
FROM alembic_version
2018-06-29 01:28:42,410 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ()
2018-06-29 01:28:42,411 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread] SELECT name FROM sqlite_master WHERE type='table' ORDER BY name
2018-06-29 01:28:42,412 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ()
2018-06-29 01:28:42,413 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread] PRAGMA table_info("models")
2018-06-29 01:28:42,413 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ()
2018-06-29 01:28:42,414 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread] SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'models' AND type = 'table'
2018-06-29 01:28:42,414 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ()
2018-06-29 01:28:42,414 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread] PRAGMA foreign_key_list("models")
2018-06-29 01:28:42,414 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ()
2018-06-29 01:28:42,414 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread] SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'models' AND type = 'table'
2018-06-29 01:28:42,415 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ()
2018-06-29 01:28:42,416 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread] PRAGMA index_list("models")
2018-06-29 01:28:42,416 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ()
2018-06-29 01:28:42,416 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread] PRAGMA index_info("my_index")
2018-06-29 01:28:42,416 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ()
2018-06-29 01:28:42,417 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread] PRAGMA index_list("models")
2018-06-29 01:28:42,417 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ()
2018-06-29 01:28:42,417 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread] PRAGMA index_info("my_index")
2018-06-29 01:28:42,417 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ()
2018-06-29 01:28:42,417 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread] SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'models' AND type = 'table'
2018-06-29 01:28:42,417 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ()
  Generating /<somepath>/tutorial/tutorial/alembic/versions/20180629_23e9f8eb6c28.py ... done

2018-06-29 01:29:37,957 INFO  [sqlalchemy.engine.base.Engine:1254][MainThread] SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2018-06-29 01:29:37,958 INFO  [sqlalchemy.engine.base.Engine:1255][MainThread] ()
2018-06-29 01:29:37,958 INFO  [sqlalchemy.engine.base.Engine:1254][MainThread] SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2018-06-29 01:29:37,958 INFO  [sqlalchemy.engine.base.Engine:1255][MainThread] ()
2018-06-29 01:29:37,960 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread] PRAGMA table_info("alembic_version")
2018-06-29 01:29:37,960 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ()
2018-06-29 01:29:37,960 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread] SELECT alembic_version.version_num
FROM alembic_version
2018-06-29 01:29:37,960 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ()
2018-06-29 01:29:37,963 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread]
CREATE TABLE users (
        id INTEGER NOT NULL,
        name TEXT NOT NULL,
        role TEXT NOT NULL,
        password_hash TEXT,
        CONSTRAINT pk_users PRIMARY KEY (id),
        CONSTRAINT uq_users_name UNIQUE (name)
)


2018-06-29 01:29:37,963 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ()
2018-06-29 01:29:37,966 INFO  [sqlalchemy.engine.base.Engine:722][MainThread] COMMIT
2018-06-29 01:29:37,968 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread]
CREATE TABLE pages (
        id INTEGER NOT NULL,
        name TEXT NOT NULL,
        data TEXT NOT NULL,
        creator_id INTEGER NOT NULL,
        CONSTRAINT pk_pages PRIMARY KEY (id),
        CONSTRAINT fk_pages_creator_id_users FOREIGN KEY(creator_id) REFERENCES users (id),
        CONSTRAINT uq_pages_name UNIQUE (name)
)


2018-06-29 01:29:37,968 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ()
2018-06-29 01:29:37,969 INFO  [sqlalchemy.engine.base.Engine:722][MainThread] COMMIT
2018-06-29 01:29:37,969 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread]
DROP INDEX my_index
2018-06-29 01:29:37,969 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ()
2018-06-29 01:29:37,970 INFO  [sqlalchemy.engine.base.Engine:722][MainThread] COMMIT
2018-06-29 01:29:37,970 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread]
DROP TABLE models
2018-06-29 01:29:37,970 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ()
2018-06-29 01:29:37,971 INFO  [sqlalchemy.engine.base.Engine:722][MainThread] COMMIT
2018-06-29 01:29:37,972 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread] UPDATE alembic_version SET version_num='23e9f8eb6c28' WHERE alembic_version.version_num = 'b6b22ae3e628'
2018-06-29 01:29:37,972 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ()
2018-06-29 01:29:37,972 INFO  [sqlalchemy.engine.base.Engine:722][MainThread] COMMIT

Alembic overview

Let's briefly discuss our configuration for Alembic.

In the alchemy cookiecutter's development.ini file, the setting for script_location configures Alembic to look for the migration script in the directory tutorial/alembic. By default Alembic stores the migration files one level deeper in tutorial/alembic/versions. These files are generated by Alembic, then executed when we run upgrade or downgrade migrations. The setting file_template provides the format for each migration's file name. We've configured the file_template setting to make it somewhat easy to find migrations by file name.

At this point in this tutorial, we have two migration files. Examine them to see what Alembic will do when you upgrade or downgrade the database to a specific revision. Notice the revision identifiers and how they relate to one another in a chained sequence.

See also

For further information, see the Alembic documentation.

Edit scripts/initialize_db.py

We haven't looked at the details of this file yet, but within the scripts directory of your tutorial package is a file named initialize_db.py. Code in this file is executed whenever we run the initialize_tutorial_db command, as we did in the installation step of this tutorial.

Note

The command is named initialize_tutorial_db because of the mapping defined in the [console_scripts] entry point of our project's setup.py file.

Since we've changed our model, we need to make changes to our initialize_db.py script. In particular, we'll replace our import of MyModel with those of User and Page. We'll also change the the script to create two User objects (basic and editor) as well as a Page, rather than a MyModel, and add them to our dbsession.

Open tutorial/scripts/initialize_db.py and edit it to look like the following:

 1import argparse
 2import sys
 3
 4from pyramid.paster import bootstrap, setup_logging
 5from sqlalchemy.exc import OperationalError
 6
 7from .. import models
 8
 9
10def setup_models(dbsession):
11    editor = models.User(name='editor', role='editor')
12    editor.set_password('editor')
13    dbsession.add(editor)
14
15    basic = models.User(name='basic', role='basic')
16    basic.set_password('basic')
17    dbsession.add(basic)
18
19    page = models.Page(
20        name='FrontPage',
21        creator=editor,
22        data='This is the front page',
23    )
24    dbsession.add(page)
25
26
27def parse_args(argv):
28    parser = argparse.ArgumentParser()
29    parser.add_argument(
30        'config_uri',
31        help='Configuration file, e.g., development.ini',
32    )
33    return parser.parse_args(argv[1:])
34
35
36def main(argv=sys.argv):
37    args = parse_args(argv)
38    setup_logging(args.config_uri)
39    env = bootstrap(args.config_uri)
40
41    try:
42        with env['request'].tm:
43            dbsession = env['request'].dbsession
44            setup_models(dbsession)
45    except OperationalError:
46        print('''
47Pyramid is having a problem using your SQL database.  The problem
48might be caused by one of the following things:
49
501.  You may need to initialize your database tables with `alembic`.
51    Check your README.txt for description and try to run it.
52
532.  Your database server may not be running.  Check that the
54    database server referred to by the "sqlalchemy.url" setting in
55    your "development.ini" file is running.
56            ''')

Only the highlighted lines need to be changed.

Populating the database

Because our model has changed, and to repopulate the database, we need to rerun the initialize_tutorial_db command to pick up the changes we've made to the initialize_db.py file. See Initialize and upgrade the database using Alembic for instructions.

Success will look something like this:

2018-06-29 01:30:39,326 INFO  [sqlalchemy.engine.base.Engine:1254][MainThread] SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2018-06-29 01:30:39,326 INFO  [sqlalchemy.engine.base.Engine:1255][MainThread] ()
2018-06-29 01:30:39,327 INFO  [sqlalchemy.engine.base.Engine:1254][MainThread] SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2018-06-29 01:30:39,327 INFO  [sqlalchemy.engine.base.Engine:1255][MainThread] ()
2018-06-29 01:30:39,328 INFO  [sqlalchemy.engine.base.Engine:682][MainThread] BEGIN (implicit)
2018-06-29 01:30:39,329 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread] INSERT INTO users (name, role, password_hash) VALUES (?, ?, ?)
2018-06-29 01:30:39,329 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ('editor', 'editor', '$2b$12$PlaJSN7goVbyx8OFs8yAju9n5gHGdI6PZ2QRJGM2jDCiEU4ItUNxy')
2018-06-29 01:30:39,330 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread] INSERT INTO users (name, role, password_hash) VALUES (?, ?, ?)
2018-06-29 01:30:39,330 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ('basic', 'basic', '$2b$12$MvXdM8jlkbjEyPZ6uXzRg.yatZZK8jCwfPaM7kFkmVJiJjRoCCvmW')
2018-06-29 01:30:39,331 INFO  [sqlalchemy.engine.base.Engine:1151][MainThread] INSERT INTO pages (name, data, creator_id) VALUES (?, ?, ?)
2018-06-29 01:30:39,331 INFO  [sqlalchemy.engine.base.Engine:1154][MainThread] ('FrontPage', 'This is the front page', 1)
2018-06-29 01:30:39,332 INFO  [sqlalchemy.engine.base.Engine:722][MainThread] COMMIT

View the application in a browser

We can't. At this point, our system is in a "non-runnable" state; we'll need to change view-related files in the next chapter to be able to start the application successfully. If you try to start the application (see Start the application) and visit http://localhost:6543, you'll wind up with a Python traceback on your console that ends with this exception:

AttributeError: module 'tutorial.models' has no attribute 'MyModel'

This will also happen if you attempt to run the tests.