Alex's Blog

1. Set up pyramid environment

Create Python 3 virtual environment then install cookiecutter -- a command line utility for creating and managing project templates.

$ mkvirtualenv --python `which python3` pyramid-test
$ pip install cookiecutter

2. Create pyramid project w SQLAlchemy using cookiecutter

I'm using the official cookiecutter template from Pylons for Pyramid project with SQLAlchemy ORM and SQLite. Later in the setup, the project settings will be modified for SQLAlchemy to work with Postgres.

When presented 'project_name[Pyramid Scaffold]' question in cookicutter setup dialog, specify pyramid_test as the name of the project, or the default project name -- 'Pyramid Scaffold' will be used.

(pyramid-test)$ cookiecutter https://github.com/Pylons/pyramid-cookiecutter-alchemy
project_name [Pyramid Scaffold]: pyramid_test
repo_name [scaffold]:

===============================================================================
Documentation: http://docs.pylonsproject.org/projects/pyramid/en/latest/
Tutorials:     http://docs.pylonsproject.org/projects/pyramid_tutorials/en/latest/
Twitter:       https://twitter.com/trypyramid
Mailing List:  https://groups.google.com/forum/#!forum/pylons-discuss
Welcome to Pyramid.  Sorry for the convenience.
==========================================================================
Change directory into your newly created project.
cd scaffold
Create a Python virtual environment.
python3 -m venv env

Upgrade packaging tools.
env/bin/pip install --upgrade pip setuptools
Install the project in editable mode with its testing requirements.
env/bin/pip install -e ".[testing]"
Configure the database:
env/bin/initialize_scaffold_db development.ini
Run your project's tests.
env/bin/pytest
Run your project.
env/bin/pserve development.ini

4. Install all project dependencies

Install psycopg2 and alembic python packages, by editing setup.py and adding package dependency to requires list.

requires = [
    ...,
    'psycopg2',
    'alembic',
]

• psycopg2 -- Postgres database driver that lets SQLAlchemy connect to postgres database
• alembic -- a database migration tool for SQLAlchemy.

The following command install all dependencies specified in 'requirements' section in setup.py all requirements for setting up pyramid framework and related dependencies are specified there.

(pyramid-test)$ pip install -e .

To verify that the packages were install run the following command that lists installed python packages and make sure psycopg2 and alembic are mentioned

(pyramid-test)$ pip freeze

3. Setup postgres db

This step assumes you already installed Postgres 9.x and able to log in as 'postgres' admin user. This setup has been used on Debian systems, but with minor differences in configuration file locations should apply to any Linux distribution.

Get access to postgres admin shell.

# su postgres
$ psql
postgres=#

Create a user and a database for this project. Here I'm using username 'alex', change username according to your local system username -- this will come in handy when setting up peer authentication.

postgres=# CREATE USER alex WITH PASSWORD 'secret';
postgres=# CREATE DATABASE pyramidtest OWNER alex;

From root console edit /etc/postgresql/9.x/main/pg_hba.conf add the following lines at the bottom of the file -- these are declarations for postgres server to allow peer and password authentication.

local   postgrestest    alex                                    peer
host    postgrestest    alex            127.0.0.1               md5

Restart postgres db.

# service postgresql restart

4. Connect to postgresdb from pyramid project

Edit development.ini, updating sqlalchemy.url to the following

sqlalchemy.url = postgresql:///pyramidtest

This config line lets pyramid project connect to postgres via unix socket using 'peer' authentication -- the database engine will verify that the name of system account that attempts to log in, matches the name of owner account of the database. When 'peer' authentication is used, Postgres doesn't verify the password set with 'CREATE USER..' command.

5. Configure alembic to manage migrations

I use alembic to make sure that the project is able to connect to the database and execute its initial migration. Alembic retrieves SQLAlchemy model definitions from /models/ folder and create appropriate tables in Postgres database.

Add alembic to one of project requirements in setup.py

requires = [
    ...,
    'alembic',
]

Then re-install the dependencies.

(pyramid-test)$ pip install -e .

Initialize alembic for the project. This creates /alembic/ subdirectory and populates it with initial configuration.

(pyramid-test)$ alembic init alembic

In alembic.ini update sqlalchemy.url variable to the same sqlalchemy.url value set in development.ini.

sqlalchemy.url = postgresql:///pyramidtest

from pyramid_test.models.meta import Base
target_metadata = Base.metadata

$ alembic revision --autogenerate -m "Initial Commit"
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'models'
INFO  [alembic.autogenerate.compare] Detected added index 'my_index' on '['name']'
  Generating /home/alex/gitlab/pyramid_test/alembic/versions/2e0b2d81bfbb_initial_commit.py ... done

from sqlalchemy import (
    Column,
    Index,
    Integer,
    Text,
)

from .meta import Base


class MyModel(Base):
    __tablename__ = 'models'
    id = Column(Integer, primary_key=True)
    name = Column(Text)
    value = Column(Integer)

Index('my_index', MyModel.name, unique=True, mysql_length=255)

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('models',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Text(), nullable=True),
    sa.Column('value', sa.Integer(), nullable=True),
    sa.PrimaryKeyConstraint('id', name=op.f('pk_models'))
    )
    op.create_index('my_index', 'models', ['name'], unique=True, mysql_length=255)
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('my_index', table_name='models')
    op.drop_table('models')
    # ### end Alembic commands ###

6 Apply alembic migrations

(pyramid-test)$ alembic upgrade head
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 2e0b2d81bfbb, Initial Commit

This will create two tables in postgrestest database.

• alembic_version -- keeps track of database versioning for alembic
• models -- model defined in /models/mymodel.py

See screenshot below as the contend of the database is displayed in PGadmin3

7 Perform test write to the database

To verify that the database is set up correctly, create a sample object and commit it to the database using Pyramid Shell. The sample code is based on scripts/initialize.db

(pyramid-test)$ pshell development.ini
>>> from pyramid_test.models.mymodel import MyModel
>>> db = request.dbsession
>>> obj_one = MyModel(name='one', value=1)
>>> db.add(obj_one)
>>> request.tm.commit()
>>> db.flush()

To retrieve newly created object from the database, run the following commands.

(pyramid-test)$ pshell development.ini
>>> from pyramid_test.models.mymodel import MyModel
>>> db = request.dbsession
>>> mymodel = db.query(MyModel).one()
>>> print(mymodel)
<pyramid_test.models.mymodel.MyModel object at 0x7fce6d83ceb8>
>>> print(mymodel.name)
one

The object now persists in the database.

8 Run the server

(pyramid-test)$ pserve develoment.ini

Open a web browser and go to http://localhost:6543/ you will be greeted with pyramid demo message.

9 Sample Code

Sample code for the project located in -- https://github.com/avolkov/pyramid-test