How to specify a dot separated table as a parameter into the sql query

user_1656488 0
Viewed 78

For purposes of this question, let's say there is a table schema foo.bar.baz

And we have created a cursor object using following boilerplate

import snowflake.connector

ctx = snowflake.connector.connect(...)
cur = ctx.cursor()

With that cursor object, we can put the whole dot deliminated schema into a query like so:

cur.execute('''
select * from foo.bar.baz
'''
)

and have no issues, but we wouldn't be able to do:

cur.execute('''
select * from %(tbl)s
''', {'tbl': 'foo.bar.baz'} 
)

Doing that throws this type of error: ProgrammingError: 001011 (42601): SQL compilation error: invalid URL prefix found in: foo.bar.baz

I'm guessing this is because the dots are sql identifiers and not strings, but I don't see any workaround in the snowflake documentation. Does anyone know how this could be done without having to change the connection object.

snowflake-cloud-data-platform python
1 Answers

Using TABLE:

In a FROM clause, the syntax TABLE( { string_literal | session_variable | bind_variable } ) can be used

select * from TABLE(%(tbl)s)
user_50708790
Related