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
- Install the psycopg2 module into your python3 installation by executing:
pip3 install psycopg2
at your Unix prompt. Assuming you don't get 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.
- Grab a copy of psycopg2-sample.py.
- Read through psycopg2-sample.py. What do you expect its output to be once you get it running?
- Make sure that your postgres server is running, and that you have loaded the
books, authors, and books_authors tables into your database as you did for
the intro to postgres lab.
- 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.
Once you have gotten 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.