docs/source/user-guide/sql.md

---
jupytext:
  text_representation:
    extension: .md
    format_name: myst
kernelspec:
  name: python3
  display_name: Python 3
---
<!---
  Licensed to the Apache Software Foundation (ASF) under one
  or more contributor license agreements.  See the NOTICE file
  distributed with this work for additional information
  regarding copyright ownership.  The ASF licenses this file
  to you under the Apache License, Version 2.0 (the
  "License"); you may not use this file except in compliance
  with the License.  You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing,
  software distributed under the License is distributed on an
  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
  KIND, either express or implied.  See the License for the
  specific language governing permissions and limitations
  under the License.
-->

# SQL

DataFusion also offers a SQL API, read the full reference [here](https://arrow.apache.org/datafusion/user-guide/sql/index.html)

```{code-cell} ipython3
import datafusion
from datafusion import DataFrame, SessionContext

# create a context
ctx = datafusion.SessionContext()

# register a CSV
ctx.register_csv("pokemon", "pokemon.csv")

# create a new statement via SQL
df = ctx.sql('SELECT "Attack"+"Defense", "Attack"-"Defense" FROM pokemon')

# collect and convert to pandas DataFrame
df.to_pandas()
```

## Parameterized queries

In DataFusion-Python 51.0.0 we introduced the ability to pass parameters
in a SQL query. These are similar in concept to
[prepared statements](https://datafusion.apache.org/user-guide/sql/prepared_statements.html),
but allow passing named parameters into a SQL query. Consider this simple
example.

```{code-cell} ipython3
def show_attacks(ctx: SessionContext, threshold: int) -> None:
    ctx.sql(
        'SELECT "Name", "Attack" FROM pokemon WHERE "Attack" > $val', val=threshold
    ).show(num=5)
show_attacks(ctx, 75)
```

When passing parameters like the example above we convert the Python objects
into their string representation. We also have special case handling
for {py:class}`~datafusion.dataframe.DataFrame` objects, since they cannot simply
be turned into string representations for an SQL query. In these cases we
will register a temporary view in the {py:class}`~datafusion.context.SessionContext`
using a generated table name.

The formatting for passing string replacement objects is to precede the
variable name with a single `$`. This works for all dialects in
the SQL parser except `hive` and `mysql`. Since these dialects do not
support named placeholders, we are unable to do this type of replacement.
We recommend either switching to another dialect or using Python
f-string style replacement.

:::{warning}
To support DataFrame parameterized queries, your session must support
registration of temporary views. The default
{py:class}`~datafusion.catalog.CatalogProvider` and
{py:class}`~datafusion.catalog.SchemaProvider` do have this capability.
If you have implemented custom providers, it is important that temporary
views do not persist across {py:class}`~datafusion.context.SessionContext`
or you may get unintended consequences.
:::

The following example shows passing in both a {py:class}`~datafusion.dataframe.DataFrame`
object as well as a Python object to be used in parameterized replacement.

```{code-cell} ipython3
def show_column(
    ctx: SessionContext, column: str, df: DataFrame, threshold: int
) -> None:
    ctx.sql(
        'SELECT "Name", $col FROM $df WHERE $col > $val',
        col=column,
        df=df,
        val=threshold,
    ).show(num=5)
df = ctx.table("pokemon")
show_column(ctx, '"Defense"', df, 75)
```

The approach implemented for conversion of variables into a SQL query
relies on string conversion. This has the potential for data loss,
specifically for cases like floating point numbers. If you need to pass
variables into a parameterized query and it is important to maintain the
original value without conversion to a string, then you can use the
optional parameter `param_values` to specify these. This parameter
expects a dictionary mapping from the parameter name to a Python
object. Those objects will be cast into a
[PyArrow Scalar Value](https://arrow.apache.org/docs/python/generated/pyarrow.Scalar.html).

Using `param_values` will rely on the SQL dialect you have configured
for your session. This can be set using the {ref}`configuration options <configuration>`
of your {py:class}`~datafusion.context.SessionContext`. Similar to how
[prepared statements](https://datafusion.apache.org/user-guide/sql/prepared_statements.html)
work, these parameters are limited to places where you would pass in a
scalar value, such as a comparison.

```{code-cell} ipython3
def param_attacks(ctx: SessionContext, threshold: int) -> None:
    ctx.sql(
        'SELECT "Name", "Attack" FROM pokemon WHERE "Attack" > $val',
        param_values={"val": threshold},
    ).show(num=5)
param_attacks(ctx, 75)
```