PostgreSQL Python: Select Data from a Table with psycopg3

Summary: In this tutorial, you’ll learn how to select one or multiple rows and filter rows from a table using Psycopg 3.

Here are the steps for retrieving data from a table in PostgreSQL from Python:

First, connect to PostgreSQL from Python.
Next, create a Cursor object from the Connection object.
Then, execute a SELECT statement by calling the execute() method of the cursor object
After that, retrieve rows using fetchone(), fetchall(), or fetchmany().
Finally, process the returned rows in Python.

Prerequisites #

This tutorial assumes you already have a products table like this in the pyinventory database:

CREATE TABLE IF NOT EXISTS products (
    product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    safety_stock INTEGER NOT NULL,
    gross_weight NUMERIC(10, 2)
);

It also assumes that the table already contains some data. If not, you can follow this tutorial to insert some data into the table:

 product_id |       name       |  price  | safety_stock | gross_weight
------------+------------------+---------+--------------+--------------
          3 | Pixel 7 Pro      |  899.99 |            8 |         0.21
          4 | Xperia 1 IV      | 1299.99 |            7 |         0.19
          2 | Galaxy S23 Ultra |  949.99 |            9 |         0.23
          1 | iPhone 14 Pro    | 1199.99 |           20 |         0.24

Select all rows from a table #

The following script selects all rows from the products table:

import psycopg
from config import config

sql = """
SELECT product_id, name, price, safety_stock, gross_weight
FROM products
ORDER BY product_id
"""

try:
    with psycopg.connect(**config) as conn:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall()

            for row in rows:
                print(row)
except Exception as e:
    print(f"Error: {e}")

Example output:

(1, 'iPhone 14 Pro', Decimal('1199.99'), 20, Decimal('0.24'))
(2, 'Galaxy S23 Ultra', Decimal('949.99'), 9, Decimal('0.23'))
(3, 'Pixel 7 Pro', Decimal('899.99'), 8, Decimal('0.21'))
(4, 'Xperia 1 IV', Decimal('1299.99'), 7, Decimal('0.19'))

How it works #

Step 1: Import the psycopg library

import psycopg

The psycopg module allows Python to connect to PostgreSQL, execute SQL statements, and retrieve data.

Step 2: Import the database configuration

from config import config

This imports the database connection settings from config.py. For example:

{
    "host": "localhost",
    "dbname": "pyinventory",
    "user": "postgres",
    "password": ""
}

Step 3: Define the SQL SELECT statement

sql = """
SELECT product_id, name, price, safety_stock, gross_weight
FROM products
ORDER BY product_id
"""

This SELECT statement:

Retrieves data in product_id, name, price, safety_stock, and gross_weight columns from the products table
Sorts them by product_id

Step 4: Open a database connection

with psycopg.connect(**config) as conn:

This creates a connection to PostgreSQL. The with statement automatically closes the connection when the block finishes.

Step 5: Create a cursor

with conn.cursor() as cur:

We use a cursor to send an SQL SELECT statement to PostgreSQL and retrieve data.

Step 6: Execute the query

cur.execute(sql)

The execute() method sends the SELECT statement to PostgreSQL and executes it.

Since our SELECT statement has no parameters, you do not need to pass a second argument.

Step 7: Fetch all rows

rows = cur.fetchall()

The fetchall() method returns all rows from the query result as a list of tuples.

Step 8: Process the rows

for row in rows:
    print(row)

This loops through each row and displays it.

Step 9: Handle errors

except Exception as e:
    print(f"Error: {e}")

If an error occurs, Python prints the error message instead of crashing.

Select one row by ID #

The following script selects one product by its product_id:

import psycopg
from config import config

sql = """
SELECT product_id, name, price, safety_stock, gross_weight
FROM products
WHERE product_id = %s
"""

product_id = 1

try:
    with psycopg.connect(**config) as conn:
        with conn.cursor() as cur:
            cur.execute(sql, (product_id,))
            row = cur.fetchone()

            if row:
                print("Product found:")
                print(row)
            else:
                print("Product not found.")
except Exception as e:
    print(f"Error: {e}")

Output:

Product found:
(1, 'iPhone 14 Pro', Decimal('1199.99'), 20, Decimal('0.24'))

How it works

Step 1. Define the SELECT statement

sql = """
SELECT product_id, name, price, safety_stock, gross_weight
FROM products
WHERE product_id = %s
"""

This SELECT statement retrieves one row from the products table where product_id matches the provided value.

Step 2. Prepare the input value

product_id = 1

This is the value that will be bound to the %s placeholder.

Step 3. Execute the query with a parameter

