CS 257: Software Design

Lab: postgres and python using psycopg2

To access a PostgreSQL database from a Python program, we will use the psycopg2 module. This short exercise is intended to get you ready to use psycopg2 when you start developing your own database-driven application.

Things to do

• Make sure that your postgres server is running. See the intro to postgres lab for reminders on how to do this. If you can get successfully to the psql prompt via "psql -U YOUR_PSQL_NAME postgres", then your server is running just fine.
• Install the psycopg2 module into your python3 installation by executing:
  python3 -m pip install psycopg2
  at your Unix prompt. Assuming you don't get an error message, you can try running python3's interactive prompt (i.e. just execute "python3" to get the >>> prompt), and then type "import psycopg2". If the prompt comes back with no complaint, you're all set.
• Get an up-to-date copy of my GitHub repository, either by doing a fresh "git clone" or by doing "git pull" in a clone you already have.
• In my repository, note the postgresql folder, which contains the files books.sql and psycopg2-sample.py.
• Use the books.sql file to load the books database on your machine.
  • Make sure you have an empty postgres database named "books". If you already have one, delete all its tables by executing
    DROP TABLE whatever
    in psql for each table. If you don't already have a books database, execute
    CREATE DATABASE books
  • Once you have a books database with no tables, go back out to the Unix prompt and run:
    psql -U YOUR_PG_USERNAME books < books.sql
    This will load the books database tables into your books database.
  • Go back into psql and check to make sure all three tables are there, and that they contain reasonable data.
• Read through psycopg2-sample.py. What do you expect its output to be once you get it running?
• Create a file called config.py in the directory where you put your copy of psycopg2-sample.py. Use the comments in psycopg2-sample.py to figure out what you need to put in config.py (hint: it's just three assignment statements).
• Execute "python3 psycopg2-sample.py" and see what happens. Not working? Troubleshoot, and if you get stuck, ask questions on Slack.

Once you have the sample program working, reread the code to nail down in your brain this basic sequence of steps involved in querying your database from your code:

• Make a connection to the database server.
• Use the connection to create a cursor object.
• Use the cursor to issue a SQL query to the database. (Exceptions are possible, so put this code in a try/except structure.)
• If your query was a SELECT statement, iterate over the cursor in a for loop to obtain the query's response.
• Close the connection.