2. Create database models¶
At this point we should create our models. In a nutshell, models represent data and its underlying storage mechanisms in an application.
We will use a relational database and SQLAlchemy’s ORM layer to access our data.
Create and edit models/user.py
¶
Our application will consist of two tables:
- users - stores all users for our application
- entries - stores our blog entries
We should assume that our users might use some Unicode characters, so we need to import the Unicode datatype from SQLAlchemy. We will also need a DateTime field to timestamp our blog entries.
Let’s first create models/user.py
.
$ touch pyramid_blogr/models/user.py
Add the following code to models/user.py
.
1 2 3 4 5 6 7 8 9 | import datetime #<- will be used to set default dates on models
from pyramid_blogr.models.meta import Base #<- we need to import our sqlalchemy metadata from which model classes will inherit
from sqlalchemy import (
Column,
Integer,
Unicode, #<- will provide Unicode field
UnicodeText, #<- will provide Unicode text field
DateTime, #<- time abstraction field
)
|
Make a copy of models/user.py
as models/blog_record.py
. We will need
these imports in both modules.
$ cp pyramid_blogr/models/user.py pyramid_blogr/models/blog_record.py
The alchemy
scaffold in Pyramid provides an example model class MyModel
that we don’t need, as well as code that creates an index, so let’s remove the
file models/mymodel.py
.
$ rm pyramid_blogr/models/mymodel.py
Now our project structure should look like this.
pyramid_blogr/
......
├── models <- model definitions aka data sources (often RDBMS or noSQL)
│ ├── __init__.py <- database engine initialization
│ ├── blog_record.py
│ ├── meta.py <- database sqlalchemy metadata object
│ └── user.py
......
Database session management¶
Note
To learn how to use SQLAlchemy, please consult its Object Relational Tutorial.
If you are new to SQLAlchemy or ORM’s, you are probably wondering what the
code from models/__init__.py
does.
To explain we need to start reading it from the includeme()
part.
52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
def includeme(config):
"""
Initialize the model for a Pyramid app.
Activate this setup using ``config.include('pyramid_blogr.models')``.
"""
settings = config.get_settings()
settings['tm.manager_hook'] = 'pyramid_tm.explicit_manager'
# use pyramid_tm to hook the transaction lifecycle to the request
config.include('pyramid_tm')
# use pyramid_retry to retry a request when transient exceptions occur
config.include('pyramid_retry')
session_factory = get_session_factory(get_engine(settings))
config.registry['dbsession_factory'] = session_factory
# make request.dbsession available for use in Pyramid
config.add_request_method(
|
The first line defines a special function called includeme
it will be
picked up by pyramid on runtime and will ensure that on every request, the
request object will have a dbsession
propery attached that will point to
SQLAlchemy’s session object.
The function also imports pyramid_tm
- it is Pyramid’s transaction manager
that will be attached to our request object as tm property, it will be
managing our dbsession
objects behavior.
We will use it to interact with the database and persist our changes to the database. It is thread-safe, meaning that it will handle multiple requests at the same time in a safe way, and our code from different views will not impact other requests. It will also open and close database connections for us transparently when needed.
What does transaction manager do?¶
WHOA THIS SOUNDS LIKE SCARY MAGIC!!
Note
It’s not.
OK, so while it might sound complicated, in practice it’s very simple and saves a developer a lot of headaches with managing transactions inside an application.
Here’s how the transaction manager process works:
- A transaction is started when a browser request invokes our view code.
- Some operations take place; for example, database rows are inserted or
updated in our datastore.
- If everything went fine, we don’t need to commit our transaction explictly; the transaction manager will do this for us.
- If some unhandled exception occurred, we usually want to roll back all the changes and queries that were sent to our datastore; the transaction manager will handle this for us.
What are the implications of this?¶
Imagine you have an application that sends a confirmation email every time a user registers. A user, Nephthys, inputs the data to register, and we send Nephthys a nice welcome email and maybe an activation link, but during registration flow, something unexpected happens and the code errors out.
It is very common in this situation that the user would get a welcome email, but in reality their profile was never persisted in the database. With packages like pyramid_mailer it is perfectly possible to delay email sending until after the user’s information is successfully saved in the database.
Nice, huh?
Although this is a more advanced topic not covered in depth in this tutorial, the most simple explanation is that the transaction manager will make sure our data gets correctly saved if everything went smoothly, and if an error occurs then our datastore modifications are rolled back.
Adding model definitions¶
Note
This will make the application error out and prevent it from starting until we reach the last point of the current step and fix imports in other files. It’s perfectly normal, so don’t worry about immediate errors.
We will need two declarations of models that will replace the MyModel
class
that was created when we scaffolded our project.
After the import part in models/user.py
add the following.
12 13 14 15 16 17 | class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(Unicode(255), unique=True, nullable=False)
password = Column(Unicode(255), nullable=False)
last_logged = Column(DateTime, default=datetime.datetime.utcnow)
|
After the import part in models/blog_record.py
add the following.
1 2 3 4 5 6 | class BlogRecord(Base):
__tablename__ = 'entries'
id = Column(Integer, primary_key=True)
title = Column(Unicode(255), unique=True, nullable=False)
body = Column(UnicodeText, default=u'')
created = Column(DateTime, default=datetime.datetime.utcnow)
|
Now it’s time to update our models/__init__.py
to include our models. This
is especially handy because it ensures that SQLAlchemy mappers will pick up all
of our model classes and functions, like create_all
, and that the models
will do what you expect.
Add these imports to the file (remember to also remove the MyModel
import).
6 7 8 9 10 | # import or define all models here to ensure they are attached to the
# Base.metadata prior to any initialization routines
from .user import User
from .blog_record import BlogRecord
|
Update database initialization script¶
It’s time to update our database initialization script to mirror the changes in
our models
package.
Open scripts/initializedb.py
. This is the file that actually gets executed
when we run initialize_pyramid_blogr_db
.
We will remove the MyModel
and import the User
model.
18 | from ..models import User
|
Since the MyModel
model is now gone, we want to replace the following bits:
with transaction.manager:
dbsession = get_tm_session(session_factory, transaction.manager)
model = MyModel(name='one', value=1)
dbsession.add(model)
with this:
41 42 43 44 45 | with transaction.manager:
dbsession = get_tm_session(session_factory, transaction.manager)
admin = User(name=u'admin', password=u'admin')
dbsession.add(admin)
|
When you initialize a fresh database, this will populate it with a single user,
with both login and unencrypted password equal to admin
.
Warning
This is just a tutorial example and production code should utilize passwords hashed with a strong one-way encryption function. You can use a package like passlib or cryptacular for this purpose.
The last step to get the application running is to change views/default.py
MyModel
class into out User model.
from ..models import MyModel
into changes to
6 | from ..models.user import User
|
and the query in my_view
changes to:
12 13 14 15 16 17 18 | def my_view(request):
try:
query = request.dbsession.query(User)
one = query.filter(User.name == 'admin').first()
except DBAPIError:
return Response(db_err_msg, content_type='text/plain', status=500)
return {'one': one, 'project': 'pyramid_blogr'}
|
Our application should start again if we try running the server.
$ $VENV/bin/pserve --reload development.ini
If you visit the URL http://0.0.0.0:6543 then you should see a “Pyramid is having a problem …” error message.
In case you have problems starting the application, you can see complete source code of the files we modifed below.
models/__init__.py
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | from sqlalchemy import engine_from_config
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import configure_mappers
import zope.sqlalchemy
# import or define all models here to ensure they are attached to the
# Base.metadata prior to any initialization routines
from .user import User
from .blog_record import BlogRecord
# run configure_mappers after defining all of the models to ensure
# all relationships can be setup
configure_mappers()
def get_engine(settings, prefix='sqlalchemy.'):
return engine_from_config(settings, prefix)
def get_session_factory(engine):
factory = sessionmaker()
factory.configure(bind=engine)
return factory
def get_tm_session(session_factory, transaction_manager):
"""
Get a ``sqlalchemy.orm.Session`` instance backed by a transaction.
This function will hook the session to the transaction manager which
will take care of committing any changes.
- When using pyramid_tm it will automatically be committed or aborted
depending on whether an exception is raised.
- When using scripts you should wrap the session in a manager yourself.
For example::
import transaction
engine = get_engine(settings)
session_factory = get_session_factory(engine)
with transaction.manager:
dbsession = get_tm_session(session_factory, transaction.manager)
"""
dbsession = session_factory()
zope.sqlalchemy.register(
dbsession, transaction_manager=transaction_manager)
return dbsession
def includeme(config):
"""
Initialize the model for a Pyramid app.
Activate this setup using ``config.include('pyramid_blogr.models')``.
"""
settings = config.get_settings()
settings['tm.manager_hook'] = 'pyramid_tm.explicit_manager'
# use pyramid_tm to hook the transaction lifecycle to the request
config.include('pyramid_tm')
# use pyramid_retry to retry a request when transient exceptions occur
config.include('pyramid_retry')
session_factory = get_session_factory(get_engine(settings))
config.registry['dbsession_factory'] = session_factory
# make request.dbsession available for use in Pyramid
config.add_request_method(
# r.tm is the transaction manager used by pyramid_tm
lambda r: get_tm_session(session_factory, r.tm),
'dbsession',
reify=True
)
|
models/user.py
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | import datetime #<- will be used to set default dates on models
from pyramid_blogr.models.meta import Base #<- we need to import our sqlalchemy metadata from which model classes will inherit
from sqlalchemy import (
Column,
Integer,
Unicode, #<- will provide Unicode field
UnicodeText, #<- will provide Unicode text field
DateTime, #<- time abstraction field
)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(Unicode(255), unique=True, nullable=False)
password = Column(Unicode(255), nullable=False)
last_logged = Column(DateTime, default=datetime.datetime.utcnow)
|
models/blog_record.py
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | import datetime #<- will be used to set default dates on models
from pyramid_blogr.models.meta import Base #<- we need to import our sqlalchemy metadata from which model classes will inherit
from sqlalchemy import (
Column,
Integer,
Unicode, #<- will provide Unicode field
UnicodeText, #<- will provide Unicode text field
DateTime, #<- time abstraction field
)
class BlogRecord(Base):
__tablename__ = 'entries'
id = Column(Integer, primary_key=True)
title = Column(Unicode(255), unique=True, nullable=False)
body = Column(UnicodeText, default=u'')
created = Column(DateTime, default=datetime.datetime.utcnow)
edited = Column(DateTime, default=datetime.datetime.utcnow)
|
scripts/initializedb.py
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | import os
import sys
import transaction
from pyramid.paster import (
get_appsettings,
setup_logging,
)
from pyramid.scripts.common import parse_vars
from ..models.meta import Base
from ..models import (
get_engine,
get_session_factory,
get_tm_session,
)
from ..models import User
def usage(argv):
cmd = os.path.basename(argv[0])
print('usage: %s <config_uri> [var=value]\n'
'(example: "%s development.ini")' % (cmd, cmd))
sys.exit(1)
def main(argv=sys.argv):
if len(argv) < 2:
usage(argv)
config_uri = argv[1]
options = parse_vars(argv[2:])
setup_logging(config_uri)
settings = get_appsettings(config_uri, options=options)
engine = get_engine(settings)
Base.metadata.create_all(engine)
session_factory = get_session_factory(engine)
with transaction.manager:
dbsession = get_tm_session(session_factory, transaction.manager)
admin = User(name=u'admin', password=u'admin')
dbsession.add(admin)
|
__init__.py
1 2 3 4 5 6 7 8 9 10 11 12 | from pyramid.config import Configurator
def main(global_config, **settings):
""" This function returns a Pyramid WSGI application.
"""
config = Configurator(settings=settings)
config.include('pyramid_jinja2')
config.include('.models')
config.include('.routes')
config.scan()
return config.make_wsgi_app()
|
views/default.py
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | from pyramid.response import Response
from pyramid.view import view_config
from sqlalchemy.exc import DBAPIError
from ..models.user import User
@view_config(route_name='home', renderer='../templates/mytemplate.jinja2')
def my_view(request):
try:
query = request.dbsession.query(User)
one = query.filter(User.name == 'admin').first()
except DBAPIError:
return Response(db_err_msg, content_type='text/plain', status=500)
return {'one': one, 'project': 'pyramid_blogr'}
db_err_msg = """\
Pyramid is having a problem using your SQL database. The problem
might be caused by one of the following things:
1. You may need to run the "initialize_pyramid_blogr_db" script
to initialize your database tables. Check your virtual
environment's "bin" directory for this script and try to run it.
2. Your database server may not be running. Check that the
database server referred to by the "sqlalchemy.url" setting in
your "development.ini" file is running.
After you fix the problem, please restart the Pyramid application to
try it again.
"""
|
If our application starts correctly, you should run the
initialize_pyramid_blogr_db
command to update the database.
$ $VENV/bin/initialize_pyramid_blogr_db development.ini
Next 3. Application routes.