Use Python's Psycopg2 Adapter For PostgreSQL To Fetch Records From A Table

Written by Data Pilot
September 19, 2019
  • PostgreSQL
  • Psycopg2
  • Python
Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction to the psycopg2 adapter

When you connect to PostgreSQL in Python, using the psycopg2 Python adapter, you can created a cursor that allows you to execute SQL statements. This article will demonstrate how to use the SELECT SQL keyword, and the fetchall() psycopg2 method, to return all of the records, iterate the rows, and parse the data. Without further ado, let’s talk about how to use the Python Psycopg2 adapter with PostgreSQL to fetch records from a table.

Prerequisites to using psycopg2 and PostgreSQL

  • The example code in this article has only been tested on Python 3, and Python 2.7 is deprecated, and will lose official support by 2020, so it’s recommended that you have both Python 3 (use the python3 -V command to verify that it’s installed) and the PIP package manager for Python 3 (pip3 -V) installed:

Getting the versions of PIP and Python 3 in a terminal window and installing psycopg2 with pip3

  • Use the PIP3 package manager to install the psycopg2 library for the PostgreSQL Python adapter:
1
pip3 install psycopg2

Create a PostgreSQL database and table to use with psycopg2

The next section helps you setup some data that the psycopg2 adapter can use to fetchall() of its row data. Feel free to skip this if you already have some data on your PostgreSQL server.

Go into the ‘psql’ command-line interface

You’ll need a PostgreSQL database and table with some rows of data in it. Go into the psql command-line interface using the following command:

1
psql -U postgres -h localhost

NOTE: You may need to use elevated sudo privileges with su - postgres, sudo -i, or sudo su.

If that doesn’t work you can also try this command (just make sure to change the username, host, and database):

1
psql objectrocket -h 127.0.0.1 -d some_database

Create a database and table for the psycopg2 Python adapter

Once inside of psql you can use the following SQL command to create a database:

1
CREATE DATABASE some_database;

It should return a response of CREATE DATABASE if everything worked as expected. Now make sure to grant privileges to your user:

1
GRANT ALL PRIVILEGES ON DATABASE some_database TO objectrocket;

NOTE: Use this SQL command if you don’t have a user created yet: CREATE USER objectrocket WITH ENCRYPTED PASSWORD 'somePass';, and don’t forget to replace the username and password with your own.

Just use the CREATE TABLE keywords to create a PostgreSQL table after you connect to the database:

1
2
3
4
5
6
CREATE TABLE some_table (
    col_id INTEGER PRIMARY KEY NOT NULL,
    col_str VARCHAR(64) UNIQUE NOT NULL,
    col_int INTEGER NOT NULL,
    col_bool BOOLEAN NOT NULL
);

Insert data rows into the PostgreSQL table

The last step is to insert some rows of data into the table. Here’s an example of how to do that:

1
2
3
4
5
6
INSERT INTO some_table (col_id, col_str, col_int, col_bool)
VALUES (1, 'I''m a string. I have value!', 42, FALSE),
(2, 'Hello, entire world!', 12345, TRUE),
(3, 'Here is another string', 11111, 'yes'),
(4, 'Fun time with strings', 424242, 'no'),
(5, 'I''m the last string in this table', 9999, FALSE);

It should return a response of INSERT 0 5. The 0 represents the row it was inserted into, and the 5 is the row count.

Entering into psql command line and creating a PostgreSQL database and table and insert data rows

Create a Python script and import the psycopg2 libraries

Paste the following code at the beginning of the script to import the sql and connect modules from the psycopg2 library:

1
2
# import the sql and connect libraries for psycopg2
from psycopg2 import sql, connect

Declare Python strings for PostgreSQL the database and table names

Here’s some code that will declare some global strings for the PostgreSQL database and table names:

1
2
3
# create global strings for the PostgreSQL db and table name
db_name = "some_database"
table_name = "some_table"

They will be passed to the psycopg2 library’s connect() method in the next section.

Instantiate a psycopg2 connection to the PostgreSQL server

The following code attempts to connect to the PostgreSQL and instantiate a psycopg2.extensions.connection object inside of a try-except indentation block:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
try:
    # declare a new PostgreSQL connection object
    conn = connect(
        dbname = db_name,
        user = "objectrocket",
        host = "localhost",
        password = "mypass"
    )

    # print the connection if successful
    print ("psycopg2 connection:", conn)

