How to select specific columns in Python with SQLite
A reusable function that connects to a SQLite database and returns only the requested columns from a given table.
Python code
29 linesimport sqlite3
def select_pruned_columns(db_path, table, columns):
with sqlite3.connect(db_path) as conn:
cursor = conn.cursor()
col_list = ", ".join(columns)
query = f"SELECT {col_list} FROM {table}"
return cursor.execute(query).fetchall()
if __name__ == "__main__":
conn = sqlite3.connect(":memory:")
conn.executescript("""
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
department TEXT,
salary INTEGER
);
INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Engineering', 85000),
('Bob', 'Marketing', 72000),
('Carol', 'Sales', 91000);
""")
conn.commit()
conn.close()
result = select_pruned_columns(":memory:", "employees", ["name", "salary"])
for row in result:
print(row)Output
('Alice', 85000)
('Bob', 72000)
('Carol', 91000)How it works
The function builds a SELECT statement dynamically using the column names passed in, joined with commas. It opens a connection with the with statement so the connection is automatically committed and closed. The cursor's execute method runs the query, and fetchall returns a list of tuples, each matching one row of the selected columns. The __main__ block sets up a mock in-memory employee table to demonstrate the behavior.
Common mistakes
- Forgetting to commit after the connection context block; the `with` block handles commit and close automatically.
- Unsafely interpolating table or column names — this function trusts its inputs and is not injection-proof.
- Passing an empty column list, producing an invalid SQL statement.
Variations
- Use `cursor.description` to dynamically fetch column names from a preceding query with `SELECT *`.
- Parameterize the WHERE clause with `?` placeholders instead of concatenating user input.
Real-world use cases
- Pulling only the fields a dashboard needs instead of the full table row, reducing network overhead.
- Building ad-hoc reports where column names come from a config file or user dropdown.
- Internal tooling that inspects a database schema and lets analysts pick fields to export.
Sponsored
More from Big data & Spark
Keep learning
Related tutorials and quizzes for this topic.