Lab: postgres and python using psycopg2

Nothing to hand in

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

Note: I'm going to assume you will do this lab on stearns. If you have installed postgres on your computer, that's fine, but then you'll also have to install the psycopg2 python module in your global installation of python3 or your virtual environment.

• Login to stearns.
• Update your copy of my github repository.
• In my repo, note the postgresql folder, which contains the files books.sql and psycopg2-sample.py.

• If you already have my books database loaded, great. Otherwise, use the books.sql file to load the books database on your machine.

  psql < books.sql

  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?
• Copy psycopg2-sample.py to your own repo, in a directory named webapp (which I assume you'll need to create now). This directory is where your web application code is going to live from now on.
• Create a file named webapp/.gitignore containing exactly one line of text: config.py. Save the file, then git add the new .gitignore file, and git commit right away.
• Create a file called webapp/config.py. Use the comments in psycopg2-sample.py to figure out what you need to put in config.py (just three assignment statements).
• Execute python3 psycopg2-sample.py and see what happens. Not working? Troubleshoot, and if you get stuck, ask questions in class, in office hours, or on Slack.
• You can keep psycopg2-sample.py if you want, but I would recommend not committing it to your webapp directory, since you want to preserve that for your project's code.

Recap

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.