Is it possible to pass query parameters? (:param or ?)

[shner-elmo]

Hey, I'm trying to port a query from duckdb to datafusion using the Python connector, the issue I'm facing, is adding query parameters.
Is there currently a way that I can pass query parameters ?

import datafusion
import pyarrow.dataset as ds

dataset = ds.dataset(...)

ctx = datafusion.SessionContext()
ctx.register_dataset('dataset', dataset)
table = ctx.sql("SELECT * FROM dataset WHERE col BETWEEN ? AND ?", parameters=[1, 10]).to_arrow_table()
## TypeError: SessionContext.sql() got an unexpected keyword argument 'parameters'

[MrPowers]

Yea, parameterized queries would be awesome, especially syntax like this:

ctx.sql(
    "select country, count(*) as num_ppl from {person_df} group by country",
    person_df=person_df,
).show()

[timsaucer]

I don't fully understand what is needed here. Can you not just do

ctx.sql(
    f"select country, count(*) as num_ppl from {person_df} group by country"
).show()

[MrPowers]

@timsaucer - In this example, person_df is a DataFrame object, not a table name. This saves the user from having to register the table (the engine just does this under the hood). It was recently added to Spark and is a surprisingly nice dev quality of life improvement!

[timsaucer]

Oh, interesting. As a pure dataframe user, I hadn't understood the request. I appreciate the clarification!

[shner-elmo]

Parametrized queries are even more important for when you want to do: WHERE col IN big_list_of_values.

A year later, I again need to choose a library for reading parquet files, but I had to settle with ducks because data fusion doesn't support this

[timsaucer]

I just tried a quick proof of concept. To get this to work, I did need a name for the table. Would the following be an acceptable solution?

from datafusion import SessionContext
ctx = SessionContext()
ctx.register_parquet("customers", "examples/tpch/data/customer.parquet")
df_customer = ctx.table("customers")
ctx.sql("select c_custkey, c_name from {df}", df=df_customer)

[timsaucer]

I think that if we change all of the read_x like read_parquet over to call register_x and auto generate a table name, then we can support removing the register line and make this work. I tested with parquet with success.