Pylons, PostgreSQL, and Simpycity in 60 Minutes or Less
Posted Thursday Jun 18th, 2009 10:50am
by Lacey Powers | Permalink
This is a tutorial to show you the basics of creating a small project within Pylons, with Simpycity. This tutorial assumes that you have already set up a Linux development environment for Pylons. Preferably on Ubuntu, though these directions should be general enough for any other Linux environment, meaning that you have installed Apache2, Python, mod-wsgi, and PostgreSQL 8.3. Choose a working directory for your project. For this example, I am using "project" for my working directory. Create the working directory, if it doesn't already exist.
blog comments powered by Disqus
Posted Thursday Jun 18th, 2009 10:50am
by Lacey Powers | Permalink
|
This is a tutorial to show you the basics of creating a small project within Pylons, with Simpycity. This tutorial assumes that you have already set up a Linux development environment for Pylons. Preferably on Ubuntu, though these directions should be general enough for any other Linux environment, meaning that you have installed Apache2, Python, mod-wsgi, and PostgreSQL 8.3. Choose a working directory for your project. For this example, I am using "project" for my working directory. Create the working directory, if it doesn't already exist.
mkdir projectMove into the working directory.
cd projectCreate your project:
paster create -t pylons helloworldThis should give you a set of choices.
Enter template_engine (mako/genshi/jinja2/etc: Template language) ['mako']: genshiFor templating, choose "genshi".
Enter sqlalchemy (True/False: Include SQLAlchemy 0.5 configuration) [False]: FalseInclude sqlalchemy. "False". We will be using Simpycity instead. You should have a directory that looks like this:
lacey@blinky:~/project$ ls -l total 4.0K drwxr-xr-x 5 lacey lacey 4.0K 2009-06-05 08:46 helloworld lacey@blinky:~/project$cd into the helloworld project.
cd helloworldThe base directory should look like this:
lacey@blinky:~/project/helloworld$ ls -l total 48K -rw-r--r-- 1 lacey lacey 1.6K 2009-06-05 08:46 development.ini drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 docs -rw-r--r-- 1 lacey lacey 9.5K 2009-06-05 08:46 ez_setup.py drwxr-xr-x 9 lacey lacey 4.0K 2009-06-05 08:46 helloworld drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 helloworld.egg-info -rw-r--r-- 1 lacey lacey 125 2009-06-05 08:46 MANIFEST.in -rw-r--r-- 1 lacey lacey 463 2009-06-05 08:46 README.txt -rw-r--r-- 1 lacey lacey 597 2009-06-05 08:46 setup.cfg -rw-r--r-- 1 lacey lacey 959 2009-06-05 08:46 setup.py -rw-r--r-- 1 lacey lacey 509 2009-06-05 08:46 test.ini lacey@blinky:~/project/helloworld$This is the basic enviroment for your "Hello World" project. There are several important files and directories here. To avoid confusion, I will only explain the most important, and most used files within the project.
setup.py -- A command and control file for the project.
With setup.py, you can run unit tests, update the packages
associated with the project, and package it into a Python .egg.
test.ini -- The test configuration file for the project.
This file mostly inherits from the development.ini file, but if
you want to have testing specific settings for this project,
you put them here. For now, this will just inherit
from development.ini.
development.ini -- This file contains basic configuration of the project.
This is where you toggle debugging settings, add database
configuration information, and so forth.
We need to customize this environment to use Simpycity, a simple and effective ORM based on stored procedures and queries.
First, we edit the "setup.py" file. You can use your preferred text editor to open this file, e.g. Vim, Emacs, Joe, Nano, Gedit, ect. For this example, we will use the basic editor "nano".
nano setup.pyThere are only a few options that need to be modified in the base setup.py file. Note the section that looks like this:
install_requires=[
"Pylons>=0.9.7",
"Genshi>=0.4",
],
This section governs the requirements for your particular project. This currently shows that a version of Pylons of 0.9.7 or greater, and a version of Genshi of 0.4 or greater is required to for this project.
Since we are using Simpycity for our ORM, we need to add a line for Simpycity.
install_requires=[
"Pylons>=0.9.7",
"Genshi>=0.4",
"Simpycity>=0.2.1"
],
The number 0.2.1 is the current version of Simpycity.
Since new features and bug fixes are added to Simpycity regularly, you should check for updated versions regularly.
To check that the version of Simpycity is up to date, please go
Down in the middle of the page, there is an area marked "Download".
The first item in the download area is the current version of Simpycity.
(Which at the time of writing this tutorial is 0.2.1, so be sure to check.)
The current release version of Simpycity is 0.2.1, which can be downloaded from hereAll that you need to do to keep Simpycity up to date is copy that version number, and paste over the version number in the install_requires stanza in setup.py. After that, there is one last thing we need to add to the setup.py file Near the bottom, before the closing parenthesis, we need to add another snippet of code.
dependency_links=[
"https://projects.commandprompt.com/public/simpycity/repo/dist/"
]
This is the link to the Simpycity repository at Command Prompt.
easy_install will use that URI, combined with the version number (in this case 0.2.1) to locate and install the proper Simpycity .egg file.
Now, we save, and exit from setup.py.
Following the insertion of those lines, we will do one last check.
Sometimes, the newest python packages with easy_install require that they be built on the machine before they are deployed.
This is the case with newer versions of psycopg2. To ensure that all of the build requirements are present, run the following command:
aptitude search postgresql-server-dev-8.3 python2.5-dev build-essentialWhich should return like this:
lacey@blinky:~/project/helloworld$ aptitude search postgresql-server-dev-8.3 python2.5-dev build-essential i A build-essential - Informational list of build-essential packages i postgresql-server-dev-8.3 - development files for PostgreSQL 8.3 server-side programming i python2.5-dev - Header files and a static library for Python (v2.5) lacey@blinky:~/project/helloworld$If it does not, then running the following command:
sudo aptitude install postgresql-server-dev-8.3 python2.5-dev build-essentialWill install the essential libraries for you. Now, we run the following command:
sudo python setup.py developThis will check and process all of the dependencies for your project, including Simpycity. This process will install the dependencies required for your project, so that they may be used within the project. Now that we have Simpycity installed, we pause to do a bit of database setup. With sudo become the postgres user.
sudo su - postgresIt will ask you for your password. Success should look something like this.
lacey@blinky:~/project/helloworld$ sudo su - postgres [sudo] password for lacey: postgres@blinky:~$Now, we run a PostgreSQL command to create a user.
postgres@blinky:~$ psql -U postgres
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=#
You are now on the postgresql command line.
While there, run the following commands.
CREATE USER helloworld WITH ENCRYPTED PASSWORD '12345'; CREATE DATABASE helloworld WITH OWNER helloworld;The successful execution of these two commands should look like this:
postgres=# CREATE USER helloworld WITH ENCRYPTED PASSWORD '12345'; CREATE ROLE postgres=# CREATE DATABASE helloworld WITH OWNER helloworld; CREATE DATABASE postgres=#Exit PostgreSQL with the \q command. This should drop you back to the command line.
postgres=# \q postgres@blinky:~$If you haven't already, take a moment to modify your pg_hba.conf so you can easily log in.
nano /etc/postgresql/8.3/main/pg_hba.confNear the bottom, there should be a line that looks like *exactly* like this.
local all all ident sameuserThe very last part, "ident sameuser" should be changed to md5, so that the line looks like this:
local all all md5Save and quit. Now, execute the command:
/etc/init.d/postgresql-8.3 force-reloadThis will reload the settings that you changed.
postgres@blinky:~$ /etc/init.d/postgresql-8.3 force-reload * Reloading PostgreSQL 8.3 database server [ OK ] postgres@blinky:~$Exit the postgres user environment with the exit command.
postgres@blinky:~$ exit logout lacey@blinky:~/project/helloworld$Now we should be back in the "helloworld" project directory. Next, as a convenience, we will create a .pgpass file. cd to your home directory.
cd ~
nano .pgpassWhen the editor window comes up, add the following line.
localhost:5432:*:helloworld:12345Save and quit. Following that, execute the following command.
chmod 600 .pgpassThis will appropriately set the permissions for your user. You can check them with the following command.
lacey@blinky:~$ ls -l .pgpass -rw------- 1 lacey lacey 135 2009-06-05 12:10 .pgpass lacey@blinky:~$Now, there is the final test. Execute the following command.
psql -U helloworldIf everything has been successfully set up, your terminal screen should look like this:
lacey@blinky:~$ psql -U helloworld
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
helloworld=>
With the command logging you in without issue, or a password prompt.
While we are here, in PostgreSQL, we should issue the following command:
CREATE LANGUAGE plpgsql;Again, success should look like this:
helloworld=> CREATE LANGUAGE plpgsql; CREATE LANGUAGE helloworld=>And again, we exit with "\q".
helloworld=> \q lacey@blinky:~$Now, we return to the project directory with the following command.
cd project/helloworld/In this directory, we edit development.ini
nano development.iniAt line 36, uncomment the line set debug = false, and add the following lines so that the section of the configuration file looks like this:
db.database = helloworld db.user = helloworld db.host = localhost db.port = 5432 db.password = 12345These are the values that Simpycity requires for the database connections.
database = a database that belongs to the user. user = the user that you are connecting as. host = the server being connected to. localhost for a development machine. port = the port that PostgreSQL expects you to connect on. password = the password for the user.Before we go on, a little more explanation is needed, so that you better understand what is going on in later steps. You may have noticed the "helloworld" directory within the project earlier.
lacey@blinky:~/project/helloworld$ ls -l total 48K -rw-r--r-- 1 lacey lacey 1.6K 2009-06-05 08:46 development.ini drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 docs -rw-r--r-- 1 lacey lacey 9.5K 2009-06-05 08:46 ez_setup.py drwxr-xr-x 9 lacey lacey 4.0K 2009-06-05 08:46 helloworld drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 helloworld.egg-info -rw-r--r-- 1 lacey lacey 125 2009-06-05 08:46 MANIFEST.in -rw-r--r-- 1 lacey lacey 463 2009-06-05 08:46 README.txt -rw-r--r-- 1 lacey lacey 597 2009-06-05 08:46 setup.cfg -rw-r--r-- 1 lacey lacey 959 2009-06-05 08:46 setup.py -rw-r--r-- 1 lacey lacey 509 2009-06-05 08:46 test.ini lacey@blinky:~/project/helloworld$This directory contains all of your project specific code is placed. If you issue the following commands:
cd helloworld ls -lYou will note that the directory has the following structure:
lacey@blinky:~/project/helloworld/helloworld$ ls -l total 32K drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 config drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 controllers -rw-r--r-- 1 lacey lacey 0 2009-06-05 08:46 __init__.py drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 lib drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 model drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 public drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 templates drwxr-xr-x 3 lacey lacey 4.0K 2009-06-05 08:46 tests -rw-r--r-- 1 lacey lacey 296 2009-06-05 08:46 websetup.py lacey@blinky:~/project/helloworld/helloworld$The directories here break down as follows:
config -- Configuration files for your application as a whole.
controllers -- This is where most of the application logic goes.
The files here control what you display, and how you display it.
lib -- This is where miscellaneous files that are necessary for your
application, but have no distinct place go.
model -- This is where the files related to the ORM go.
This will be where most of the files related
to the database and Simpycity will live.
public -- This is where static html, docs, css, and such live.
templates -- This is where Genshi will create the dynamic html templates
for the dynamic pages that you will use.
__init__.py -- This is a helper file for packaging your application.
That will be covered later in a different tutorial.
tests -- This is where your unit tests live.
These test controllers and other application functionality.
websetup.py -- This is another helper file for packaging your application.
It too will be covered later in a different tutorial.
Now that you have an overview of the directories within a Pylons project, we can start making some changes within this directory and its child directories.
First, we should make an important edit to lib/base.py, related to Simpycity.
nano lib/base.pyThe contents of the file will look like this.
"""The base Controller API
Provides the BaseController class for subclassing.
"""
from pylons.controllers import WSGIController
from pylons.templating import render_genshi as render
class BaseCoOne line ntroller(WSGIController):
def __call__(self, environ, start_response):
"""Invoke the Controller"""
# WSGIController.__call__ dispatches to the Controller method
# the request is routed to. This routing information is
# available in environ['pylons.routes_dict']
return WSGIController.__call__(self, environ, start_response)
We will add the following lines, to fix a troublesome issue before it starts.
For the curious, more detail can be found here at Aurynn Shaw's blog:
For purposes of this tutorial, it should be sufficient to say that this will allow Pylons and mod_wsgi to properly close and clean up connections to the database, so that connections are not left hanging idle in transaction.
So this is very important to include in any project.
import psycopg2
import psycopg2.extensions
psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
from simpycity.handle import Manager
m = Manager()
try:
return WSGIController.__call__(self, environ, start_response)
finally:
# Shut down *all* the extant handles.
m.close()
We paste this in so that the controller code will look like this:
One line
"""The base Controller API
Provides the BaseController class for subclassing.
"""
from pylons.controllers import WSGIController
from pylons.templating import render_genshi as render
class BaseController(WSGIController):
def __call__(self, environ, start_response):
"""Invoke the Controller"""
# WSGIController.__call__ dispatches to the Controller method
# the request is routed to. This routing information is
# available in environ['pylons.routes_dict']
#return WSGIController.__call__(self, environ, start_response)
import psycopg2
import psycopg2.extensions
psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
from simpycity.handle import Manager
m = Manager()
try:
return WSGIController.__call__(self, environ, start_response)
finally:
# Shut down *all* the extant handles.
m.close()
Save, and exit nano.
Next we edit environment.py
One line nano config/environment.pyWithin this file, at the very end, below the lines:
# CONFIGURATION OPTIONS HERE (note: all config options will override # any Pylons config options)we add the follwing lines, for Simpycity setup.
app_conf = config['app_conf'] db_config.port = app_conf['db.port'] db_config.database= app_conf['db.database'] db_config.host= app_conf['db.host'] db_config.user = app_conf['db.user'] db_config.password = app_conf['db.password'] db_config.debug = FalseAnd at the top of environment.py, with the rest of the imports, we add the following line.
from simpycity import config as db_configSo that the proper configuration options are read. It should be noted here, if you need to see the debugging options from Simpycity itself, you should set db_config.debug to True. Having added the proper configuration parameters to base.py and environment.py, we can start looking at how to serve content from Pylons. There are two basic ways to serve content through Pylons:
1. To server static content from the public/ directory. 2. Use a controller, which contains the logic for what to control and how to display it.The trivially easy case is static content. Execute the following command.
nano helloworld/public/hello.htmlPaste the following content.
<html>
<body>
Hello World!
</body>
</html>
Save and exit.
This creates a basic static html file.
Now, from our current directory, execute the following command
paster serve --reload development.iniThis command starts the Pylons server, and is great for a bit of quick debugging, or if you haven't got Apache installed or configured. Entering the following URI into your web browser:
http://localhost:5000/hello.htmlYou should see a simple page with the words "Hello World!". Now, stop the server on the command line with <ctrl>-<c>. We are moving on to the more interesting and complicated case of using a controller to serve content within Pylons. Execute the following command:
paster controller helloSuccessful execution of this command should look something like this:
lacey@blinky:~/project/helloworld$ paster controller hello Creating /home/lacey/project/helloworld/helloworld/controllers/hello.py Creating /home/lacey/project/helloworld/helloworld/tests/functional/test_hello.pyThis is an interesting and useful command. As expected, it created a file called hello.py in the helloworld/controllers directory, but it also did a wonderful thing for us, and created a file in helloworld/tests/functional/test_hello.py to contain the specific unit tests for the hello.py controller. Executing the following command:
nano helloworld/controller/hello.pyShows us the contents of the hello.py controller, which are very basic.
import logging
from pylons import request, response, session, tmpl_context as c
from pylons.controllers.util import abort, redirect_to
from helloworld.lib.base import BaseController, render
log = logging.getLogger(__name__)
class HelloController(BaseController):
def index(self):
# Return a rendered template
#return render('/hello.mako')
# or, return a response
return 'Hello World'
Exiting from Nano, we can also have a look at test_hello.py by executing a similar command.
nano helloworld/tests/functional/test_hello.pyThe contents of which are:
from helloworld.tests import *
class TestHelloController(TestController):
def test_index(self):
response = self.app.get(url(controller='hello', action='index'))
# Test response...
The paster command has created a basic framework for a unit test of the hello.py controller. We will come back to this later in the document.
For right now, we want to see the controller in action.
But there is a bit more setup that we need to do first.
Execute the following command:
nano helloworld/config/routes.pyThis should bring up a file that looks like this:
"""Routes configuration
The more specific and detailed routes should be defined first so they
may take precedent over the more generic routes. For more information
refer to the routes manual at http://routes.groovie.org/docs/
"""
from pylons import config
from routes import Mapper
def make_map():
"""Create, configure and return the routes Mapper"""
map = Mapper(directory=config['pylons.paths']['controllers'],
always_scan=config['debug'])
map.minimization = False
# The ErrorController route (handles 404/500 error pages); it should
# likely stay at the top, ensuring it can always be resolved
map.connect('/error/{action}', controller='error')
map.connect('/error/{action}/{id}', controller='error')
# CUSTOM ROUTES HERE
map.connect('/{controller}/{action}')
map.connect('/{controller}/{action}/{id}')
return map
This is another relatively spartan, but highly important file. Within this file, the routes for the application are defined. Routes are what translates the URI within the browser into directions in the application, which point data at a specific controller, for manipulation.
This may sound a bit obtuse right now, but at the end of this particular example, it should be clear.
Currently, if you were to restart Pylons, with the aformentioned command....
paster serve --reload development.iniAnd attempted to browse to the URI:
http://localhost:5000/helloYou would be presented with a very cheery orange-yellow, and black 404 page. This is because you haven't specified the route. Without a route, Pylons has no idea what you want. So, to let it know that we want to see what is within the HelloController (located in helloworld/controller/hello.py) we will add the following line to helloworld/config/routes.py :
map.connect('hello', '/hello', controller='hello', action='index')
The first item in map.connect gives the route the name "hello" (this will be handy later). The second part is what maps the part of the URI that you put in the search bar as in:
http://localhost:5000/helloNow, when we run:
paster serve --reload development.iniAnd attempted to browse to the URI:
http://localhost:5000/helloWe will see the words "Hello World". Note that this is different from our static html entry.
http://localhost:5000/hello.htmlWhich says "Hello World!". And both are available. Moving to a more generic route....
http://localhost:5000Will show you a very cheery welcome page, with the same orange-yellow and black color scheme, with links. This is served from helloworld/public/public.html Now, that is all fine for an example, but in an actual production application, you don't want someone to see "Welcome To Pylons" when they browse to your root URI. So, lets fix this. First, stop the server on the command line with <ctrl>-<c>. Execute the command:
nano helloworld/config/routes.pyAt the bottom, just before "return map", we will add the following line.
map.connect('root', '/', controller='hello', action='index')
Why do we add this way down there, you might ask? Because the Pylons developers left us with a handy comment at the top of this file.
The more specific and detailed routes should be defined first so they may take precedent over the more generic routes.Taking heed of this advice, and realizing that '/' is the most generic route that you can have, we place this at the very bottom so it is chosen last in the evaluation of routes. Again, we restart the server, from the ~/projects/hellworld directory.
paster serve --reload development.iniAnd browse to:
http://localhost:5000....We still see the cheery orange-yellow and black welcome page. (Be sure to shut down the server with <ctrl>-<c>) Why is that!?!? Well, there's an interesting thing about Pylons. It will evaluate the static content in helloworld/public first. In this case, it is index.html. If we poke around a bit, we find the culprit in helloworld/config/middleware.py on line 67.
app = Cascade([static_app, app])This line basically says that, look for static content first, and evaluate that over the dynamic application content. There are two ways to solve this particular issue. 1. Execute the following command:
mv helloworld/public/index.html helloworld/public/index.html.dont_evaluate_me_pylonsIf you do that, Pylons won't be able to find it, and will default to the route set in routes.py. In this case, if you restart the server, and browse to the aformentioned URI, you will see Hello World (without the exclamation point). 2. Swap the order of Cascade. Since the Cascade command defines whether or not you evaluate static content or dynamic content first, swapping the order will mean that dynamic content is evaluated first, and static content second. So, we execute the following command:
mv helloworld/public/index.html.dont_evaluate_me_pylons helloworld/public/index.htmlTo return the file back to its original state. If we are incorrect, this will show us by displaying that cheery welcome page again. So, we execute the following command:
nano helloworld/config/middleware.pyOnce there, we will copy line 67, and paste it directly below itself. Then we will comment out line 67 (we have the original line for reference), so that the file now looks like this:
#app = Cascade([static_app, app]) app = Cascade([app, static_app])With the order of app, and static_app swapped. In this case, if you restart the server, and browse to the aformentioned URI, you will see Hello World (without the exclamation point). And index.html is still happily sitting in helloworld/public
lacey@blinky:~/project/helloworld$ ls -l helloworld/public/index.html -rw-r--r-- 1 lacey lacey 4.6K 2009-06-05 08:46 helloworld/public/index.htmlNow, even though we're using the HelloController in hello.py, this is still little better than a static application. So, now, we will work on making hello.py dynamic, letting it read and write to and from the database by using Simpycity. First, we will need a simple set of tables and functions for use in PostgreSQL. We are currently still in the ~/project/helloworld directory. We are going to move one directory back.
cd ..And make a new directory called 'sql'.
mkdir sqlNow, we consider the following table and functions, already designed and provided for you to use. These will be the examples that we use for the rest of the tutorial. This is a very simple table, that contains ways to say "Hello" in different languages.
CREATE TABLE hello
(
salutation text not null primary key,
language text not null
);
-- Various formal ways of saying hello or good morning.
INSERT INTO hello (salutation, language) VALUES ('hello','English');
INSERT INTO hello (salutation, language) VALUES ('hola','Spanish');
INSERT INTO hello (salutation, language) VALUES ('bonjour','French');
INSERT INTO hello (salutation, language) VALUES ('merhaba selam','Turkish');
INSERT INTO hello (salutation, language) VALUES ('zdravstvuyte','Russian');
INSERT INTO hello (salutation, language) VALUES ('buon giorno','Italian');
CREATE OR REPLACE FUNCTION add_salutation
(in_salutation text, in_language text)
RETURNS boolean AS $BODY$
DECLARE
BEGIN
INSERT INTO hello (salutation, language)
VALUES (in_salutation, in_language);
RETURN TRUE;
END;
$BODY$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION remove_salutation
(in_salutation text, in_language text)
RETURNS boolean AS $BODY$
DECLARE
BEGIN
DELETE FROM hello
WHERE salutation = in_salutation
AND language = in_language;
RETURN TRUE;
END;
$BODY$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION update_salutation
(in_old_salutation text, in_new_salutation text)
RETURNS boolean AS $BODY$
DECLARE
BEGIN
UPDATE hello
SET salutation = in_new_salutation
WHERE salutation = in_old_salutation;
RETURN TRUE;
END;
$BODY$ LANGUAGE PLPGSQL;
Execute the following command, in your ~/project/sql directory.
nano hello.sqlPaste the above SQL code into that file, save and quit. Then run the following command.
psql -U helloworld helloworld -f hello.sqlSuccess should look like this.
lacey@blinky:~/project/sql$ psql -U helloworld helloworld -f hello.sql psql:hello.sql:5: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "hello_pkey" for table "hello" CREATE TABLE INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION lacey@blinky:~/project/sql$Now that the necessary tables, functions, and data are in place in PostgreSQL, we can move on to making our hello model. Execute the following command to move back into your helloworld project directory.
cd ../helloworld/helloworldThe directory you are in should look like this, if you execute the command ls -l :
lacey@blinky:~/project/helloworld/helloworld$ ls -l total 36K drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 16:52 config drwxr-xr-x 2 lacey lacey 4.0K 2009-06-06 18:57 controllers -rw-r--r-- 1 lacey lacey 0 2009-06-05 08:46 __init__.py -rw-r--r-- 1 lacey lacey 140 2009-06-05 15:07 __init__.pyc drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 15:26 lib drwxr-xr-x 2 lacey lacey 4.0K 2009-06-06 19:35 model drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 16:40 public drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 templates drwxr-xr-x 3 lacey lacey 4.0K 2009-06-05 08:46 tests -rw-r--r-- 1 lacey lacey 296 2009-06-05 08:46 websetup.py lacey@blinky:~/project/helloworld/helloworld$Recall that earlier, when discussing the structure of the projects, that the "model" directory was where the files related to your ORM are placed. We are going to create our model for our Hello World project. Execute the following command:
nano model/hello_model.pyAnd paste the following code into it:
from simpycity.core import Function, Raw
from simpycity.model import SimpleModel, Function, Construct
from simpycity.core import Raw
class HelloModel(SimpleModel):
# Getters
get_salutation = Raw("SELECT salutation
FROM public.hello
ORDER BY random() LIMIT 1")
# Setters
add_salutation = Function("public.add_salutation",
['salutation','language'])
upd_salutation = Function("public.update_salutation",
['old_salutation','new_salutation'])
# Deleters
del_salutation = Function("public.remove_salutation",
['salutation','language'])
This code sets up Simpycity for use in our application.
At the top, are the required imports from Simpycity.
There are two basic ways that Simpycity interacts with the PostgreSQL database beneath it.
Function is how Simpycity maps Python to the stored procedures in the database.
Looking at this line:
add_salutation = Function("public.add_salutation",['salutation','language'])
The double-quoted portion of this function, "public.add_salutation", is the schema qualified name of the stored procedure we are looking to map to.
The bracketed portion of this function, ['salutation','language'], map the arguments to the function.
For example, when we call this function:
return_status = HelloModel.add_salutation("sawadee ka","Thai")
Will map to "SELECT * FROM public.add_salutation('sawadee ka','Thai')", which will insert the salutation "namaste" and the language "hindi" into the database, and return true, which sets the value of "return_status" to true.
Raw is the Simpycity way of mapping straight SQL to the database.
get_salutation = Raw("SELECT salutation
FROM public.hello
ORDER BY random() LIMIT 1")
For example, calling this function:
return_item = HelloModel.get_salutation()
Raw simply executes the provided query, which in this example, is:
SELECT salutation FROM public.hello ORDER BY random() LIMIT 1;Returning a random way to say hello into the variable "return_item". Now, we need to modify the Hello Controller. Execute the command:
nano controller/hello.pyThe contents of which are:
import logging
from pylons import request, response, session, tmpl_context as c
from pylons.controllers.util import abort, redirect_to
from helloworld.lib.base import BaseController, render
log = logging.getLogger(__name__)
class HelloController(BaseController):
def index(self):
# Return a rendered template
#return render('/hello.mako')
# or, return a response
return 'Hello World'
We will add:
from helloworld.model.hello_model import HelloModeland:
salutation = HelloModel.get_salutation(options=dict(fold_output=True)) return salutationTo the hello controller, and we will comment out the line "return 'Hello World'", so that our controller now looks like this:
import logging
from pylons import request, response, session, tmpl_context as c
from pylons.controllers.util import abort, redirect_to
from helloworld.lib.base import BaseController, render
from helloworld.model.hello_model import HelloModel
log = logging.getLogger(__name__)
class HelloController(BaseController):
def index(self):
# Return a rendered template
#return render('/hello.mako')
# or, return a response
#return 'Hello World'
salutation = HelloModel.get_salutation(options=dict(fold_output=True))
return salutation
Now, combining the database functions we created, the HelloModel that we created, and this code, we will be able to query the database.
To see the code in action, all we have to do is start the server again.
So we execute the command
cd ..And then execute:
paster serve --reload development.iniAnd open our browsers to http://localhost:5000/ This should show you a randomly chosen way to say hello. Pressing the refresh button will show you another, and another. Now, let's add a bit to it with a Genshi Template. Press <ctrl>-<c> to stop your server. Now, we refer back to the directory layout of the project.
lacey@blinky:~/project/helloworld/helloworld$ ls -l total 36K drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 16:52 config drwxr-xr-x 2 lacey lacey 4.0K 2009-06-06 18:57 controllers -rw-r--r-- 1 lacey lacey 0 2009-06-05 08:46 __init__.py -rw-r--r-- 1 lacey lacey 140 2009-06-05 15:07 __init__.pyc drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 15:26 lib drwxr-xr-x 2 lacey lacey 4.0K 2009-06-06 19:35 model drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 16:40 public drwxr-xr-x 2 lacey lacey 4.0K 2009-06-05 08:46 templates drwxr-xr-x 3 lacey lacey 4.0K 2009-06-05 08:46 tests -rw-r--r-- 1 lacey lacey 296 2009-06-05 08:46 websetup.py lacey@blinky:~/project/helloworld/helloworld$The templates directory is where all of our dynamically generated page templates (created by Genshi, as you recall from the very beginning of this tutorial). So we should execute the following command.
cd helloworld/templatesNow, we are going to look at the contents of this directory.
lacey@blinky:~/project/helloworld/helloworld/templates$ ls -l total 0 -rw-r--r-- 1 lacey lacey 0 2009-06-05 08:46 __init__.py lacey@blinky:~/project/helloworld/helloworld/templates$This only contains a blank __init__.py file. This is only because the Genshi templating engine within Pylons will ignore any template directories without an __init__.py in them. Armed with that knowledge, we will create our own directory.
mkdir helloWe will move into it:
cd helloand we will make a blank __init__.py file.
touch __init__.pyWe will also make another file.
nano hello.htmlAnd within that we will paste the following contents.
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>${c.hello}</title>
</head>
<body class="index">
<div id="header">
<h1>${c.hello}</h1>
</div>
<p>That is hello in: ${c.language}</p>
</body>
</html>
Save and quit.
This is a very basic xHTML Genshi template.
Aside from having syntax for dynamically setting variables, Genshi templates can be treated the same as any other xHTML page.
Within this template are dynamically setting the title, a header, a small bit of content.
${c.hello} And ${c.language} are variables within the template, that we will set within our controller.
For more information on the Genshi templates, please consult the documentation, found here
But before we render this page, we need to make a few more modifications.
We move out of this directory, into the model directory.
cd ../../modelAnd open the file hello_model.py. We will add the following content.
get_language = Raw("SELECT language
FROM public.hello
WHERE salutation = %s", ['salutation']);
This is another variation of the Raw function, in which we are able to substitute variables into the query.
Save and quit.
Lastly, we move to the controller directory, and modify our controller.
cd ../controller
nano hello.pyWe will add the following import:
from pylons.templating import render_genshi as renderWhich now brings the render_genshi function into our controller, and aliases it to render. We will also comment out "return salutation" #return salutation We will add the following lines:
c.language = HelloModel.get_language(salutation,
options=dict(fold_output=True))
c.hello = salutation
return render("hello/hello.html");
This should leave the file looking like this:
import logging
from pylons import request, response, session, tmpl_context as c
from pylons.controllers.util import abort, redirect_to
from helloworld.lib.base import BaseController, render
from helloworld.model.hello_model import HelloModel
from pylons.templating import render_genshi as render
log = logging.getLogger(__name__)
class HelloController(BaseController):
def index(self):
# Return a rendered template
#return render('/hello.mako')
# or, return a response
#return 'Hello World'
salutation = HelloModel.get_salutation(options=dict(fold_output=True))
c.language = HelloModel.get_language(salutation,
options=dict(fold_output=True))
c.hello = salutation
return render("hello/hello.html");
#return salutation
The c. prefix to the variables are special to Genshi, and let it know what variables it should be rendering.
We are still getting a salutation from the HelloModel.get_salutation() function.
Now, we are taking that result, and using it to get the language for the saluation, using the HelloModel.get_language() function.
We are then setting the c.hello variable to the same value as the salutation variable.
And the c.language variable is set to the return value of the HelloModel.get_language() function.
return render("hello/hello.html") tells Genshi where the file we want to render is in relation to the template directory.
If hello.html was simply in the template directory, the return statement would be: return render("hello.html")
But since it is in the hello directory, we have to represent the rest of the path to the xHTML template.
And for the HelloModel Simpycity functions, we add new parameter. options=dict(fold_output=True)
Since we know these are going to return a single row only, we are collapsing the output of the query, causing it to return a value into the variable that is ready to be used by Python.
Now, to test.
Again, we back out of this directory to the outermost helloworld directory, in order to start the server.
cd ../..
paster serve --reload development.iniAnd again, we browse to the page http:://localhost:5000 in our browser. Which should now look something like this:
bonjour That is hello in: FrenchNow, displaying dynamic content is only one part of dealing with the database. We need to be able to manipulate the content. Deleting and updating content within the database are both crucial parts. So, we're going to add the remaining parts to make this a well-rounded example. So we stop the server (<ctrl>-<c>, again), and we move to the helloworld/public directory.
cd helloworld/publicAnd we copy/paste the following files:
nano add_salutation.htmlAnd we paste the following code.
<html>
<body>
<form action="/hello/add_salutation" method="POST">
<p>
Add Salutation:<br/>
Salutation: <input type="text" name="salutation"><br/>
Language: <input type="text" name="language"><br/>
<input type="submit" value="submit">
</p>
</form>
</body>
</html>
Save and close.
nano modify_salutation.htmlAnd we paste the following code.
<html>
<body>
<form action="/hello/modify_salutation" method="POST">
<p>
Modify Salutation:<br/>
Old Salutation: <input type="text" name="old_salutation"><br/>
New Salutation: <input type="text" name="new_salutation"><br/>
<input type="submit" value="submit">
</p>
</form>
</body>
</html>
Save and close.
nano remove_salutation.htmlAnd we paste the following code.
<html>
<body>
<form action="/hello/remove_salutation" method="POST">
<p>
Remove Salutation:<br/>
Salutation: <input type="text" name="salutation"><br/>
Language: <input type="text" name="language"><br/>
<input type="submit" value="submit">
</p>
</form>
</body>
</html>
Save and close.
Each of these files are simple, static pages, that submit forms to different actions in the hello controller.
Each of the forms has two fields in which to specify the language and salutation.
In the case of modify_salutation.html, we specify an old and new salutation.
Plus, the ever helpful submit button.
Currently, though, the hello controller only contains the index action. So we will need to modify that.
We navigate to the controllers directory.
cd ../controllersAnd open hello.py for editing
nano hello.pyWe will add the following functions.
def add_salutation(self):
if 'salutation' in request.params:
if 'language' in request.params:
salutation = request.params['salutation']
language = request.params['language']
rs = HelloModel.add_salutation(salutation, language)
return_status = rs.next()[0]
rs.commit()
if return_status == True:
c.language = language
c.hello = salutation
response.status = '200 OK'
return render("hello/added_salutation.html")
else:
c.language = language
c.hello = salutation
return render("hello/error.html")
def remove_salutation(self):
if 'salutation' in request.params:
if 'language' in request.params:
salutation = request.params['salutation']
language = request.params['language']
rs = HelloModel.del_salutation(salutation, language)
return_status = rs.next()[0]
rs.commit()
if return_status == True:
c.hello = salutation
c.language = language
response.status = '200 OK'
return render("hello/removed_salutation.html")
else:
c.hello = salutation
c.language = language
return render("hello/error.html")
def modify_salutation(self):
if 'old_salutation' in request.params:
if 'old_salutation' in request.params:
old_salutation = request.params['old_salutation']
new_salutation = request.params['new_salutation']
rs = HelloModel.upd_salutation(old_salutation, new_salutation)
return_status = rs.next()[0]
rs.commit()
if return_status == True:
c.old = old_salutation
c.new = new_salutation
response.status = '200 OK'
return render("hello/modified_salutation.html")
else:
c.old = old_salutation
c.new = new_salutation
return render("hello/error.html")
Each of these functions does basically the same thing, with small variations.
They read the request parameters, and check for essential parameters in the request dict.
Then they pull the parameters out, use them to insert, update, or delete fields in the database, and check for success.
If the change is successful, there is a page showing what the change was, and if it was not successful, it gives you a very basic error message.
Both pages redirect you back to the main page that shows the various salutations and languages.
Now, in each of these functions, there are references to rendered pages that are returned. We will go construct those now.
cd ../templates/helloAnd we will create several files here as well:
nano added_salutation.htmlAnd we will paste the following code.
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>SUCCESS</title>
</head>
<body class="index">
<div id="header">
<h1>SUCCESS: Added:</h1>
</div>
<p>${c.hello}</p>
<p>${c.language}</p>
<p/>
<a href="/hello">Return home</a>
</body>
</html>
Save and quit.
nano modified_salutation.htmlAnd we will paste the following code.
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>SUCCESS</title>
</head>
<body class="index">
<div id="header">
<h1>SUCCESS: Modified:</h1>
</div>
<p>${c.old}</p>
<p>${c.new}</p>
<p/>
<a href="/hello">Return home</a>
</body>
</html>
Save and quit.
nano removed_salutation.htmlAnd we will paste the following code.
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>SUCCESS</title>
</head>
<body class="index">
<div id="header">
<h1>SUCCESS: Removed:</h1>
</div>
<p>${c.hello}</p>
<p>${c.language}</p>
<p/>
<a href="/hello">Return home</a>
</body>
</html>
Save and quit.
We will also add a few links to hello.html
nano hello.html
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>${c.hello}</title>
</head>
<body class="index">
<div id="header">
<h1>${c.hello}</h1>
</div>
<p>That is hello in: ${c.language}</p>
<p/>
<a href="add_salutation.html">Add a salutation</a><br/>
<a href="remove_salutation.html">Remove a salutation</a><br/>
<a href="modify_salutation.html">Modify a salutation</a>
</body>
</html>
Save and quit.
We will also add the following code to a new file called error.html
nano error.htmlAnd we will paste the following code.
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>ERROR</title>
</head>
<body class="index">
<div id="header">
<h1>ERROR: Failed to add/modify/delete</h1>
</div>
<p>${c.hello}</p>
<p>${c.language}</p>
<p/>
<a href="/hello">Return home</a>
</body>
</html>
Save and quit.
Finally, we add a couple of lines to hello.html
nano hello.html
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>${c.hello}</title>
</head>
<body class="index">
<div id="header">
<h1>${c.hello}</h1>
</div>
<p>That is hello in: ${c.language}</p>
<p/>
<a href="add_salutation.html">Add a salutation</a><br/>
<a href="remove_salutation.html">Remove a salutation</a><br/>
<a href="modify_salutation.html">Modify a salutation</a>
</body>
</html>
This allows us to access the static pages that are in helloworld/public.
Now, if you recall from eariler, Pylons needs to know how to point these pages at the appropriate controller and action.
And it does that through the routes defined in routing.py.
So we now go modify that.
cd ../../config
nano routing.pyAnd we add the following lines
map.connect('add_salutation',
'/hello/add_salutation',
controller='hello',
action='add_salutation',
conditions=dict(method=['POST']))
map.connect('remove_salutation',
'/hello/remove_salutation',
controller='hello',
action='remove_salutation',
conditions=dict(method=['POST']))
map.connect('modify_salutation',
'/hello/modify_salutation',
controller='hello',
action='modify_salutation',
conditions=dict(method=['POST']))
These routes will, point the pages at the appropriate controllers and actions, as advertised. The additional conditional dict specifies what sort of HTTP method (GET,POST,DELETE, ect) are supported for this URI. Here we are specifiying POST because all of the form methods associated with these URIs are POST.
And this should be the last set of modifications that we need to do to the application.
Now, we test our new code.
cd ../..Which should put us into the outermost helloworld directory, with development.ini, so that we can start the server.
paster serve --reload development.iniNow, browsing to
http://localhost:5000/helloShould show you a page like this:
hello That is hello in: English Add a salutation Remove a salutation Modify a salutationWith the bottom text being HTML links. Refreshing should take you through various incarnations of "hello" in different languages. Now, we will add an additional greeting. Click the link for Add a salutation. This should take you to another very simple page with a form and a submit button. Here, we are going to add hello for the Thai language. Saluation: sawadee ka Language: Thai So we copy those words into the appropriate form boxes, and click "Submit". We should be taken to a page that looks like this.
SUCCESS: Added: sawadee ka Thai Return homeAnd if we were to look in the database at this time, we would see the entry has been successfully entered into the database.
lacey@blinky:~/project/sql$ psql -U helloworld
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
helloworld=> SELECT * FROM hello;
salutation | language
---------------+----------
hello | English
hola | Spanish
bonjour | French
merhaba selam | Turkish
zdravstvuyte | Russian
buon giorno | Italian
sawadee ka | Thai
(7 rows)
helloworld=>
The chain of events is as follows.
1. We type the words into the form.
2. We click submit.
3. The words are placed into the http request:
webob._parsed_post_vars: (MultiDict([('salutation', 'sawadee ka'),
('language', 'Thai')]), <FakeCGIBody at
0x354b5d0 viewing MultiDict([('ol...R')])>)
4. We are routed to the add_salutation function in the hello
controller via routing.py
5. The add_salutation function parses the salutation and language
out of the http request.
6. It then hands it off to Simpycity, which translates it into:
SELECT * FROM public.add_salutation(E'sawadee ka',E'Thai');
7. Which then inserts it into the database, returning true.
8. We come back to the add_salutation function, which checks the
return, which is True.
9. We are routed to the success page. Here, we can click to go back
to the /hello page.
We can now click to see our newly added salutation and language. (which may take a few tries, since it is randomly selected.)
sawadee ka That is hello in: Thai Add a salutation Remove a salutation Modify a salutationWe can modify a salutation as well. Click the modify salutation link, which will bring us to the modify salutation page. We will add capitalization to the Thai greeting that we just added. Type the following into the form pages: Old Salutation: sawadee ka New Salutation: Sawadee Ka And click submit. Again, you will be greeted by a page that looks like this.
SUCCESS: Modified: sawadee ka Sawadee Ka Return homeAnd if we check the database here as well...
lacey@blinky:~/project/helloworld/helloworld/model$ psql -U helloworld
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
helloworld=> SELECT * FROM hello;
salutation | language
---------------+----------
hello | English
hola | Spanish
bonjour | French
merhaba selam | Turkish
zdravstvuyte | Russian
buon giorno | Italian
Sawadee Ka | Thai
(7 rows)
helloworld=>
We note that the salutation is now capitalized.
The lifecycle of this event is almost exactly the same as the one noted above, except in two places.
The routing points at the modify_salutation function, and the upd_salutation function is translated into
SELECT * FROM public.update_salutation(E'sawadee ka',E'Sawadee Ka')Finally, if we decide that we no longer want the salutation Sawadee Ka in the database, we can delete with a very similar lifecycle. Click on the Remove a Salutation Link. Salutation: Sawadee Ka (note that it has to be capitalized now) Language: Thai
SUCCESS: Removed: Sawadee Ka ThaiChecking the database, we note that it is indeed removed.
lacey@blinky:~/project/helloworld/helloworld/model$ psql -U helloworld
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
helloworld=> SELECT * FROM hello;
salutation | language
---------------+----------
hello | English
hola | Spanish
bonjour | French
merhaba selam | Turkish
zdravstvuyte | Russian
buon giorno | Italian
(6 rows)
helloworld=>
And as noted above, the lifecycle is the same.
The routing points at the remove_salutation function, and the del_salutation function is translated into
SELECT * FROM public.remove_salutation(E'Sawadee Ka',E'Thai')Thus, removing it from the database. This now completes our small web application. Combining Simpycity, Pylons and PostgreSQL, we have made an app that displays database content, and creates, updates, and deletes that content, which is the core functionality of any database driven web application. And this also concludes our PostgreSQL->Simpycity->Pylons tutorial. Thank you for following along. =)
blog comments powered by Disqus
Copyright © 2000-2012 Command Prompt, Inc. All Rights Reserved. All trademarks property of their respective owners.