CMD: Lacey Powers's Blog http://www.commandprompt.com/blogs/lacey_powers/ Command Prompt Blog Feed: Lacey Powers's Blog Thu, 18 Jun 2009 10:50:07 -0700 www.commandprompt.com http://www.commandprompt.com/images/small_mammoth.gif Command Prompt Mammoth http://www.commandprompt.com/blogs/lacey_powers/ 46 46 http://www.commandprompt.com/blogs/lacey_powers/2009/06/pylons_postgresql_and_simpycity_in_60_minutes_or_less/ Thu, 18 Jun 2009 10:50:07 -0700 Pylons, PostgreSQL, and Simpycity in 60 Minutes or Less http://www.commandprompt.com/blogs/lacey_powers/2009/06/pylons_postgresql_and_simpycity_in_60_minutes_or_less/ 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. <pre> mkdir project </pre> Move into the working directory. <pre> cd project </pre> Create your project: <pre> paster create -t pylons helloworld </pre> This should give you a set of choices. <pre style="font-size: 12px"> Enter template_engine (mako/genshi/jinja2/etc: Template language) ['mako']: genshi </pre> For templating, choose "genshi". <pre style="font-size: 12px"> Enter sqlalchemy (True/False: Include SQLAlchemy 0.5 configuration) [False]: False </pre> Include sqlalchemy. "False". We will be using Simpycity instead. You should have a directory that looks like this: <pre> 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$ </pre> cd into the helloworld project. <pre> cd helloworld </pre> The base directory should look like this: <pre> 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$ </pre> 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. <pre> 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. </pre> 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". <pre> nano setup.py </pre> There are only a few options that need to be modified in the base setup.py file. Note the section that looks like this: <pre> install_requires=[ "Pylons>=0.9.7", "Genshi>=0.4", ], </pre> 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. <pre> install_requires=[ "Pylons>=0.9.7", "Genshi>=0.4", "Simpycity>=0.2.1" ], </pre> 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 <a href="https://projects.commandprompt.com/public/simpycity"here</a> 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.) <pre> The current release version of Simpycity is 0.2.1, which can be downloaded from here </pre> 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. <pre> dependency_links=[ "https://projects.commandprompt.com/public/simpycity/repo/dist/" ] </pre> 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: <pre> aptitude search postgresql-server-dev-8.3 python2.5-dev build-essential </pre> Which should return like this: <pre style="font-size: 10px"> 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$ </pre> If it does not, then running the following command: <pre> sudo aptitude install postgresql-server-dev-8.3 python2.5-dev build-essential </pre> Will install the essential libraries for you. Now, we run the following command: <pre> sudo python setup.py develop </pre> 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. <pre> sudo su - postgres </pre> It will ask you for your password. Success should look something like this. <pre> lacey@blinky:~/project/helloworld$ sudo su - postgres [sudo] password for lacey: postgres@blinky:~$ </pre> Now, we run a PostgreSQL command to create a user. <pre> 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=# </pre> You are now on the postgresql command line. While there, run the following commands. <pre> CREATE USER helloworld WITH ENCRYPTED PASSWORD '12345'; CREATE DATABASE helloworld WITH OWNER helloworld; </pre> The successful execution of these two commands should look like this: <pre> postgres=# CREATE USER helloworld WITH ENCRYPTED PASSWORD '12345'; CREATE ROLE postgres=# CREATE DATABASE helloworld WITH OWNER helloworld; CREATE DATABASE postgres=# </pre> Exit PostgreSQL with the \q command. This should drop you back to the command line. <pre> postgres=# \q postgres@blinky:~$ </pre> If you haven't already, take a moment to modify your pg_hba.conf so you can easily log in. <pre> nano /etc/postgresql/8.3/main/pg_hba.conf </pre> Near the bottom, there should be a line that looks like *exactly* like this. <pre> local all all ident sameuser </pre> The very last part, "ident sameuser" should be changed to md5, so that the line looks like this: <pre> local all all md5 </pre> Save and quit. Now, execute the command: <pre> /etc/init.d/postgresql-8.3 force-reload </pre> This will reload the settings that you changed. <pre> postgres@blinky:~$ /etc/init.d/postgresql-8.3 force-reload * Reloading PostgreSQL 8.3 database server [ OK ] postgres@blinky:~$ </pre> Exit the postgres user environment with the exit command. <pre> postgres@blinky:~$ exit logout lacey@blinky:~/project/helloworld$ </pre> 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. <pre> cd ~ </pre> <pre> nano .pgpass </pre> When the editor window comes up, add the following line. <pre> localhost:5432:*:helloworld:12345 </pre> Save and quit. Following that, execute the following command. <pre> chmod 600 .pgpass </pre> This will appropriately set the permissions for your user. You can check them with the following command. <pre> lacey@blinky:~$ ls -l .pgpass -rw------- 1 lacey lacey 135 2009-06-05 12:10 .pgpass lacey@blinky:~$ </pre> Now, there is the final test. Execute the following command. <pre> psql -U helloworld </pre> If everything has been successfully set up, your terminal screen should look like this: <pre> 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=> </pre> With the command logging you in without issue, or a password prompt. While we are here, in PostgreSQL, we should issue the following command: <pre> CREATE LANGUAGE plpgsql; </pre> Again, success should look like this: <pre> helloworld=> CREATE LANGUAGE plpgsql; CREATE LANGUAGE helloworld=> </pre> And again, we exit with "\q". <pre> helloworld=> \q lacey@blinky:~$ </pre> Now, we return to the project directory with the following command. <pre> cd project/helloworld/ </pre> In this directory, we edit development.ini <pre> nano development.ini </pre> 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: <pre> db.database = helloworld db.user = helloworld db.host = localhost db.port = 5432 db.password = 12345 </pre> These are the values that Simpycity requires for the database connections. <pre> 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. </pre> 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. <pre> 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$ </pre> This directory contains all of your project specific code is placed. If you issue the following commands: <pre> cd helloworld ls -l </pre> You will note that the directory has the following structure: <pre> 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$ </pre> The directories here break down as follows: <pre> 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. </pre> 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. <pre> nano lib/base.py </pre> The contents of the file will look like this. <pre> """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) </pre> We will add the following lines, to fix a troublesome issue before it starts. For the curious, more detail can be found <a href="http://www.commandprompt.com/blogs/aurynn_shaw/2009/05/long_running_request_handlers_and_python_garbage_collection/">here at Aurynn Shaw's blog</a>: 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. <pre> 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() </pre> We paste this in so that the controller code will look like this: <pre>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() </pre> Save, and exit nano. Next we edit environment.py <pre>One line nano config/environment.py </pre> Within this file, at the very end, below the lines: <pre> # CONFIGURATION OPTIONS HERE (note: all config options will override # any Pylons config options) </pre> we add the follwing lines, for Simpycity setup. <pre> 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 </pre> And at the top of environment.py, with the rest of the imports, we add the following line. <pre> from simpycity import config as db_config </pre> 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: <pre> 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. </pre> The trivially easy case is static content. Execute the following command. <pre> nano helloworld/public/hello.html </pre> Paste the following content. <pre> &lt;html&gt; &lt;body&gt; Hello World! &lt;/body&gt; &lt;/html&gt; </pre> Save and exit. This creates a basic static html file. Now, from our current directory, execute the following command <pre> paster serve --reload development.ini </pre> 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: <pre> http://localhost:5000/hello.html </pre> You should see a simple page with the words "Hello World!". Now, stop the server on the command line with &lt;ctrl&gt;-&lt;c&gt;. We are moving on to the more interesting and complicated case of using a controller to serve content within Pylons. Execute the following command: <pre> paster controller hello </pre> Successful execution of this command should look something like this: <pre style="font-size: 10px"> 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 </pre> 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: <pre> nano helloworld/controller/hello.py </pre> Shows us the contents of the hello.py controller, which are very basic. <pre> 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' </pre> Exiting from Nano, we can also have a look at test_hello.py by executing a similar command. <pre> nano helloworld/tests/functional/test_hello.py </pre> The contents of which are: <pre> from helloworld.tests import * class TestHelloController(TestController): def test_index(self): response = self.app.get(url(controller='hello', action='index')) # Test response... </pre> 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: <pre> nano helloworld/config/routes.py </pre> This should bring up a file that looks like this: <pre> """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 </pre> 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.... <pre> paster serve --reload development.ini </pre> And attempted to browse to the URI: <pre> http://localhost:5000/hello </pre> 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 : <pre> map.connect('hello', '/hello', controller='hello', action='index') </pre> 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: <pre> http://localhost:5000/hello </pre> Now, when we run: <pre> paster serve --reload development.ini </pre> And attempted to browse to the URI: <pre> http://localhost:5000/hello </pre> We will see the words "Hello World". Note that this is different from our static html entry. <pre> http://localhost:5000/hello.html </pre> Which says "Hello World!". And both are available. Moving to a more generic route.... <pre> http://localhost:5000 </pre> 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 &lt;ctrl&gt;-&lt;c&gt;. Execute the command: <pre> nano helloworld/config/routes.py </pre> At the bottom, just before "return map", we will add the following line. <pre> map.connect('root', '/', controller='hello', action='index') </pre> 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. <pre> The more specific and detailed routes should be defined first so they may take precedent over the more generic routes. </pre> 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. <pre> paster serve --reload development.ini </pre> And browse to: <pre> http://localhost:5000 </pre> ....We still see the cheery orange-yellow and black welcome page. (Be sure to shut down the server with &lt;ctrl&gt;-&lt;c&gt;) 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. <pre> app = Cascade([static_app, app]) </pre> 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: <pre style="font-size: 12px"> mv helloworld/public/index.html helloworld/public/index.html.dont_evaluate_me_pylons </pre> 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: <pre style="font-size: 12px"> mv helloworld/public/index.html.dont_evaluate_me_pylons helloworld/public/index.html </pre> 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: <pre> nano helloworld/config/middleware.py </pre> 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: <pre> #app = Cascade([static_app, app]) app = Cascade([app, static_app]) </pre> 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 <pre> 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 </pre> 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. <pre> cd .. </pre> And make a new directory called 'sql'. <pre> mkdir sql </pre> 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. <pre style="font-size: 14px"> 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; </pre> Execute the following command, in your ~/project/sql directory. <pre> nano hello.sql </pre> Paste the above SQL code into that file, save and quit. Then run the following command. <pre> psql -U helloworld helloworld -f hello.sql </pre> Success should look like this. <pre> 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$ </pre> 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. <pre> cd ../helloworld/helloworld </pre> The directory you are in should look like this, if you execute the command ls -l : <pre> 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$ </pre> 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: <pre> nano model/hello_model.py </pre> And paste the following code into it: <pre> 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']) </pre> 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: <pre> add_salutation = Function("public.add_salutation",['salutation','language']) </pre> 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. <pre> get_salutation = Raw("SELECT salutation FROM public.hello ORDER BY random() LIMIT 1") </pre> For example, calling this function: return_item = HelloModel.get_salutation() Raw simply executes the provided query, which in this example, is: <pre> SELECT salutation FROM public.hello ORDER BY random() LIMIT 1; </pre> Returning a random way to say hello into the variable "return_item". Now, we need to modify the Hello Controller. Execute the command: <pre> nano controller/hello.py </pre> The contents of which are: <pre> 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' </pre> We will add: <pre> from helloworld.model.hello_model import HelloModel </pre> and: <pre> salutation = HelloModel.get_salutation(options=dict(fold_output=True)) return salutation </pre> To the hello controller, and we will comment out the line "return 'Hello World'", so that our controller now looks like this: <pre> 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 </pre> 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 <pre> cd .. </pre> And then execute: <pre> paster serve --reload development.ini </pre> 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 &lt;ctrl&gt;-&lt;c&gt; to stop your server. Now, we refer back to the directory layout of the project. <pre> 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$ </pre> 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. <pre> cd helloworld/templates </pre> Now, we are going to look at the contents of this directory. <pre> 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$ </pre> 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. <pre> mkdir hello </pre> We will move into it: <pre> cd hello </pre> and we will make a blank __init__.py file. <pre> touch __init__.py </pre> We will also make another file. <pre> nano hello.html </pre> And within that we will paste the following contents. <pre> &lt;html xmlns="http://www.w3.org/1999/xhtml"&gt; &lt;head&gt; &lt;title&gt;${c.hello}&lt;/title&gt; &lt;/head&gt; &lt;body class="index"&gt; &lt;div id="header"&gt; &lt;h1&gt;${c.hello}&lt;/h1&gt; &lt;/div&gt; &lt;p&gt;That is hello in: ${c.language}&lt;/p&gt; &lt;/body&gt; &lt;/html&gt; </pre> 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 <a href="http://genshi.edgewall.org/wiki/Documentation">here</a> But before we render this page, we need to make a few more modifications. We move out of this directory, into the model directory. <pre> cd ../../model </pre> And open the file hello_model.py. We will add the following content. <pre> get_language = Raw("SELECT language FROM public.hello WHERE salutation = %s", ['salutation']); </pre> 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. <pre> cd ../controller </pre> <pre> nano hello.py </pre> We will add the following import: <pre> from pylons.templating import render_genshi as render </pre> 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: <pre> c.language = HelloModel.get_language(salutation, options=dict(fold_output=True)) c.hello = salutation return render("hello/hello.html"); </pre> This should leave the file looking like this: <pre> 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 </pre> 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. <pre> cd ../.. </pre> <pre> paster serve --reload development.ini </pre> And again, we browse to the page http:://localhost:5000 in our browser. Which should now look something like this: <pre> bonjour That is hello in: French </pre> 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 (&lt;ctrl&gt;-&lt;c&gt;, again), and we move to the helloworld/public directory. <pre> cd helloworld/public </pre> And we copy/paste the following files: <pre> nano add_salutation.html </pre> And we paste the following code. <pre> &lt;html&gt; &lt;body&gt; &lt;form action="/hello/add_salutation" method="POST"&gt; &lt;p&gt; Add Salutation:&lt;br/&gt; Salutation: &lt;input type="text" name="salutation"&gt;&lt;br/&gt; Language: &lt;input type="text" name="language"&gt;&lt;br/&gt; &lt;input type="submit" value="submit"&gt; &lt;/p&gt; &lt;/form&gt; &lt;/body&gt; &lt;/html&gt; </pre> Save and close. <pre> nano modify_salutation.html </pre> And we paste the following code. <pre> &lt;html&gt; &lt;body&gt; &lt;form action="/hello/modify_salutation" method="POST"&gt; &lt;p&gt; Modify Salutation:&lt;br/&gt; Old Salutation: &lt;input type="text" name="old_salutation"&gt;&lt;br/&gt; New Salutation: &lt;input type="text" name="new_salutation"&gt;&lt;br/&gt; &lt;input type="submit" value="submit"&gt; &lt;/p&gt; &lt;/form&gt; &lt;/body&gt; &lt;/html&gt; </pre> Save and close. <pre> nano remove_salutation.html </pre> And we paste the following code. <pre> &lt;html&gt; &lt;body&gt; &lt;form action="/hello/remove_salutation" method="POST"&gt; &lt;p&gt; Remove Salutation:&lt;br/&gt; Salutation: &lt;input type="text" name="salutation"&gt;&lt;br/&gt; Language: &lt;input type="text" name="language"&gt;&lt;br/&gt; &lt;input type="submit" value="submit"&gt; &lt;/p&gt; &lt;/form&gt; &lt;/body&gt; &lt;/html&gt; </pre> 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. <pre> cd ../controllers </pre> And open hello.py for editing <pre> nano hello.py </pre> We will add the following functions. <pre> 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") </pre> 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. <pre> cd ../templates/hello </pre> And we will create several files here as well: <pre> nano added_salutation.html </pre> And we will paste the following code. <pre> &lt;html xmlns="http://www.w3.org/1999/xhtml"&gt; &lt;head&gt; &lt;title&gt;SUCCESS&lt;/title&gt; &lt;/head&gt; &lt;body class="index"&gt; &lt;div id="header"&gt; &lt;h1&gt;SUCCESS: Added:&lt;/h1&gt; &lt;/div&gt; &lt;p&gt;${c.hello}&lt;/p&gt; &lt;p&gt;${c.language}&lt;/p&gt; &lt;p/&gt; &lt;a href="/hello"&gt;Return home&lt;/a&gt; &lt;/body&gt; &lt;/html&gt; </pre> Save and quit. <pre> nano modified_salutation.html </pre> And we will paste the following code. <pre> &lt;html xmlns="http://www.w3.org/1999/xhtml"&gt; &lt;head&gt; &lt;title&gt;SUCCESS&lt;/title&gt; &lt;/head&gt; &lt;body class="index"&gt; &lt;div id="header"&gt; &lt;h1&gt;SUCCESS: Modified:&lt;/h1&gt; &lt;/div&gt; &lt;p&gt;${c.old}&lt;/p&gt; &lt;p&gt;${c.new}&lt;/p&gt; &lt;p/&gt; &lt;a href="/hello"&gt;Return home&lt;/a&gt; &lt;/body&gt; &lt;/html&gt; </pre> Save and quit. <pre> nano removed_salutation.html </pre> And we will paste the following code. <pre> &lt;html xmlns="http://www.w3.org/1999/xhtml"&gt; &lt;head&gt; &lt;title&gt;SUCCESS&lt;/title&gt; &lt;/head&gt; &lt;body class="index"&gt; &lt;div id="header"&gt; &lt;h1&gt;SUCCESS: Removed:&lt;/h1&gt; &lt;/div&gt; &lt;p&gt;${c.hello}&lt;/p&gt; &lt;p&gt;${c.language}&lt;/p&gt; &lt;p/&gt; &lt;a href="/hello"&gt;Return home&lt;/a&gt; &lt;/body&gt; &lt;/html&gt; </pre> Save and quit. We will also add a few links to hello.html <pre> nano hello.html </pre> <pre> &lt;html xmlns="http://www.w3.org/1999/xhtml"&gt; &lt;head&gt; &lt;title&gt;${c.hello}&lt;/title&gt; &lt;/head&gt; &lt;body class="index"&gt; &lt;div id="header"&gt; &lt;h1&gt;${c.hello}&lt;/h1&gt; &lt;/div&gt; &lt;p&gt;That is hello in: ${c.language}&lt;/p&gt; &lt;p/&gt; &lt;a href="add_salutation.html"&gt;Add a salutation&lt;/a&gt;&lt;br/&gt; &lt;a href="remove_salutation.html"&gt;Remove a salutation&lt;/a&gt;&lt;br/&gt; &lt;a href="modify_salutation.html"&gt;Modify a salutation&lt;/a&gt; &lt;/body&gt; &lt;/html&gt; </pre> Save and quit. We will also add the following code to a new file called error.html <pre> nano error.html </pre> And we will paste the following code. <pre> &lt;html xmlns="http://www.w3.org/1999/xhtml"&gt; &lt;head&gt; &lt;title&gt;ERROR&lt;/title&gt; &lt;/head&gt; &lt;body class="index"&gt; &lt;div id="header"&gt; &lt;h1&gt;ERROR: Failed to add/modify/delete&lt;/h1&gt; &lt;/div&gt; &lt;p&gt;${c.hello}&lt;/p&gt; &lt;p&gt;${c.language}&lt;/p&gt; &lt;p/&gt; &lt;a href="/hello"&gt;Return home&lt;/a&gt; &lt;/body&gt; &lt;/html&gt; </pre> Save and quit. Finally, we add a couple of lines to hello.html <pre> nano hello.html </pre> <pre> &lt;html xmlns="http://www.w3.org/1999/xhtml"&gt; &lt;head&gt; &lt;title&gt;${c.hello}&lt;/title&gt; &lt;/head&gt; &lt;body class="index"&gt; &lt;div id="header"&gt; &lt;h1&gt;${c.hello}&lt;/h1&gt; &lt;/div&gt; &lt;p&gt;That is hello in: ${c.language}&lt;/p&gt; &lt;p/&gt; &lt;a href="add_salutation.html"&gt;Add a salutation&lt;/a&gt;&lt;br/&gt; &lt;a href="remove_salutation.html"&gt;Remove a salutation&lt;/a&gt;&lt;br/&gt; &lt;a href="modify_salutation.html"&gt;Modify a salutation&lt;/a&gt; &lt;/body&gt; &lt;/html&gt; </pre> 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. <pre> cd ../../config </pre> <pre> nano routing.py </pre> And we add the following lines <pre> 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'])) </pre> 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. <pre> cd ../.. </pre> Which should put us into the outermost helloworld directory, with development.ini, so that we can start the server. <pre> paster serve --reload development.ini </pre> Now, browsing to <pre> http://localhost:5000/hello </pre> Should show you a page like this: <pre> hello That is hello in: English Add a salutation Remove a salutation Modify a salutation </pre> 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. <pre> SUCCESS: Added: sawadee ka Thai Return home </pre> And if we were to look in the database at this time, we would see the entry has been successfully entered into the database. <pre> 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=> </pre> The chain of events is as follows. <pre> 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')]), &lt;FakeCGIBody at 0x354b5d0 viewing MultiDict([('ol...R')])&gt;) 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. </pre> We can now click to see our newly added salutation and language. (which may take a few tries, since it is randomly selected.) <pre> sawadee ka That is hello in: Thai Add a salutation Remove a salutation Modify a salutation </pre> 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. <pre> SUCCESS: Modified: sawadee ka Sawadee Ka Return home </pre> And if we check the database here as well... <pre> 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=> </pre> 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 <pre> SELECT * FROM public.update_salutation(E'sawadee ka',E'Sawadee Ka') </pre> 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 <pre> SUCCESS: Removed: Sawadee Ka Thai </pre> Checking the database, we note that it is indeed removed. <pre> 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=> </pre> 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 <pre> SELECT * FROM public.remove_salutation(E'Sawadee Ka',E'Thai') </pre> 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. =) http://www.commandprompt.com/blogs/lacey_powers/2009/06/pylons_postgresql_and_simpycity_in_60_minutes_or_less/ OpenSource PostgreSQL Python