cur.execute(sql, (product_id,))

The second argument is a tuple containing the parameter value.

Note that (product_id,) includes a trailing comma, which makes it a tuple.

Step 4. Fetch one row

row = cur.fetchone()

The fetchone() method returns the next row from the result set.

If no row matches, the fetchone() returns None.

Step 5. Check the result

if row:
    print("Product found:")
    print(row)
else:
    print("Product not found.")

Select multiple rows with a condition #

The following script retrieves products whose price is greater than a given amount:

import psycopg
from config import config

sql = """
SELECT product_id, name, price, safety_stock, gross_weight
FROM products
WHERE price > %s
ORDER BY price DESC
"""

min_price = 900

try:
    with psycopg.connect(**config) as conn:
        with conn.cursor() as cur:
            cur.execute(sql, (min_price,))
            rows = cur.fetchall()

            print("Matching products:")
            for row in rows:
                print(row)
except Exception as e:
    print(f"Error: {e}")

Output:

Matching products:
(4, 'Xperia 1 IV', Decimal('1299.99'), 7, Decimal('0.19'))
(1, 'iPhone 14 Pro', Decimal('1199.99'), 20, Decimal('0.24'))
(2, 'Galaxy S23 Ultra', Decimal('949.99'), 9, Decimal('0.23'))

How it works

Step 1. Define the SELECT statement

sql = """
SELECT product_id, name, price, safety_stock, gross_weight
FROM products
WHERE price > %s
ORDER BY price DESC
"""

This SELECT statement:

Filters rows using WHERE price > %s
Sorts matching rows by price in descending order

Step 2. Prepare the parameter

min_price = 900

This value will be passed safely into the query.

Step 3. Execute the query

cur.execute(sql, (min_price,))

Psycopg replaces %s with the provided value safely.

Step 4. Fetch all matching rows

rows = cur.fetchall()

The fetchall() returns all rows that match the condition.

Step 5. Print the results

print("Matching products:")
for row in rows:
    print(row)

This code loops through the result and displays each row.

Select a limited number of rows with fetchmany() #

Sometimes, you may not want to load all rows at once. In these cases, you can use fetchmany() to retrieve a limited number of rows.

import psycopg
from config import config

sql = """
SELECT product_id, name, price, safety_stock, gross_weight
FROM products
ORDER BY product_id
"""

try:
    with psycopg.connect(**config) as conn:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchmany(2)

            print("First 2 rows:")
            for row in rows:
                print(row)
except Exception as e:
    print(f"Error: {e}")

Output:

First 2 rows:
(1, 'iPhone 14 Pro', Decimal('999.99'), 10, Decimal('0.24'))
(2, 'Galaxy S23 Ultra', Decimal('1199.99'), 15, Decimal('0.23'))

How it works

Step 1. Execute the query

cur.execute(sql)

Step 2. Fetch a fixed number of rows

rows = cur.fetchmany(2)

The fetchmany(2) retrieves up to 2 rows from the result set.

The fetchmany can be useful when you want to process rows in smaller batches and do not want to load everything into memory at once.

Select specific columns only #

In practice, you do not always need every column in a table. For example, this script retrieves only the product name and price:

import psycopg
from config import config

sql = """
SELECT name, price
FROM products
ORDER BY name
"""

try:
    with psycopg.connect(**config) as conn:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall()

            for row in rows:
                print(row)
except Exception as e:
    print(f"Error: {e}")

Example output:

('Galaxy S23 Ultra', Decimal('949.99'))
('iPhone 14 Pro', Decimal('1199.99'))
('Pixel 7 Pro', Decimal('899.99'))
('Xperia 1 IV', Decimal('1299.99'))

This can make the program run faster when you only need a few columns.

Why use parameterized queries #

When retrieving data with conditions, you should always use placeholders like %s instead of building SQL strings manually.

Good:

sql = "SELECT * FROM products WHERE product_id = %s"
cur.execute(sql, (1,))

Avoid:

sql = f"SELECT * FROM products WHERE product_id = {product_id}"

Parameterized queries are better because they help prevent SQL injection, let Psycopg convert Python values safely, and keep your code cleaner and easier to maintain.

Summary #

Use psycopg.connect() to open a connection to PostgreSQL.
Use execute() to execute a query.
Use %s placeholders and pass values separately for safe filtering.
Use fetchone() to retrieve one row.
Use fetchall() to retrieve all matching rows.
Use fetchmany() to retrieve a limited number of rows.
Use with blocks to manage connections and cursors automatically.
Use try/except to catch errors and make debugging easier.

Was this helpful?