Flask SQLAlchemy query, specify column names

Flask

SQLAlchemy

query

column names

Python

Flask SQLAlchemy query, specify column names

Data Structures & Algorithms practice on Codemia

Step through 300+ coding interview problems with animated visualisers that show the data structure changing as the code runs. The Blind 75 are free.

Practice algorithms

Python

Introduction

When querying with Flask-SQLAlchemy, the default Model.query.all() loads all columns for every row. For performance and clarity, you often want to select only specific columns — reducing data transfer, memory usage, and serialization overhead. Flask-SQLAlchemy provides several ways to specify columns: db.session.query(), with_entities(), load_only(), and hybrid properties.

Setup

python

1from flask import Flask
2from flask_sqlalchemy import SQLAlchemy
3
4app = Flask(__name__)
5app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'
6db = SQLAlchemy(app)
7
8class User(db.Model):
9    id = db.Column(db.Integer, primary_key=True)
10    username = db.Column(db.String(80), nullable=False)
11    email = db.Column(db.String(120), nullable=False)
12    bio = db.Column(db.Text)
13    avatar_url = db.Column(db.String(255))
14    created_at = db.Column(db.DateTime, default=db.func.now())

Method 1: db.session.query() with Specific Columns

Pass column objects directly to db.session.query():

python

1# Select only username and email
2results = db.session.query(User.username, User.email).all()
3print(results)
4# [('alice', '[email protected]'), ('bob', '[email protected]')]
5
6# Each result is a named tuple — access by name or index
7for row in results:
8    print(row.username, row.email)
9    # Or: print(row[0], row[1])
10
11# With filters
12results = db.session.query(User.username, User.email)\
13    .filter(User.username.like('a%'))\
14    .order_by(User.username)\
15    .all()

The results are Row objects (named tuples), not User model instances. You cannot access other columns or call model methods on them.

Method 2: with_entities()

Chain .with_entities() onto an existing query:

python

1# Select specific columns from a model query
2results = User.query.with_entities(User.username, User.email).all()
3
4# Combine with filters
5results = User.query\
6    .filter(User.created_at > '2026-01-01')\
7    .with_entities(User.id, User.username)\
8    .all()
9
10# With aggregations
11from sqlalchemy import func
12
13result = User.query.with_entities(func.count(User.id)).scalar()
14print(f"Total users: {result}")

Method 3: load_only() for Deferred Loading

Load a full model instance but only fetch specific columns from the database:

python

1from sqlalchemy.orm import load_only
2
3# Returns User objects, but only id, username, email are loaded
4users = User.query.options(load_only(User.id, User.username, User.email)).all()
5
6for user in users:
7    print(user.username)    # Loaded — no extra query
8    print(user.bio)         # Triggers a lazy load (extra SELECT) — loads on access

Unlike with_entities(), load_only() returns actual model instances. Other columns are deferred — they trigger a separate query when accessed.

Method 4: defer() for Excluding Columns

The inverse of load_only() — exclude specific heavy columns:

python

1from sqlalchemy.orm import defer
2
3# Load all columns EXCEPT bio and avatar_url
4users = User.query.options(
5    defer(User.bio),
6    defer(User.avatar_url)
7).all()
8
9for user in users:
10    print(user.username)   # Loaded
11    print(user.email)      # Loaded
12    # user.bio             # Would trigger lazy load if accessed

This is useful when you have large text or blob columns that you rarely need.

Method 5: values() and values_list() (Flask-specific)

Using raw query with column selection:

python

1# Using db.session and column labels
2results = db.session.query(
3    User.username.label('name'),
4    User.email.label('contact')
5).all()
6
7for row in results:
8    print(row.name, row.contact)

Selecting Columns from Multiple Tables (JOINs)

python

1class Post(db.Model):
2    id = db.Column(db.Integer, primary_key=True)
3    title = db.Column(db.String(200))
4    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
5
6# Join and select specific columns from both tables
7results = db.session.query(User.username, Post.title)\
8    .join(Post, User.id == Post.user_id)\
9    .all()
10
11for row in results:
12    print(f"{row.username}: {row.title}")

Aggregation with Column Selection

python

1from sqlalchemy import func
2
3# Group by with aggregation
4results = db.session.query(
5    User.username,
6    func.count(Post.id).label('post_count')
7).outerjoin(Post).group_by(User.username).all()
8
9for row in results:
10    print(f"{row.username}: {row.post_count} posts")

Converting Results to Dictionaries

python

1# Named tuple to dict
2results = db.session.query(User.username, User.email).all()
3users_list = [{'username': r.username, 'email': r.email} for r in results]
4
5# Using _asdict() (available on Row objects)
6users_list = [row._asdict() for row in results]
7print(users_list)
8# [{'username': 'alice', 'email': '[email protected]'}, ...]
9
10# For JSON API responses
11from flask import jsonify
12return jsonify(users_list)

Comparison of Methods

Method	Returns	Lazy loading	Use case
`query(Model.col)`	Named tuples	No	Select specific columns
`with_entities()`	Named tuples	No	Modify existing query
`load_only()`	Model instances	Yes (for other cols)	Need model methods
`defer()`	Model instances	Yes (for deferred cols)	Exclude heavy columns

Common Pitfalls

Named tuples are not model instances: Results from query(User.username) and with_entities() are named tuples, not User objects. You cannot call model methods, access relationships, or use user.id unless you included User.id in the query.
Deferred column N+1: With load_only(), accessing a deferred column triggers a separate SQL query per row. If you access user.bio in a loop of 100 users, it generates 100 extra queries. Either include the column in load_only() or use with_entities().
Column name conflicts in joins: When two tables have the same column name (e.g., User.id and Post.id), use .label() to disambiguate: User.id.label('user_id').
Forgetting .all(): User.query.with_entities(User.username) returns a query object, not results. Call .all(), .first(), or iterate to execute the query.
Using db.session.query vs Model.query: Both work, but db.session.query(User.col) is the standard SQLAlchemy way, while User.query.with_entities() is Flask-SQLAlchemy specific. The former is more portable.

Summary

Use db.session.query(User.username, User.email) to select specific columns (returns named tuples)
Use .with_entities() to modify an existing query to return specific columns
Use load_only() when you need model instances but want to limit loaded columns
Use defer() to exclude large columns (text, blob) from default loading
Results from column-specific queries are named tuples — use ._asdict() for dictionary conversion

Tackling System Design Interview Problems

A short course that equips you with the skills to approach system design interviews methodically.

Start the free course

Basis Lab

No course covers this one yet

Basis Lab writes one for you from a sentence about what you want to be able to do, then teaches it and checks you understood. Your first course is free.

Build my course

Data Structures & Algorithms practice on Codemia

Step through 300+ coding interview problems with animated visualisers that show the data structure changing as the code runs. The Blind 75 are free.

Practice algorithms

Flask SQLAlchemy query, specify column names

Data Structures & Algorithms practice on Codemia

Introduction

Setup

Method 1: db.session.query() with Specific Columns

Method 2: with_entities()

Method 3: load_only() for Deferred Loading

Method 4: defer() for Excluding Columns

Method 5: values() and values_list() (Flask-specific)

Selecting Columns from Multiple Tables (JOINs)

Aggregation with Column Selection

Converting Results to Dictionaries

Comparison of Methods

Common Pitfalls

Summary

Related reading

Tackling System Design Interview Problems

No course covers this one yet

Data Structures & Algorithms practice on Codemia