Python SQL Alchemy how to query by excluding selected columns

Not sure why you're not just fetching the model. When doing that, you can defer loading of certain columns so that they are only queried on access.

db.session.query(User).options(db.defer('location')).filter_by(...).first()

In this example, accessing User.location the first time on an instance will issue another query to get the data.

See the documentation on column deferral: https://docs.sqlalchemy.org/en/20/orm/queryguide/columns.html

Note that unless you're loading huge amounts of data, you won't see any speedup with this. It might actually make things slower since another query will be issued later. I have queries that load thousands of rows with eager-loaded relationships in less than 200ms, so this might be a case of premature optimization.

We can use the Inspection API to get the model's columns, and then create a list of columns that we want.

exclude = {'age', 'registration_date'}
insp = sa.inspect(User)
include = [c for c in insp.columns if c.name not in exclude]

# Traditional ORM style    
with Session() as s:
    q = s.query(*include)
    for row in q:
        print(row.id, row.name)
    print()

# 1.4 style
with Session() as s:
    q = sa.select(*include)
    for row in s.execute(q):
        print(row.id, row.name)
    print()

inspect returns the mapper for the model class; to work with non-column attributes like relationships use one of the mapper's other attributes, such as all_orm_descriptors.

If you're using an object deserializer like marshmallow, it is easier to omit the required fields during the deserialization. https://marshmallow.readthedocs.io/en/latest/api_reference.html#marshmallow.EXCLUDE

The fields to be omitted can be formed dynamically and conditionally excluded. Example:

ModelSchema(exclude=(field1, field2,)).jsonify(records)

I am not aware of a method that does that directly, but you can always get the column keys, exclude your columns, then call the resulting list. You don't need to see what is in the list while doing that.

q = db.session.query(blah blah...)

exclude = ['age']
targ_cols = [x for x in q.first().keys() if x not in exclude]

q.with_entities(targ_cols).all()