Updated for Pylons 0.9.6 and SQLAlchemy 0.4.
For people who haven't used Pylons before, think about it like putting on a theatrical production. First of all you need a stage or backdrop, then we put together a script so we can show off the cast. Pylons, as a framework, runs in the background like the backdrop and stage hands. The play you're producing is called an "application" or "project" in Pylons terminology, so we'll use those terms too.
A Python egg corresponding to some version of this article is under the "Attachments" tab.
If you have any SQLAlchemy questions not addressed here, see the excellent SQLAlchemy manual
.
 |
TODO:
|
Step 1 - Building the Backdrop
Step 1.1 - Getting the Odds and Ends
Install Pylons, SQLAlchemy, the database engine you intend to use, and the Python interface to that database engine.
There are several other documents around describing how to install these, so we'll just list the Python packages you'll need.
1
2
3
4
5
6 | easy_install Pylons
easy_install SQLAlchemy
easy_install pysqlite # for sqlite (included in Python 2.5)
easy_install MySQL-python # for MySQL
easy_install psycopg2 # for PostgreSQL
|
SQLite is an easy-to-install engine for learning with, and an adequate choice for non-huge projects. MySQL and PostgreSQL are good production workhorses. SQLAlchemy also supports Oracle, MS-SQL, Firebird, ODBC, and other engines.
Step 1.2 - Erecting the Pylons
cd to your projects directory. Do not use your webserver's "htdocs" directory, as this will publish your application's source files rather than the running application, which would be a security hole if the source contains passwords. If you have no other place, use your home directory. The example will use "/home/odin", abbreviated "~". The project will be called MyBlog.
1
2
3 | cd /home/odin # I am Odin, the chief Norse god.
paster create -t pylons MyBlog # This will show a whole lot of output.
cd MyBlog # You'll now be in /home/odin/MyBlog
|
Step 1.3 - Models and Data
We'll set up the database according to the Using SQLAlchemy with Pylons tutorial. (We'll just add an init_model function that will soon be added to that tutorial.)
Put this in the "[app:main]" section.
For MySQL:
1
2
3 | sqlalchemy.url = mysql://username:password@host:port/database
sqlalchemy.pool_recycle = 3600
sqlalchemy.convert_unicode = true
|
For SQLite:
1
2 | sqlalchemy.url = sqlite:///%(here)s/db.sqlite
sqlalchemy.convert_unicode = true
|
For Postgres:
1
2 | sqlalchemy.url = postgres://username:password@host:port/database
sqlalchemy.convert_unicode = true
|
The SQLite example puts the database in the same directory as development.ini.
Because databases cannot store Unicode directly, the "sqlalchemy.convert_unicode" option makes SQLAlchemy convert String columns to UTF-8 on write, and back to Unicode on read. Otherwise you'd get "str" strings containing whatever's in the database verbatim. (This is preferred over MySQL's "?use_unicode=1" option because it's database neutral.)
Add this at the top of the module:
1
2 | import sqlalchemy as sa
from myblog import model
|
And put this at the end of the load_environment function:
1
2 | engine = sa.engine_from_config(config, "sqlalchemy.") # notice there is trailing dot
model.init_model(engine)
|
load_environment is called when your application starts running. Here we create a SQLAlchemy "engine", which is a pool of connections to a certain database, and bind it to the model (which we haven't created yet).
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 | import sqlalchemy as sa
from sqlalchemy import orm
from sqlalchemy import types
def init_model(bind):
"""Call me at the beginning of the application.
'bind' is a SQLAlchemy engine or connection, as returned by
sa.create_engine, sa.engine_from_config, or engine.connect().
"""
global engine, Session
engine = bind
Session = orm.scoped_session(
orm.sessionmaker(transactional=True, autoflush=True, bind=bind))
orm.mapper(Blog, blog_table,
order_by=[blog_table.c.date.desc()])
meta = sa.MetaData()
blog_table = sa.Table("Blog", meta,
sa.Column("id", types.Integer, primary_key=True, autoincrement=True),
sa.Column("subject", types.String(255)),
sa.Column("author", types.String(255)),
sa.Column("date", types.DateTime()),
sa.Column("content", types.Text()), #or types.TEXT() - to .Text() doesn't exists :/
)
class Blog(object):
def __str(self):
return self.title
|
Why is some code in init_app and other code isn't? The code in init_app depends on a live database connection, which is not available when the model is imported. All programs that use the model will have to initialize a SQLAlchemy engine on their own, and call init_model(engine) before using any of the tables or classes in the model. "bind" is a SQLAlchemy argument convention meaning either an engine or a connection is acceptable. It also gets around the fact that we can't set a global variable with the same name as an argument.
blog_table tells SQLAlchemy the table structure (called a schema), so that it can create and use the table. It's possible to autoload the schema from an existing database table rather then specifying the columns ourselves, but that depends on a live connection so it would have to be done in init_app. blog_table.c contains the table columns as attributes.
Blog is our object-oriented class we'll use to access the table. We could also access blog_table directly at the SQL level, but that is not the subject of this tutorial. The orm.mapper call links the table to our Blog class.
The order_by argument means all queries will return Blog objects in descending date order; i.e., most recent first.
meta is an object SQLAlchemy uses to hold information about tables. If you had multiple databases with overlapping table names, you'd need one MetaData object for each database.
Session is a SQLAlchemy session manager, used to coordinate access to mapped classes like Blog. It is not related to pylons.session, which is an HTTP session. Session is called a "scoped session" in the SQLAlchemy manual. That means it's not a session object itself but a thread-safe wrapper around it. Don't worry about the difference. Session's class methods call the same-named methods in hidden session objects; that's all you need to know.
autoflush=True means SQLAlchemy will automatically write changes to the database so we don't have to call Session.save(). transactional=True puts all database access in a SQL transaction, so we'll call Session.commit() to save our changes permanently or Session.rollback() to discard them. These flags are usually both true or both false in applications. MySQL MyISAM tables are non-transactional so you can't actually roll back changes on them; you'll get an error if you call .rollback in this case.
bind=engine tells the session to use this database for all database access. If we had some tables in one database and other tables in another database, we'd use the binds argument instead.
Now edit ~/MyBlog/myblog/websetup.py so it looks like this:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 | """Setup the MyBlog application"""
import logging
from paste.deploy import appconfig
from pylons import config
from myblog.config.environment import load_environment
from myblog import model
log = logging.getLogger(__name__)
def setup_config(command, filename, section, vars):
"""Place any commands to setup myblog here"""
conf = appconfig('config:' + filename)
load_environment(conf.global_conf, conf.local_conf)
log.info("Creating database tables")
model.meta.create_all(bind=model.engine)
log.info("Finished setting up")
|
Replace the WSGIController.__call__ line with this:
1
2
3
4
5
6 | # (ignore this line; it's to fix the indentation)
def __call__(self, environ, start_response):
try:
return WSGIController.__call__(self, environ, start_response)
finally:
model.Session.remove()
|
This prevents session data from "leaking" from one Web request to the next.
Now run the following the command in ~/MyBlog:
1 | paster setup-app development.ini
|
This will configure the above blog database and we can move on to templates and some actual Python!
|
If you get an error saying the database doesn't exist, you'll have to create it in a database-specific manner. MySQL uses the command "mysqladmin create DATABASE_NAME". You may also have to set access permissions using the "GRANT" statement in the mysql command-line tool. This is another reason to use SQLite when just starting out, because SQLite automatically creates the database file without any fuss. |
Step 2 - Putting the script together
Step 2.1 - The First Part
In Pylons, the application is based on a series of "controllers." They are like the actors of a play, each playing their part.
So let's make a blog controller!
Again in ~/MyBlog:
Now we need to give the actors their lines.
1
2
3
4
5
6
7
8
9
10
11
12 | import logging
from myblog.lib.base import *
log = logging.getLogger(__name__)
class BlogController(BaseController):
def index(self):
q = model.Session.query(model.Blog)
c.posts = q.limit(5)
return render("/blog/index.html")
|
This retrieves the last 5 blog posts in the database and passes them to the template via Pylons' c global. Actually it passes the unexecuted database query, which gives the template a bit more flexibility to ask different questions of the result set. q is a query referring to all records in the table. c.posts is a query referring to the first five records. To execute the query and get the results as a list of Blog objects, append .all() to it.
Step 2.2 - Making the template
We want to store our blog-related templates inside their own subdirectory called 'blog'. (This is not necessary; we could put them directly in the 'templates' directory.)
1 | mkdir ~/MyBlog/myblog/templates/blog # this creates the blog directory
|
Now create a new text file called index.html inside ~/MyBlog/myblog/templates/blog.
1
2
3
4
5
6
7
8
9
10
11
12
13 | <%inherit file="site.html" />
<%def name="title()">MyBlog Home</%def>
<p>${c.posts.count()} new blog posts!</p>
% for post in c.posts:
<p class="content" style="border-style:solid;border-width:1px">
<span class="h3"> ${post.subject} </span>
<span class="h4">Posted on: ${post.date} by ${post.author}</span>
<br>
${post.content}
</p>
% endfor
|
The for statement runs through all the posts found by the query and prints them out. The .count() call is just a silly example of asking another question on the same query.
Now create a site template with boilerplate HTML for all your pages.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 | <%def name="title()"></%def>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>MyBlog: ${self.title()}</title>
</head>
<body>
<h1>${self.title()}</h1>
<!-- *** BEGIN page content *** -->
${self.body()}
<!-- *** END page content *** -->
</body>
</html>
|
(The author refuses to use XHTML, believing it's a dead end and HTML 5 will supercede it.)
The syntax and template inheritance is explained in the excellent Mako manual.
The only tricky thing here is the "title" function, used to make the page's title accessible to the site template. Hopefully Mako will evolve a better way to do this.
|
This example displays any HTML markup the user enters in their blog post, which is a security hole if the user is untrusted. See Mako's "h" filter for a solution to this. |
Step 2.3 - Transaction log
This step is optional but is useful for debugging. It prints an Apache-style log line whenever somebody makes a Web request to the application.
At the top of the file put:
1 | from paste.translogger import TransLogger
|
Below "# CUSTOM MIDDLEWARE HERE" put:
1
2
3
4 | format = ('%(REMOTE_ADDR)s - %(REMOTE_USER)s [%(time)s] '
'"%(REQUEST_METHOD)s %(REQUEST_URI)s %(HTTP_VERSION)s" '
'%(status)s %(bytes)s')
app = TransLogger(app, format=format, logger_name="access")
|
Step 3 - The first rehearsal: Testing the application
Run the application using (inside ~/MyBlog):
Now navigate to: http://localhost:5000/blog
This will print out the latest five blog posts in the database (in order!). Of course there aren't any posts yet, so it simply displays "0 blog posts".
Because we used "--reload", the HTTP server will restart whenever we modify a file. When you get bored, type your system's stop character (ctrl-C on Unix, maybe ctrl-Z on Windows or command-. on Macintosh) to quit the server. You may have to stop and restart the server manually if paster doesn't realize you've added a module.
Step 3.1 - Home page
If you navigate to http://localhost:5000/, you'll get a default home page. To fix this, delete the file ~/MyBlog/myblog/public/index.html. Now you'll get a Not Found error, which is not much better. What you'd like instead is the list of new blog posts, the same as "/blog" outputs. Add a routing rule going to that action.
Under "# CUSTOM ROUTES HERE", add this line:
1 | map.connect('', controller='blog', action='index')
|
You'll see there's already a route underneath it: "map.connect(':controller/:action/:id')'. This is what's making all our other URLs work. It says that any URL that looks like "/CONTROLLER_NAME/ACTION_NAME" should be routed to that controller and action. (For security it won't call any action that begins with an underscore (_), but instead return a "Not Found" error.) The "id" field is not used in our examples; you could use it with a URL like "/blog/show/1" for instance.
Step 4 - Adding to the cast!
Step 4.1 - Acting Parts for Stagehands (The admin frontend)
Now, choose a suitable name for your admin toolkit to live in. In this tutorial I'll use "toolkit". from inside ~/MyBlog do:
1 | paster controller toolkit
|
Now you have a blank controller that will just give you a "hello world" on http://127.0.0.1:5000/toolkit/
Why do we want a separate controller and not just add it to the blog controller? It's easier to force different permission levels onto a whole controller, uses less code, etc..
Step 4.2 - Adding Content
Now we have an empty toolkit, what do we want in it? I've been told some people actually use a Web interface to do things, so we'll start by making an "Add a Blog Post" page. To make it simple for now, we'll leave the pretty templates and centralised linking for later.
We want to store our toolkit-related templates inside their own directory called 'toolkit'
We start with to simple templates called "index.mako" and an "add.mako".
