I am trying to make a flask app accept queries with multiple values using sqlalchemy but am running into some issues. Trying to debug I am running into a late evaluation issue and I am trying to view the sqlalchemy query in SQL to make sure that I am querying the Postgres db correctly. So for example I am taking multiple inputs via a normal REST api like:
url_of_api/?var=value1,value2
That is passed to
import flask as f
var = f.request.args.getlist("var")
ap = api_endpoint.search(filters=[vars.var.in_()])
where search is defined as:
def search(cls, filters):
q = Session.query(cls)
return q.filter(*filters).all()
So my issue is this: when I submit the query using sqlalchemy nothing is returned from my database. If I view print(Str(q.filter(*filters)) the filter part of the SQL query looks like this:
WHERE vars.var IN (__[POSTCOMPILE_var_1])
If I "manually" query the database like so:
WHERE vars.var IN ('value1', 'value2')")
I get the expected return.
So my question is this: how do I force the evaluation of the __[POSTCOMPILE_var_1] bit that is the supplied variable values. I need to see what is being passed as a query to the database to debug but have been unable to figure out to get evaluate that. Any ideas?
For anyone else visiting this question since it comes up in google results.
from sqlalchemy.dialects import postgresql # (oracle, mysql etc)
q = sql.compile(dialect=postgresql.dialect(), compile_kwargs={})
print(q) # the SQL it will execute but will have __[POSTCOMPILE_param_1] yet params has :param_1
print(q.params) # the parameters the sql will include
print(q.statement) has :param_1 in it which i think is far more useful
to have it output the exact SQL it would execute with the params included in (the values) - this is the answer OP was looking for.
compile_kwargs={"literal_binds": True}
so
q = sql.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True})
I dont know where this is mentioned in the sqlalchemy docs. That place is scary, but you can find some more info https://docs.sqlalchemy.org/en/20/changelog/migration_14.html and just search for "literal_binds"