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() 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:
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.