CODEWITHSUNDEEP
pythonsnowflake-cloud-data-platform
Anand Singh
Anand Singh

Reputation: 107

Using variable in Snowflake SQL in Python script

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'.

Upvotes: 3

Views: 4561

Answers (3)

demircioglu
demircioglu

Reputation: 3455

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

Upvotes: 0

Anand Singh
Anand Singh

Reputation: 107

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)

Upvotes: 0

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

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.

Upvotes: 2

Related Questions