The SELECT statement is one of the most powerful tools in SQL, allowing you to fetch data from your database. In this tutorial, you'll learn how to execute SELECT queries using Python and PostgreSQL, retrieve results, and handle them efficiently using psycopg2.

Table of Contents

1. What You’ll Learn

2. Prerequisites

3. Setting Up

4. Using SELECT in Python

5. Fetching Results: fetchone(), fetchmany(), fetchall()

6. Filtering Data with WHERE

7. Full Working Example

8. Tips and Common Pitfalls

1. What You’ll Learn

• How to use SELECT queries with Python and PostgreSQL

• How to retrieve and iterate over query results

• How to use different fetch methods

• How to filter and format the output

✅ 2. Prerequisites

You’ll need:

• PostgreSQL installed and running

• Python and psycopg2 installed

• A PostgreSQL table (e.g., students) populated with data

3. Setting Up

First, install psycopg2 if you haven’t already:

pip install psycopg2-binary

Assume you have a table like:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade VARCHAR(10)
);

And it contains a few rows of data.

4. Using SELECT in Python

Here's how to perform a simple SELECT * query:

import psycopg2

conn = psycopg2.connect(
    dbname="school",
    user="postgres",
    password="your_password",
    host="localhost",
    port="5432"
)

cur = conn.cursor()

cur.execute("SELECT * FROM students")
rows = cur.fetchall()

for row in rows:
    print(row)

cur.close()
conn.close()

5. Fetching Results

You can retrieve results in three main ways:

Example:

# Fetch first record only
cur.execute("SELECT * FROM students")
record = cur.fetchone()
print(record)

# Fetch next 2 records
records = cur.fetchmany(2)
print(records)

6. Filtering Data with WHERE

You can use WHERE clauses to filter data:

cur.execute("SELECT * FROM students WHERE age > %s", (13,))
rows = cur.fetchall()

for row in rows:
    print(row)

✅ Always use parameterized queries to avoid SQL injection.

7. Full Working Example

import psycopg2

try:
    conn = psycopg2.connect(
        dbname="school",
        user="postgres",
        password="your_password",
        host="localhost",
        port="5432"
    )
    cur = conn.cursor()

    # Select all students
    cur.execute("SELECT * FROM students")
    students = cur.fetchall()

    print("Student Records:")
    for student in students:
        print(f"ID: {student[0]}, Name: {student[1]}, Age: {student[2]}, Grade: {student[3]}")

    cur.close()
    conn.close()

except psycopg2.Error as e:
    print("Database error:", e)

8. Tips and Common Pitfalls

Conclusion

Using SELECT in Python with PostgreSQL is a core skill for any data-driven application. With psycopg2, you can retrieve data, apply filters, and format results for further processing. Whether you're building a web app or analyzing data, mastering SELECT is essential.