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?
Add a comment
|
2 Answers
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).
3 Comments
Louis Marie
I got the following error when I tried the first code: raise AttributeError( AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Question.id has an attribute 'not_in' and this is my code: nextQuestion = Question.query.filter_by( Question.id.not_in(previousQuestions), Question.category == category_id).one() where previousquestions is the except list
snakecharmerb
It seems there may be something wrong with your models - for example see this Q&A. The code in the answer works - I've tested it twice.
snakecharmerb
I've modified the example to make it more like your code. If you can't make it work you'll need to ask a new question and provide a minimal reproducible example.
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()
1 Comment
Louis Marie
I got the following error when I tried it: nextQuestion = Question.query( TypeError: 'BaseQuery' object is not callable