Check if the data exists in snowflake table (python)-CodePudding

I am trying to check if the data exists in a snowflake table

'''

conn = sf.connect(user=snow_username, password=snow_password, account=snow_account, 
wharehouse=snow_wharehouse)
cur = conn.cursor()
query = "Use Database testDatabase"
cur.execute(query)
video_id = "5XDBVX9cAAw"
channel_name = "test_channel"
query = (f""" SELECT video_id FROM {channel_name}  WHERE video_id = {video_id};""")
cur.execute(query)
cur.fetchone()

''' but I get the following error, I am not sure what's wrong as I am able insert data into the table.

ProgrammingError: 001003 (42000): SQL compilation error: syntax error line 1 at position 47 unexpected 'XDBVX9cAAw'.

CodePudding user response：

You can check that by using if statement, try this

conn = sf.connect(user=snow_username, password=snow_password, account=snow_account, 
wharehouse=snow_wharehouse)
cur = conn.cursor()
query = "Use Database testDatabase"
cur.execute(query)
video_id = "5XDBVX9cAAw"
channel_name = "test_channel"
query = (f""" SELECT video_id FROM {channel_name}  WHERE video_id = "{video_id}";""")
cur.execute(query)
data = cur.fetchone()


if data != None:
   print(f"Record exists for video_id : {video_id}\n {data}")
else:
   print(f"Records not exist for video_id : {video_id}")

CodePudding user response：

The issue is the query:

query = (f""" SELECT video_id FROM {channel_name}  WHERE video_id = {video_id};""")

Which is using string interpolation(SQL Injection possible), after subsitution the query is resolved as:

SELECT video_id FROM test_channel WHERE video_id = 5XDBVX9cAAw
                                                   -- this is not string literal

-- should rather be
SELECT video_id FROM test_channel WHERE video_id = '5XDBVX9cAAw'

A cleanear approach is to use parameter binding:

query = (f""" SELECT video_id FROM TABLE(?)  WHERE video_id = ?;""")
cur.execute(query, (channel_name, video_id))