Error with SQLAlchemy when query by list with "in_"

Question

I keep receiving the error: sqlalchemy.exc.ArgumentError: Object 1 is not legal as a SQL literal value when executing this query:

cat_list = [1,2,3,4,5,6]
my_cats = Category.query.filter(Category.id.in_(cat_list)).all()

My model:

class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(120), index=True)

I have also tried

my_cats = db.session.query(Category).filter(Category.id.in_(cat_list)).all()
my_cats = Category.query.where(Category.id.in_(cat_list)).all()

It is the same error on all of them. Question:

What is Object 1 referring to? "Category" or "Category.id" or my cat_list?
How do I fix this error?

EDIT: I did some further work and when I make my list via a for loop and then print:

print(cat_list_fromloop)
>> [1, 2, 3, 4, 5, 6]
print(cat_list)
>> [1, 2, 3, 4, 5, 6]
print(type(cat_list_fromloop))
>> <class 'list'>
print(type(cat_list))
>> <class 'list'>
if cat_list == cat_listfromloop:
    print("same")
else:
    print("not same")
>> not same

So this suggests that how I am making my list is probably what is causing the issue. I am doing this via:

template = Template.query.filter_by(id=int(form.template.data)).first()
cat_listfromloop = []
for val in template.categories:
    cat_listfromloop.append(val)

I think it failing the equality check is a hint, but still don't understand why.

Edit 2: I got it to work after I checked type of each item in list. I needed to make sure it was an int.

template = Template.query.filter_by(id=int(form.template.data)).first()
cat_listfromloop = []
for val in template.categories:
    cat_listfromloop.append(int(val.id))

Is Object 1 is not legal as a SQL literal value literally the error message you receive, or do you get soething like sqlalchemy.exc.ArgumentError: Object <Foo object at 0x7fa417a3b400> is not legal as a SQL literal value? — snakecharmerb
– snakecharmerb, Commented Jul 12, 2021 at 7:29
@snakecharmerb Yes, it is the sqlalchemy.exc.ArgumentError: Object 1 is not legal as a SQL literal value. See the edit above. When I manually make the list with hardcoded values it works. When I make it via a loop, it fails. When I check the type() of each list I get <class 'list'>. So, it must be something with how I am creating the list (see last code block in Edit — user1563247
– user1563247, Commented Jul 12, 2021 at 9:46
It looks like you've solved your issue, but here's a link that provides some background information about the error message: docs.sqlalchemy.org/en/14/changelog/… — snakecharmerb
– snakecharmerb, Commented Jul 12, 2021 at 13:07

c8999c 3f964f64 · Accepted Answer · 2022-05-16 08:59:10Z

2

Try making catlist a tuple:

cat_list = [1, 2, 3, 4, 5, 6]
cat_list = tuple(cat_list)

There's more answers here SQLAlchemy IN clause, but I think this might already solve your problem.

edited May 16, 2022 at 8:59

answered Jul 12, 2021 at 12:38

c8999c 3f964f64

1,6491 gold badge18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PicxyB Over a year ago

You probably mean: tuple((1, 2, 3)) Tuple takes only 1 argument

c8999c 3f964f64 Over a year ago

yes, a common mistake, I meant to use the cat_list as the input for the tuple, like so: cat_list = [1, 2, 3, 4, 5, 6] tuple(cat_list)

Collectives™ on Stack Overflow

Error with SQLAlchemy when query by list with "in_"

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related