CS 257: Software Design

Lab: getting started with psycopg2

To complete your Flask API, you'll need a way for your Python code to send SQL queries to your PostgreSQL database. The most popular Python library for PostgreSQL interaction is the awkwardly named psycopg2.

Get my sample code

Clone my CS257 Fall 2018 GitHub repository, and open up psycopg2/psycopg2-sample.py.

Typical psycopg2 interaction

This sequence of steps is illustrated in minimal fashion in psycopg2-sample.py.

• 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.

Try adjusting the database name and the SQL query in psycopg2-sample.py to match your database. (Or just use my books/authors database from the PostgreSQL lab.)