1

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:

  1. What is Object 1 referring to? "Category" or "Category.id" or my cat_list?
  2. 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))
user1563247
  • 516
  • 1
  • 7
  • 20
  • 1
    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 is not legal as a SQL literal value`? – snakecharmerb Jul 12 '21 at 07: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 ``. So, it must be something with how I am creating the list (see last code block in Edit – user1563247 Jul 12 '21 at 09:46
  • 1
    It looks like you've solved your issue, but here's a link that provides some background information about the error message: https://docs.sqlalchemy.org/en/14/changelog/migration_11.html#specific-checks-added-for-passing-mapped-classes-instances-as-sql-literals – snakecharmerb Jul 12 '21 at 13:07

1 Answers1

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.

c8999c 3f964f64
  • 1,430
  • 1
  • 12
  • 25