Mammoth
  • |
  • Contact
  • |
CMD | Command Prompt, Inc. - PostgreSQL Solutions, Support & Hosting
  • |
  • |
  • |
  • |
  • |
Pylons, PostgreSQL, and Simpycity in 60 Minutes or Less
Posted Thursday Jun 18th, 2009 10:50am
by Lacey Powers
| Permalink

Follow cmdpromptinc on Twitter


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 project
Move into the working directory.
cd project
Create your project:
paster create -t pylons helloworld
This should give you a set of choices.
Enter template_engine (mako/genshi/jinja2/etc: Template language) ['mako']: genshi
For templating, choose "genshi".
Enter sqlalchemy (True/False: Include SQLAlchemy 0.5 configuration) [False]: False
Include 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 helloworld
The 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.py
There 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 here 
All 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-essential
Which 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-essential
Will install the essential libraries for you. Now, we run the following command:
sudo python setup.py develop
This 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 - postgres
It 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.conf
Near the bottom, there should be a line that looks like *exactly* like this.
local   all         all                               ident sameuser
The very last part, "ident sameuser" should be changed to md5, so that the line looks like this:
local   all         all                               md5
Save and quit. Now, execute the command:
/etc/init.d/postgresql-8.3 force-reload
This 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 .pgpass
When the editor window comes up, add the following line.
localhost:5432:*:helloworld:12345
Save and quit. Following that, execute the following command.
chmod 600 .pgpass
This 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 helloworld
If 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.ini
At 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 = 12345
These 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 -l
You 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.py
The 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.py
Within 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 = False
And at the top of environment.py, with the rest of the imports, we add the following line.
from simpycity import config as db_config
So 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.html
Paste 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.ini
This 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.html
You 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 hello
Successful 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.py
This 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.py
Shows 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.py
The 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.py
This 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.ini
And attempted to browse to the URI:
http://localhost:5000/hello
You 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/hello
Now, when we run:
paster serve --reload development.ini
And attempted to browse to the URI:
http://localhost:5000/hello
We will see the words "Hello World". Note that this is different from our static html entry.
http://localhost:5000/hello.html
Which says "Hello World!". And both are available. Moving to a more generic route....
http://localhost:5000
Will 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.py
At 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.ini
And 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_pylons
If 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.html 
To 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.py
Once 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.html
Now, 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 sql
Now, 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.sql
Paste the above SQL code into that file, save and quit. Then run the following command.
psql -U helloworld helloworld -f hello.sql
Success 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/helloworld
The 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.py
And 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.py
The 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 HelloModel
and:
salutation = HelloModel.get_salutation(options=dict(fold_output=True))
return salutation
To 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.ini
And 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/templates
Now, 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 hello
We will move into it:
cd hello
and we will make a blank __init__.py file.
touch __init__.py
We will also make another file.
nano hello.html
And 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 ../../model
And 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.py
We will add the following import:
from pylons.templating import render_genshi as render
Which 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.ini
And again, we browse to the page http:://localhost:5000 in our browser. Which should now look something like this:
bonjour

That is hello in: French
Now, 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/public
And we copy/paste the following files:
nano add_salutation.html
And 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.html
And 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.html
And 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 ../controllers
And open hello.py for editing
nano hello.py
We 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/hello
And we will create several files here as well:
nano added_salutation.html
And 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.html
And 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.html
And 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.html
And 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.py
And 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.ini
Now, browsing to
http://localhost:5000/hello
Should show you a page like this:

hello

That is hello in: English

Add a salutation
Remove a salutation
Modify a salutation
With 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 home
And 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 salutation
We 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 home
And 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

Thai
Checking 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. =)

Categories: OpenSource, PostgreSQL, Python

blog comments powered by Disqus

Copyright © 2000-2014 Command Prompt, Inc. All Rights Reserved. All trademarks property of their respective owners.