Compare lists with column in snowflake using python-CodePudding

I have a column in my snowflake table that contain values with space

    select distinct ("power") from "dev"."devtable"."devschema"
    
    ------------------
     demo1 is good
     what are you doing
     thank you stack
     
   ----------------------

but would need to compare with a array of list without space and with quotes and comma :

 ["demo1_is_good",
 "thank_you_stack",
 "what_are_you_doing"]

I want to use python with snowflake and the purpose of this comparison is to check that e.g. 'demo1_is_good' (column values) is the same as 'demo1 is good' (list values in array) by first removing the quote and looping for each list to compare with the record in the column.

If any of the list is not in the column , then script will exit and not continue.

CodePudding user response：

Code to search string in snowflake table. Exits when string not found.

import snowflake.connector
con = snowflake.connector.connect(
    user='username',
    password='password',
    account='account_name.region',
    warehouse='warehouse',
    database='dbname',
    schema='PUBLIC'
)
flag_not_exists = 0
tab_array = []
devarray = ['"demo1_is_good"', '"thank_you_stack"','"what_are_you_doing12"']
try:
    cur = con.cursor()
    cur.execute("select DISTINCT power as col1 from devtable;")
    for col1 in cur:
        tab_array.append(col1[0])
finally:
    con.close()

for col1 in devarray:
    for col2 in tab_array:
        if (col2 == col1.replace('_',' ').replace('"','')):
            flag_not_exists = 0
            break
        else:
            flag_not_exists = 1
            continue
    if (flag_not_exists == 1):
        break
    else:
        print ("Pass")