Create compiled query without parameters

[saschahofmann]

In ibis 3.1.0, I am wondering how I generate the query string without parameter placeholders.
e.g. code like this

conn = ibis.postgres.connect(....)
table = conn.get_table(table_name)
q = table.limit(100)
c = q.compile()
str()

returns

SELECT t0.*
FROM <table_name AS t0 
  LIMIT %(param_1)s

The best I could find right now is to add another compile call this time to the sqlalchemy select object like this

c.compile(compile_kwargs={"literal_binds": True})

Is that the easiest way or am I overlooking a method?

[cpcloud]

@saschahofmann That is correct, the old "double compile" trick is how you'd do this right now.

Would it be helpful if we added an API (either a kwarg or separate method) to get this version of the string out?

Note that SQLAlchemy doesn't support literal binding for array parameters so there's currently no way to do that at the moment.

[saschahofmann]

I think it'd be nice to mention it somewhere. Not sure whether it was because I was mostly using the bigquery implementation before or whether it always was the case but compile use to create a string instead of a bigquery object?

For an API, I would prefer a method that independently of the backend creates a query string (of course in the selected dialect). Although, I understand that would not make sense for the pandas backends?

[cpcloud]

You can use ibis.show_sql for this:

import io


buf = io.StringIO()

ibis.show_sql(expr, dialect="...", file=buf)
query = buf.getvalue()

Note that it's meant for pretty printing at the moment which is why it may seem a bit roundabout. We're definitely open to making another API that returns the SQL string itself.

[saschahofmann]

Ok I'll try that!