After inserting data into your SQLite database, the next step is learning how to retrieve it. The SELECT statement is one of the most important SQL commands—it allows you to read data from your tables.

In this article, you'll learn how to use Python's built-in sqlite3 module to:

• Run basic SELECT queries

• Fetch one or all rows

• Use filtering, sorting, and column selection

• Avoid common mistakes

✅ Prerequisites

• Python 3.x

• An SQLite database with a table and some data inserted
  (e.g., a Users table with columns like id, name, email, age)

Step 1: Connect to the Database

Start by importing sqlite3 and connecting to your database.

import sqlite3

conn = sqlite3.connect("example.db")
cursor = conn.cursor()

Step 2: Run a Basic SELECT Query

To retrieve all records from the Users table:

cursor.execute("SELECT * FROM Users")
rows = cursor.fetchall()

for row in rows:
    print(row)

Output example:

(1, 'Alice', '[email protected]', 30)
(2, 'Bob', '[email protected]', 25)

* selects all columns. You can also specify columns like SELECT name, email.

Fetching Methods Explained

Example: Using fetchone()

cursor.execute("SELECT * FROM Users")
row = cursor.fetchone()
print(row)

Step 3: Filter Results Using WHERE

You can select specific rows using the WHERE clause.

cursor.execute("SELECT * FROM Users WHERE age > 30")
rows = cursor.fetchall()

for row in rows:
    print(row)

Returns users where age is greater than 30.

Step 4: Select Specific Columns

To select only name and email:

cursor.execute("SELECT name, email FROM Users")
rows = cursor.fetchall()
for row in rows:
    print(row)

Step 5: Use Parameterized Queries for Safety

Avoid string concatenation to prevent SQL injection:

age_limit = 25
cursor.execute("SELECT * FROM Users WHERE age > ?", (age_limit,))
rows = cursor.fetchall()

Use ? placeholders and pass a tuple for safe queries.

Full Working Example: SELECT with Python and SQLite

import sqlite3

conn = sqlite3.connect("example.db")
cursor = conn.cursor()

# Select all users
cursor.execute("SELECT id, name, email FROM Users")
rows = cursor.fetchall()

print("All users:")
for row in rows:
    print(row)

# Select users over age 30
cursor.execute("SELECT * FROM Users WHERE age > ?", (30,))
for row in cursor.fetchall():
    print("Over 30:", row)

conn.close()

Tips for Using SELECT in SQLite with Python

⚠️ Common Pitfalls

Best Practices

• Always close the database connection with conn.close().

• Structure SELECT queries to return only necessary data.

• Use list comprehensions to cleanly process results:

names = [row[0] for row in cursor.execute("SELECT name FROM Users")]

Conclusion

Using the SELECT statement in Python with SQLite is straightforward and powerful. Whether you're retrieving all records or just one, filtering data, or displaying it on the web or CLI, the combination of SQL and Python makes it easy to work with databases in a clean and efficient way.