import sqlite3
import pandas as pd

# Connect to the SQLite database file
conn = sqlite3.connect("my_database.db")

# Write a SQL SELECT query
query = "SELECT * FROM users WHERE signup_date >= '2023-01-01'"

# Read the result directly into a pandas DataFrame
df = pd.read_sql_query(query, conn)

# Preview the data
print(df.head())

# If working with very large tables, read data in chunks
chunk_iter = pd.read_sql_query(query, conn, chunksize=1000)
for chunk in chunk_iter:
    # Process each chunk (for demonstration, just print shape)
    print("Chunk shape:", chunk.shape)

# Always close the connection when done
conn.close()

Working with SQL databases is a common requirement in data pipelines. SQLite is a lightweight, file-based database that is easy to use for both prototyping and production-scale applications. To extract data from SQLite using Python, you typically use the sqlite3 library to manage the connection and pandas to handle data queries and manipulation.

A SQL SELECT query allows you to specify which columns and rows to retrieve from a table. The example above demonstrates how to connect to a SQLite database file, execute a SELECT statement, and load the results directly into a pandas DataFrame. This approach makes it easy to analyze and transform your data using pandas' powerful features.

Connection management is essential to avoid resource leaks and ensure reliable pipeline execution. Always close the database connection after your extraction tasks are complete.

When dealing with very large tables, loading all rows into memory at once may not be practical. pandas supports reading SQL query results in chunks using the chunksize parameter. This technique allows you to process large datasets iteratively, handling one chunk at a time, which is more memory efficient and suitable for scalable pipelines.