This final section of the course utilizes the
psycopg2 Python library to interact with a PostgreSQL database using Python. That library can be installed with
pip install psycopg2 at the command line.
psycopg2 is imported as
Next, connect to the database. By convention, that is
Now, the connection is established. The next step is retrieving the cursor. A cursor is a control structure that enables traversal over database records. It is like a pointer or iterator for SQL data retrieval.
Those steps are all that is required to begin executing SQL.
The cursor has a couple methods available to return rows of data. These methods include:
fetchall- returns all the rows. The return type is a list of tuples, with types appropriate to the data.
fetchmany- takes as a parameter the number of rows to return. The return type is a list of tuples, with types appropriate to the data.
fetchone- returns the first row of the data. The return type is a single tuple.
Then, the data can be conveniently accessed using tuple unpacking, as shown below.
Once the data reading process is complete, close the connection.
A final note from the instructor is to use psycopg2 carefully, as Python string manipulation makes it easy to accidentally mess up the database. The best practice is to always manually type out the entire SQL query, as opposed to relying on string manipulation in Python, to ensure that the query being executed is what the user intends.