CODEWITHSUNDEEP
sqlalchemyflask-sqlalchemy
Louis Marie
Louis Marie

Reputation: 9

sqlalchemy EXCEPT: select all IDs except some ids

I am trying to query my database with sqlalchemy in python to select all rows except those whose IDs belong to a certain list. Something like this;

exceptList = [1, 3, 5]
db.query.all() except those in exceptList

How do I go about this?

Upvotes: 0

Views: 656

Answers (2)

snakecharmerb
snakecharmerb

Reputation: 55600

Given this initial setup:

class Question(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    category = db.Column(db.String)

db.create_all()

# Assign category alternately: id 1 ->, id 2 -> B etc.
db.session.add_all([Question(category='AB'[n % 2]) for n in range(5)])
db.session.commit()

Let's try to get question for category "A", assuming questions 1 - 3 have already been asked.

If you already have the list, you can do

q = Question.query.filter(Question.id.not_in([1, 2, 3]), Question.category == 'A')
next_question = q.one()
print(next_question.id, next_question.category)

If the exception list must be obtained via a query, you can use an EXCEPT clause:

# Create a filter than defines the rows to skip
skip = Question.query.filter(db.or_(Question.id < 4, Question.category == 'B'))
q = Question.query.except_(skip)
next_question = q.one()
print(next_question.id, next_question.category)

This documentation section describes how to use except_ (though it uses UNION as an example).

Upvotes: 1

meril
meril

Reputation: 582

You can try something like below.

except_list = ["password", "another_column"]
result = session.query(*[c for c in User.__table__.c if c.name not in except_list).all()

Upvotes: 0

Related Questions