Sqlalchemy expression IN is not working

[ReshnaNP]

I have tried the below example from the documentation, but it is not working.
Example
http://127.0.0.1:5000/people?where={"firstname":"in(\"(\'John\',\'Fred\'\"))"}

Please help me to understand, how to use IN sqlalchemy expression?

[amoyiki]

I have same problem
if i used http://127.0.0.1:5000/biddings?where={"id":"in(\"(10,12\"))"}
raw sql like

SELECT ...
FROM tabel
WHERE table.id = %s [<sqlalchemy.sql.elements.BinaryExpression object at 0x04CFA790>]

I used http://127.0.0.1:5000/people?where={"id":"in(\"(10,12)\")"}
raw sql like

SELECT ...
FROM tabel
WHERE table.id in %s [<sqlalchemy.sql.elements.BinaryExpression object at 0x04CFA790>]

but catch a error

sqlalchemy.exc.ProgrammingError: (_mysql_exceptions.ProgrammingError) 
(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''(10,12)'

print sql

SELECT ...
FROM tabel
WHERE table.id  IN '(10,12)'

'(10,12)' has single quote
Thanks a bunch for any help! ;)

[amoyiki]

OK, I know, that documentation example is missing
http://127.0.0.1:5000/people?where={"id":"in([10,12])"}
The above can work.
Am I right? @dkellner

[ReshnaNP]

Hi amoyiki,
Thanks for the reply :)
I have tried , http://127.0.0.1:5000/people?where={"id":"in([10,12])"}.
It does not seems to work (tried for PostgreSQL database server ).

[amoyiki]

yes, use "in([10,12])" not work, my mistake,sorry

[amoyiki]

@ReshnaNP i think you can use
where={"or_": [{"id":"__eq__(10)"}, {"id": "__eq__(12)"}]}
instead of in

[amoyiki]

@ReshnaNP
?where={"user_id":[4,5]} can work
you can see tests/sql.py

[LaQuay]

Hi, i'm facing the same issue.

Here I add my scenario, the syntax follows the documentation.

param = {
    "title": "in(\"('NAME_1', 'NAME_2')\")"
}
url = "/my_endpoint/?where=" + json.dumps(param)
api_client.get(url)

Is generating this SQLExpression, which is not correct.

WHERE my_model.title in '(''NAME_1'', ''NAME_2'')'

This should generate something like:

WHERE my_model.title in ('NAME_1', 'NAME_2')

Also, as @amoyiki said, this is working

param = {
    "title": ['NAME_1', 'NAME_2']
}
url = "/my_endpoint/?where=" + json.dumps(param)
api_client.get(url)