Binding data in type (list) is not supported

Question

I wrote this script to execute a query and return the results to a data frame. It works like a charm and is quite fast. Now, I want to give the script bind parameters at runtime, a customer id and a date.

from snowflake.snowpark.functions import *
from snowflake.snowpark import *
import pandas as pd
import sys

# Parameter style has to be set before connection
snowflake.connector.paramstyle = 'numeric'

# Connection
conn = snowflake.connector.connect (
    account=...
)

try:
    print('Session started...')
 # Session Handle
    cs = conn.cursor()

 # Debug
    print(f"Arguments : {sys.argv[1:]=}")

 # Query with bin variables
    Query = "with dims as (\n" \
 "  select...
 "  from revenue where site_id = :1 and subscription_product = TRUE and first_or_recurring = 'First'\n" \
 "  qualify row_number = 1\n" \
...
 ")\n" \
 "select to_date(:2) as occurred_date, count(distinct srm.subscription_id) as ..."

 # Bind variables - create list first
    params = [ [sys.argv[1]] ]
    cs.executemany(Query, [ [params] ] )

 # This works
 # cs.execute(Query, ['ksisk5kZRvk', '2022-10-28'])

 # Write to a dataframe & show
    df = cs.fetch_pandas_all()
    print(df.head(1))

 # Cleanup
    cs.close()
    conn.close()

except Exception as e:
        print(e)
finally:
 if conn:
        cs.close()
        conn.close()
    print('Done...')

At runtime, I get the following error:

myscript.py ksisk5kZRvk 2022-10-28
session started...
Arguments : sys.argv[1:]=['ksisk5kZRvk,', '2022-10-28']
255001: Binding data in type (list) is not supported.
done...

Ok, so I change params to be a tuple instead

 ... params = ( (sys.argv[1]) )
    cs.execute(Query, ( (params) ) )...

And I get a new error

myscript.py ksisk5kZRvk 2022-10-28
session started...
Arguments : sys.argv[1:]=['ksisk5kZRvk,', '2022-10-28']
252004: Binding parameters must be a list: ksisk5kZRvk,
done...

Obviously, I am missing something here. I think the issue is how I use sys.argv[1:] but I can't figure it out. Any ideas? Thanks in advance!

Answer 1

My guess is that you are bundling a list into a list into a list. Here:

   params = [ [sys.argv[1]] ]
   cs.executemany(Query, [ [params] ] )

Instead try:

   params = [ [sys.argv[1]] ]
   cs.executemany(Query, params)

Or just:

   cs.executemany(Query, [sys.argv[1]])