Simpycity

h2. Creating your first "Hello World" Project in Pylons.

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>

Enter template_engine (mako/genshi/jinja2/etc: Template language) ['mako']: genshi

</pre>

For templating, choose "genshi".

<pre>

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.

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 the development.ini file.

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".

<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 to:

https://projects.commandprompt.com/public/simpycity

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>

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.

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.

<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: 

   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.

<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 [[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)

</pre>

We will add the following lines, to fix a troublesome issue before it starts.

For the curious, more detail can be found here:

http://www.commandprompt.com/blogs/aurynn_shaw/2009/05/long_running_request_handlers_and_python_garbage_collection/

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>

"""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>

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: 

   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.

<pre>

nano helloworld/public/hello.html

</pre>

Paste the following content. 

<pre>

<html>

   <body>

      Hello World!

   </body>

</html>

</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 <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:

<pre>

paster controller hello

</pre>

Successful execution of this command should look something like this:

<pre>

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 <ctrl>-<c>.

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 <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.

<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>

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>

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 67i (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>

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.

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:

<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]]