Passing list of tuples as values inside a cte in Snowflake

Question

I am trying to pass a query to Snowflake which has the following format:

query = f"""
insert into target
with incoming (id,name,age) as (
select * from
values ()
), another_cte (
....
)
select * from another cte
"""

For the values, I want to pass a list of tuples. For example: incoming_values = [(1,'john',20),(2,'jane',22)].

I am calling the execute function as:

execute(query, incoming_values)

However, I am running into error: AttributeError: 'tuple' object has no attribute 'replace'\n"

Answer 1

Rather than trying to select from the values as a literal row, insert them into a temporary table in the usual way and use that temporary table in your query. It will make for much cleaner code, here is a full example:

import snowflake.connector
import os

snowflake_username = os.environ['SNOWFLAKE_USERNAME']
snowflake_password = os.environ['SNOWFLAKE_PASSWORD']
snowflake_account = os.environ['SNOWFLAKE_ACCOUNT']
snowflake_warehouse = os.environ['SNOWFLAKE_WAREHOUSE']
snowflake_database = 'test_db'
snowflake_schema = 'public'


if __name__ == '__main__':
    with snowflake.connector.connect(
        user=snowflake_username,
        password=snowflake_password,
        account=snowflake_account,
        warehouse=snowflake_warehouse,
        database=snowflake_database,
        schema=snowflake_schema,
        autocommit=False
    ) as con:

        # Sample data to load into table
        tuples = [(1, 'john', 20), (2, 'jane', 22), (3, 'simon', 23)]

        # Create temporary table and insert list of tuples into it
        con.cursor().execute("create temporary table incoming (col1 number, col2 varchar, col3 number)")
        con.cursor().executemany("insert into incoming (col1, col2, col3) values (%s, %s, %s)", tuples)

        # This query uses the temporary table within the another_cte cte
        query = """
        with another_cte as (
            select * from incoming
        )
        select * from another_cte
        """

        # Run the query ane fetch the results to prove the temporary table contains the values
        results = con.cursor().execute(query).fetchall()
        print(results)

This script prints the following:

[(1, 'john', 20), (2, 'jane', 22), (3, 'simon', 23)]

Don't worry about the creation of a temporary table, once the Snowflake session ends (when the program is out of the context manager) the table is cleaned up and gone, it also doesn't use any unnecessary storage costs after it is cleaned up.