CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
olleh
olleh

Reputation: 1277

Python SQL Alchemy how to query by excluding selected columns

I basically just need to know how to query by excluding selected columns. Is this possible?

Example: I have table which has id, name, age, address, location, birth, age, sex... etc.

Instead of citing out the columns to retrieve, I'd like to just exclude some columns in the query(exclude age for example).

Sample code:

db.session.query(User.username).filter_by(username = request.form['username'], password = request.form['password']).first()

Last thing I wanna do is to list down all the attributes on the query() method, since this would be pretty long especially when you have lots of attributes, thus I just wanna exclude some columns.

Upvotes: 12

Views: 20295

Answers (4)

davidism
davidism

Reputation: 127180

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.

Upvotes: 17

Azharullah Shariff
Azharullah Shariff

Reputation: 357

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)

Upvotes: 1

snakecharmerb
snakecharmerb

Reputation: 55600

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.

Upvotes: 1

dmvianna
dmvianna

Reputation: 15718

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()

Upvotes: -1

Related Questions