Skip to content
Advertisement

How to execute sql query with python variables null or empty

Hi I’m using python 3 and snowflake. Example:

MY_SCHEMA = 'TEST'
MY_TABLE = Empty

sql = "SELECT SCHEMA, TABLE FROM TABLE_LOG WHERE SCHEMA = %s AND TABLE = %s"

tuple1 = (MY_SCHEMA, MY_TABLE)
cur.execute(sql,tuples1)

I tried with this:

 WHERE SCHEMA = %s OR (SCHEMA IS NULL AND %s IS NULL) also WHERE SCHEMA = IIF(%S IS NULL, SCHEMA, %s) 

I’m getting TypeError: not enough arguments for format string. I would like to run this query for all schemas if I dont have defined MY_SCHEMA variable and also for all tables if MY_TABLE variable is null or empty. I would be grateful for any advice.

Advertisement

Answer

I found a solution this issue:

MY_SCHEMA = 'TEST'
MY_TABLE = None

cur.execute("SELECT SCHEMA,TABLE FROM TABLE_LOG WHERE SCHEMA = COALESCE(%(schema_variable)s,SCHEMA) AND TABLE = COALESCE(%(table_variable)s,TABLE )",{"schema_variable": MY_SCHEMA, "table_variable": MY_TABLE})

7 People found this is helpful
Advertisement