Testing – Controller Database Access

Now is a good time to test the database access from a Python script, but first we need to install the PostgreSQL adapter for Python –  psycopg.

sudo apt-get install python-psycopg2

Then we can place the following script in the /usr/local/bin/code/controller directory…

#
# hellodb.py script to show PostgreSQL and Pyscopg together
#

import sys
import psycopg2
import psycopg2.extras

try:
    cstr = "dbname='hub' user='postgres' host='localhost' password='raspberry'"
    conn = psycopg2.connect(cstr)
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    cur.execute("SELECT * from \"Zone\"")
    rows = cur.fetchall()
    print "\nShow me the zones:\n"
    for row in rows:
        print row.get("ZoneID"), row.get("ZoneName")
except Exception:
    print("Unable to connect to the database")
    e = sys.exc_info()[0]
    print (e)

 

Run your script with the following…

cd /usr/local/bin/code/controller
sudo python hellodb.py

You should see the following output:

Show me the zones:

1 Kitchen
2 Bathroom

This script uses the Dictionary-like cursor found in the extras package. The advantage of this method of database access is that you can reference the columns by name instead of index, which makes the code more tolerant of database changes.

Having confirmed our controller can access the database, it is time to create the website.