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!
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]])