Using variable in Snowflake SQL in Python script

Question

I am trying to create a view that contains a variable in Snowflake SQL. The whole thing is being done in Python script. Initially, I tried the binding variable approach but binding does not work in view creation SQL. Is there any other way I can proceed with this? I have given the code below.

Code:

import snowflake.connector as sf
import pandas

ctx = sf.connect (
    user = 'floatinginthecloud89',
    password = '',
    account = 'nq13914.southeast-asia.azure',
    warehouse = 'compute_wh',    
    database = 'util_db',
    schema = 'public'
)
print("Got the context object")
 
cs = ctx.cursor()
print("Got the cursor object")

column1 = 'attr_name';


try:
     row = cs.execute("select listagg(('''' || attr_name || ''''), ',') from util_db.public.TBL_DIM;")
     rows = cs.fetchall()
     for row in rows:
          print(row)
          print(rows)
          
     row1 = cs.execute("""CREATE OR REPLACE table util_db.public.HIERARCHY_VIEW_2 AS SELECT * FROM (SELECT MSTR.PROD_CODE AS PROD_CODE,DIM.ATTR_NAME AS ATTR_NAME,MSTR.ATTR_VALUE AS ATTR_VALUE FROM TBL_DIM DIM INNER JOIN TBL_MSTR MSTR ON DIM.ATTR_KEY=MSTR.ATTR_KEY ) Q
                  PIVOT (MAX (Q.ATTR_VALUE) FOR Q.ATTR_NAME IN (*row))
                  AS P
                  ORDER BY P.PROD_CODE;""")
     rows1 = cs.fetchall()
     for row1 in rows1:
          print(row1)     
          

finally:
     cs.close()     
ctx.close()

Error:

File "C:\Users\Anand Singh\anaconda3\lib\site-packages\snowflake\connector\errors.py", line 179, in default_errorhandler raise error_class(

ProgrammingError: 001003 (42000): SQL compilation error: syntax error line 2 at position 65 unexpected 'row'.

Simeon Pilgrim · Accepted Answer · 2021-03-05 07:00:21Z

2

Looking at the Python binding example

and your code it appears, you need

row1 = cs.execute("""CREATE OR REPLACE table util_db.public.HIERARCHY_VIEW_2 AS 
        SELECT * FROM (
            SELECT MSTR.PROD_CODE AS PROD_CODE,DIM.ATTR_NAME AS ATTR_NAME,MSTR.ATTR_VALUE AS ATTR_VALUE 
            FROM TBL_DIM DIM 
            INNER JOIN TBL_MSTR MSTR 
                ON DIM.ATTR_KEY=MSTR.ATTR_KEY 
        ) Q
        PIVOT (MAX (Q.ATTR_VALUE) FOR Q.ATTR_NAME IN (%s))
        AS P
        ORDER BY P.PROD_CODE;""", row)

but *row will pass the many argugments to I have changed to build the string or comman seperated as a single string.

edited Mar 5, 2021 at 7:00

answered Mar 5, 2021 at 6:21

Simeon Pilgrim

26.7k3 gold badges38 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Anand Singh Over a year ago

Thanks, Simeon. I modified the query as per your example but getting another error now. Is it because the row variable returns a tuple data type result?

Anand Singh Over a year ago

Error in question: TypeError: not all arguments converted during string formatting

Simeon Pilgrim Over a year ago

my bad, read the SQL for the first result, which should be a single column of strings which thus should be able you just be used as is. thus ','.join(row) => row

Simeon Pilgrim Over a year ago

@AnandSingh also I used the wrong string formatting token should have been %s, the joys of edit text in the stackoverflow ui

Simeon Pilgrim Over a year ago

@AnandSingh do you need the ','.join(row), if the output of your print(row) shows comma separated values, then it wont be needed. Which is what my reading of the listagg is doing.

Anand Singh · Accepted Answer · 2021-03-05 07:19:19Z

Issue resolved! Thanks a lot, Simeon for your help.

import snowflake.connector as sf
import pandas

ctx = sf.connect (
    user = 'floatinginthecloud89',
    password = 'AzureSn0flake@123',
    account = 'nq13914.southeast-asia.azure',
    warehouse = 'compute_wh',    
    database = 'util_db',
    schema = 'public'
)
print("Got the context object")
 
cs = ctx.cursor()
print("Got the cursor object")

column1 = 'attr_name';


try:
     row = cs.execute("select listagg(('''' || attr_name || ''''), ',') from util_db.public.TBL_DIM;")
     rows = cs.fetchall()
     for row in rows:
          print(row)
          print(rows)
          
     row1 = cs.execute("""CREATE OR REPLACE table util_db.public.HIERARCHY_VIEW_2 AS 
        SELECT * FROM (
            SELECT MSTR.PROD_CODE AS PROD_CODE,DIM.ATTR_NAME AS ATTR_NAME,MSTR.ATTR_VALUE AS ATTR_VALUE 
            FROM TBL_DIM DIM 
            INNER JOIN TBL_MSTR MSTR 
                ON DIM.ATTR_KEY=MSTR.ATTR_KEY 
        ) Q
        PIVOT (MAX (Q.ATTR_VALUE) FOR Q.ATTR_NAME IN (%s))
        AS P
        ORDER BY P.PROD_CODE;""", ','.join(row))
     rows1 = cs.fetchall()
     for row1 in rows1:
          print(row1)

demircioglu · Accepted Answer · 2021-03-05 19:22:15Z

0

More pythonic way to implement this is using f-string

     row1 = cs.execute(f"""CREATE OR REPLACE table util_db.public.HIERARCHY_VIEW_2 AS 
        SELECT * FROM (
            SELECT MSTR.PROD_CODE AS PROD_CODE,DIM.ATTR_NAME AS ATTR_NAME,MSTR.ATTR_VALUE AS ATTR_VALUE 
            FROM TBL_DIM DIM 
            INNER JOIN TBL_MSTR MSTR 
                ON DIM.ATTR_KEY=MSTR.ATTR_KEY 
        ) Q
        PIVOT (MAX (Q.ATTR_VALUE) FOR Q.ATTR_NAME IN ({row}))
        AS P
        ORDER BY P.PROD_CODE;""")

It is also more readable especially if you have multiple parameters in the f-string

answered Mar 5, 2021 at 19:22

demircioglu

3,5151 gold badge21 silver badges30 bronze badges

1 Comment

user2555515 Over a year ago

Maybe pythonic but it creates sql injection vulnerability

Collectives™ on Stack Overflow

Using variable in Snowflake SQL in Python script

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related