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
Cursorobject from theConnectionobject. - Then, execute a
SELECTstatement by calling theexecute()method of the cursor object - After that, retrieve rows using
fetchone(),fetchall(), orfetchmany(). - 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)
);Code language: SQL (Structured Query Language) (sql)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.24Select 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}")
Code language: Python (python)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'))Code language: Python (python)How it works #
Step 1: Import the psycopg library
import psycopgCode language: Python (python)The psycopg module allows Python to connect to PostgreSQL, execute SQL statements, and retrieve data.
Step 2: Import the database configuration
from config import configCode language: Python (python)This imports the database connection settings from config.py. For example:
{
"host": "localhost",
"dbname": "pyinventory",
"user": "postgres",
"password": ""
}Code language: Python (python)Step 3: Define the SQL SELECT statement
sql = """
SELECT product_id, name, price, safety_stock, gross_weight
FROM products
ORDER BY product_id
"""Code language: Python (python)This SELECT statement:
- Retrieves data in
product_id,name,price,safety_stock, andgross_weightcolumns from theproductstable - Sorts them by
product_id
Step 4: Open a database connection
with psycopg.connect(**config) as conn:Code language: Python (python)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:Code language: Python (python)We use a cursor to send an SQL SELECT statement to PostgreSQL and retrieve data.
Step 6: Execute the query
cur.execute(sql)Code language: Python (python)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()Code language: Python (python)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)Code language: Python (python)This loops through each row and displays it.
Step 9: Handle errors
except Exception as e:
print(f"Error: {e}")Code language: Python (python)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}")
Code language: Python (python)Output:
Product found:
(1, 'iPhone 14 Pro', Decimal('1199.99'), 20, Decimal('0.24'))Code language: Python (python)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
"""Code language: Python (python)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 = 1Code language: Python (python)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,))Code language: Python (python)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()Code language: Python (python)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.")
Code language: Python (python)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}")
Code language: Python (python)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'))Code language: Python (python)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
"""Code language: Python (python)This SELECT statement:
- Filters rows using
WHERE price > %s - Sorts matching rows by price in descending order
Step 2. Prepare the parameter
min_price = 900Code language: Python (python)This value will be passed safely into the query.
Step 3. Execute the query
cur.execute(sql, (min_price,))Code language: Python (python)Psycopg replaces %s with the provided value safely.
Step 4. Fetch all matching rows
rows = cur.fetchall()Code language: Python (python)The fetchall() returns all rows that match the condition.
Step 5. Print the results
print("Matching products:")
for row in rows:
print(row)
Code language: Python (python)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}")
Code language: Python (python)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'))
Code language: Python (python)How it works
Step 1. Execute the query
cur.execute(sql)Code language: Python (python)Step 2. Fetch a fixed number of rows
rows = cur.fetchmany(2)Code language: Python (python)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}")
Code language: Python (python)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'))Code language: Python (python)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,))Code language: Python (python)Avoid:
sql = f"SELECT * FROM products WHERE product_id = {product_id}"Code language: Python (python)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
%splaceholders 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
withblocks to manage connections and cursors automatically. - Use
try/exceptto catch errors and make debugging easier.
