PostgreSQL RAW query in SQLAlchemy

Question

I am trying to execute a raw query to a PostgreSQL database that works with SqlAlchemy in a Flask project.

I have a class Project and want to query against an array using the IN operator

This is the query that works:

db.session.execute( "SELECT * \
                     FROM project\
                     WHERE project.id IN (1,2,3)"
                   )

Instead of (1,2,3) I want to pass a variable but cannot figure out how to do that.

I want to do something like:

db.session.execute( "SELECT * \
                   FROM project\
                    WHERE project.id IN 
                    (my_array)"
                   )

I have tried this but does not work:

db.session.execute( "SELECT * \
                         FROM project\
                         WHERE project.id IN 
                          param",
                     {"param":(1,2,3)}
                   )

Please help!:)

score 0 · Answer 1 · answered May 26 '21 at 03:44

0

change your query to this and pass the values as a tuple :

db.session.execute("SELECT * FROM project WHERE project.id IN (%s)",(1,2,3))

answered May 26 '21 at 03:44

eshirvana

23,227
3
22
38

score 0 · Answer 2 · answered May 26 '21 at 05:18

Option-1:

raw_sql_param = """
SELECT  *
FROM    project
WHERE   project.id IN :project_id
"""

stmt = text(raw_sql_param)
stmt = stmt.bindparams(bindparam("project_id", expanding=True))
res = session.execute(stmt, {"project_id": [1, 2, 3]})

Option-2: (postgresql specific)

raw_sql_param = """
SELECT  *
FROM    project
WHERE   project.id = ANY(:project_id)
"""

stmt = text(raw_sql_param)
res = session.execute(stmt, {"project_id": [1, 2, 3]})

See similar question with more expanded answers: How can I bind a list to a parameter in a custom query in sqlalchemy?

PostgreSQL RAW query in SQLAlchemy

2 Answers2