list cant be passed as the parameter

Open
#503 2 comments 2 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
3/5
Estimated time
1-2 days
Newbie friendliness
38/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Stale
Tech stack
python, sql
Domain
databases

Research direction

Start with docs/parameters.md and run the supplied Python reproduction against Databricks SQL Connector 4.0.0, focusing on the list-valued parameter examples and the reported type-inference error. Trace the parameter handling entry point referenced by the documentation, then verify the intended behavior with a regression test; done means the documented list-parameter cases behave consistently or the documentation clearly states their limits.

Written by the indexing model from the issue text.

Description

enhancement

Versions:
Python: 3.12
Databricks SQL Connector: 4.0.0
polars: 1.16.0

I am trying to pass list parametes as it was mentined in docs:
https://github.com/databricks/databricks-sql-python/blob/main/docs/parameters.md

Steps to reproduce:

  1. Create and populate table:
    ` CREATE TABLE epam.base.tmp_params_table (
    product_code VARCHAR(15),
    qty int,
    BU VARCHAR(15)
    );

INSERT INTO epam.base.tmp_params_table (product_code, qty, BU)
VALUES
('prod_cd_1', 5, 'BU_1'),
('prod_cd_2', 4, 'BU_1'),
('prod_cd_3', 3, 'BU_1'),
('prod_cd_4', 3, 'BU_2'),
('prod_cd_5', 1, 'BU_2');`

  1. Run queries using connector:
    `import polars as pl
    from configs.databricks_config import databricks_settings
    from databricks import sql
    from databricks.sql.parameters import StringParameter, IntegerParameter

query_params_list = [
[
"SELECT product_code, qty, BU from epam.base.tmp_params_table where product_code in (:product_code) and BU= :BU",
{"product_code": ["prod_cd_1", "prod_cd_2", "prod_cd_4"], "BU": ["BU_1"]},
],
[
"SELECT product_code, qty, BU from epam.base.tmp_params_table where product_code in (:product_code) and BU= :BU",
[
StringParameter(name="product_code", value="prod_cd_1, prod_cd_2, prod_cd_4"),
StringParameter(name="BU", value="BU_1"),
],
],
[
"SELECT product_code, qty, BU from epam.base.tmp_params_table where qty in %(:qty)s",
{"qty": [1, 2, 3]},
],
["SELECT product_code, qty, BU from :tab_name", {"tab_name": "epam.base.tmp_params_table"}],
]

with sql.connect(
server_hostname=databricks_settings.host,
http_path=databricks_settings.http_path,
access_token=databricks_settings.api_token,
) as connection:
with connection.cursor() as cursor:
for num, record in enumerate(query_params_list):
query, params = record
try:
cursor.execute(query, params)
table = cursor.fetchall_arrow()
res_df = pl.from_arrow(table)
print(num, res_df.shape, params)
except Exception as error:
print(num, error, params)`

and I see this error:
databricks.sql.exc.NotSupportedError: Could not infer parameter type from value: ['prod_cd_1', 'prod_cd_2', 'prod_cd_4'] - <class 'list'> Please specify the type explicitly.

Dominant language
Python
Stars
233
Forks
152
Avg merge
21h 5m
Merged PRs (30d)
10

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from databricks/databricks-sql-python

All issues in databricks/databricks-sql-python

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.