Aims

This exercise aims to give you practice in:

• writing Python code to access a PostgreSQL database
• building a Python script that can run as a command

This exercise will not explain how to do everything in fine detail. Part of the aim of the exercise is that you explore how to use the Python, Psycopg2 and PostgreSQL systems. The documentation for the versions of these systems on d2: Python3, Psycopg2, PostgreSQL.

Background

In this exercise we are using the same database as for Prac 06: an on-line beer rating system which describes beers, breweries, tasters, ratings, etc.

A relational schema for this database is available in the file

/home/cs3311/web/23T3/pracs/08/schema.sql

A dump file containing both the schema and the data for this database is available in the file

/home/cs3311/web/23T3/pracs/08/beers.dump

The relational model attempts to capture all of the semantics of the E/R design. However, there is one difference between the relational model and the E/R design in that beer styles have been converted into entities. This is primarily to ensure that all beer style information looks consistent (e.g. we don't have some beers called lager and others called laager).

You should create a new database called beers and load the schema and data into this database. The following commands wil do this:

$ createdb beers
CREATE DATABASE
$ psql beers -f /home/cs3311/web/23T3/pracs/08/beers.dump
... which will produce CREATE TABLE, COPY and ALTER TABLE messages ...

If you get any error messages from the above commands, read them carefully, diagnose the problem, and fix it.

Once the schema and data are loaded, check that everything is in order by running the following queries and seeing whether you get the same results:

beers=# select count(*) from Ratings;
 count 
-------
    32
(1 row)

beers=# select given from Taster order by given;
 given  
--------
 Adam
 Geoff
 Hector
 Jeff
 John
 Peter
 Raghu
 Ramez
 Rose
 Sarah
(10 rows)

If the database doesn't look correct, try to work out what went wrong and then try to load the data correctly. Once you're statisfied that the database is correct, continue with the exercises.

Exercises

Write a Python/Psycopg2 script that gives average ratings for three different kinds of entity: tasters, beers and brewers. The script has the following usage:

Usage: ./avgrat taster|beer|brewer Name

The command is called avgrat (short for "average ratings") and takes two command line arguments: type of entity and a name. The type of entity can be either taster, beer or brewer. The behaviour of the script is as follows for each of these:

• taster ... takes the given Name of a taster and shows the average rating they give for beers
• beer ... take the Name of a beer and shows the average rating for that beer
• brewer ... takes the Name of a brewer and shows the average rating for beers made by that brewer

Note that the names have to match exactly the names in the database.

You will need to create script from scratch yourself. There are examples of such scripts in the Data entries for the Week 7 Lecture Material. Make sure that you add the appropriate #! line at the start of the script, and that you make the script executable.

Some examples of how the script should work:

$ ./avgrat not right
Usage: ./avgrat taster|beer|brewer Name
$ ./avgrat taster John
Average rating for taster John is 3.1
$ ./avgrat taster Sarah
Average rating for taster Sarah is 2.7
$ ./avgrat taster sarah
No taster called 'sarah'   # doesn't exactly match upper/lower-case
$ ./avgrat brewer Chimay 
Average rating for brewer Chimay is 3.0
$ ./avgrat brewer 'Sierra Nevada'
Average rating for brewer Sierra Nevada is 3.8
$ ./avgrat brewer "Pete's"
No ratings for Pete's
$ ./avgrat beer New
Average rating for beer New is 1.5
$ ./avgrat beer 'Sierra Nevada Pale Ale'
Average rating for beer Sierra Nevada Pale Ale is 4.0

You should try to solve the problem yourself before looking at the below.

A partial solution is available in the file

/home/cs3311/web/23T3/pracs/08/avgrat

It is deficient in being unable to distinguish between an X with no ratings and an X that does not exist in the database.