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
- 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 psycopg2at 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 my latest 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 whateverin psql for each table. If you don't already have a books database, executeCREATE 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.sqlThis 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.
- Make sure you have an empty postgres database named "books".
If you already have one, delete all its tables by executing
- 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 in class, in office hours, or 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.