0

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?

Louis Marie
  • 9
  • 2

2 Answers2

1

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

snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
  • 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 – Louis Marie Aug 10 '22 at 17:09
  • It seems there may be something wrong with your models - for example see [this Q&A](https://stackoverflow.com/q/16589208/5320906). The code in the answer works - I've tested it twice. – snakecharmerb Aug 11 '22 at 07:00
  • 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 [mre]. – snakecharmerb Aug 11 '22 at 08:17
0

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()
meril
  • 462
  • 3
  • 13
  • I got the following error when I tried it: nextQuestion = Question.query( TypeError: 'BaseQuery' object is not callable – Louis Marie Aug 10 '22 at 17:08