These notes describe how to use the
psycopg2 Python library to interact with a PostgreSQL database. The
psycopg2 library can be installed with
pip install psycopg2 at the command line.
psycopg2 is imported as
import psycopg2 as pg2
Next, connect to the database. By convention, that is
secret = # your password conn = pg2.connect(database='dvdrental', user='postgres', password=secret)
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.
cur = conn.cursor()
Those steps are all that is required to begin executing SQL.
cur.execute('SELECT * FROM payment')
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.
data = cur.fetchmany(10)
Then, the data can be conveniently accessed using tuple unpacking, as shown below.
Once the data reading process is complete, close the connection.
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.