except Exception as err:
    print ("psycopg2 connect() ERROR:", err)
    conn = None

NOTE: Make sure to pass the correct parameters to the method call or the connection will fail.

The code will set the connection object (conn) to none in the case of an exception.

Declare a new cursor object from the PostgreSQL connection

1
2
3
4
5
# ensure that the PostgreSQL connection is not set to 'None'
if conn != None:

    # instantiate a new cursor object
    cursor = conn.cursor()

Use the psycopg2 ‘sql’ module to format a SQL statement

Pass the SELECT * FROM SQL string to the sql.SQL() method call to have it return a psycopg2.sql.SQL object, and use Python’s format() function to insert the table name into the string.

Here’s some example code that will do that and select all of the data rows from a PostgreSQL table:

1
2
3
4
5
6
7
8
9
    # put all of the records into the cursor
    # (use sql.SQL() to prevent SQL injection attack)
    sql_object = sql.SQL(
        # pass SQL statement to sql.SQL() method
        "SELECT * FROM {};"
    ).format(
        # pass the identifier to the Identifier() method
        sql.Identifier( table_name )
    )

NOTE: It’s recommended that you use the sql.SQL() method, rather than concatenating the string, in order to help prevent SQL injection attacks.

Screenshot of IDLE3 Python 3 creating a psycopg2.sql.SQL object

Use the psycopg2 cursor to fetch all of the PostgreSQL data rows

Use the next bit of code to pass the psycopg2.sql.SQL object to the cursor’s execute() method:

1
2
3
4
5
    # use the execute() method to put table data into cursor obj
    cursor.execute( sql_object )

    # use the fetchall() method to return a list of all the data
    table_data = cursor.fetchall()

This will return the table data, and store it in the cursor, so that you can access it with the fetchall() method call.

Iterate over the PostgreSQL table rows using Python

The following code uses Python’s enumerate() function to iterate over the rows of table data, and then prints the row of tuple data in each iteration:

1
2
3
4
5
    # iterate the list of tuple rows
    for num, row in enumerate(table_data):
        print ("row:", row)
        print (type(row))
        print ("\n")

Close the psycopg2 cursor and connection objects

It’s always good practice to close the psycopg2 cursor and connection objects, once you’re done using them, in order to avoid memory leaks:

1
2
3
4
5
    # close cursor objects to avoid memory leaks
    cursor.close()

    # close the connection object to avoid memory leaks
    conn.close()

Conclusion

Make sure to save the code inside a Python script, and then use the python3 command to execute its code:

1
python3 get_table_data.py

Notice how the boolean values in the table are automatically converted to the Python boolean data types with the title-cased True and False values.

Screenshot of a Python 3 script running in a terminal window to get the table data from a PostgreSQL database

You should now have a good idea of how you can access the PostgreSQL table data in a Python script so that you can iterate and parse the table rows.

Just the Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
#!/usr/bin/python3
# -*- coding: utf-8 -*-

# import the sql and connect libraries for psycopg2
from psycopg2 import sql, connect

# create global strings for the PostgreSQL db and table name
db_name = "some_database"
table_name = "some_table"

try:
    # declare a new PostgreSQL connection object
    conn = connect(
        dbname = db_name,
        user = "objectrocket",
        host = "localhost",
        password = "mypass"
    )

    # print the connection if successful
    print ("psycopg2 connection:", conn)

except Exception as err:
    print ("psycopg2 connect() ERROR:", err)
    conn = None

# ensure that the PostgreSQL connection is not set to 'None'
if conn != None:

    # instantiate a new cursor object
    cursor = conn.cursor()

    # put all of the records into the cursor
    # (use sql.SQL() to prevent SQL injection attack)
    sql_object = sql.SQL(
        # pass SQL statement to sql.SQL() method
        "SELECT * FROM {};"
    ).format(
        # pass the identifier to the Identifier() method
        sql.Identifier( table_name )
    )

    # use the execute() method to put table data into cursor obj
    cursor.execute( sql_object )

    # use the fetchall() method to return a list of all the data
    table_data = cursor.fetchall()

    # iterate the list of tuple rows
    for num, row in enumerate(table_data):
        print ("row:", row)
        print (type(row))
        print ("\n")

    # close cursor objects to avoid memory leaks
    cursor.close()

    # close the connection object to avoid memory leaks
    conn.close()

else:
    print ("psycopg2 failed to connect to PostgreSQL\n")
Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.