Any way to duplicate this SQL query behavior using Python?

[info-rchitect]

Hi,

We need this behavior to work when executing SQL from Python.

show grants on database MYDB;
-- DKU_END_STATEMENT
create or replace temporary table db_check as
    select EXISTS(
        select * from table(result_scan(-1)) where "privilege" = 'USAGE' and "granted_on" = 'DATABASE' and "grantee_name" = 'MYROLE'
    ) as db_ok
;
-- DKU_END_STATEMENT
show grants on schema MYDB.MYSCHEMA;
-- DKU_END_STATEMENT
create or replace temporary table schema_check as
    select EXISTS(
        select * from table(result_scan(-1)) where "privilege" = 'USAGE' and "granted_on" = 'SCHEMA' and "grantee_name" = 'MYROLE'
    ) as schema_ok
;
-- DKU_END_STATEMENT
show grants on table MYDB.MYSCHEMA.MYTABLE;
-- DKU_END_STATEMENT
create or replace temporary table table_check as
    select EXISTS(
        select * from table(result_scan(-1)) where "privilege" = 'SELECT' and "granted_on" = 'TABLE' and "grantee_name" = 'MYROLE'
    ) as table_ok
;
-- DKU_END_STATEMENT
with combined as (
    select distinct db_ok, schema_ok, table_ok, (db_ok and schema_ok and table_ok) as rest_api_ok
    from db_check, schema_check, table_check
)
select * from combined;

When I try either of the following, I get the same error:

ERROR: Job failed: Error in Python process: At line 44: <class 'Exception'>: Query failed: b'Actual statement count 7 did not match the desired statement count 1.'

dku_connector = SQLExecutor2(connection='MYCONNECTION')

# ATTEMPT #1:
df = dku_connector.query_to_df(sql)

# ATTEMPT #2

mydataset = dataiku.Dataset('mydataset')
dku_connector.exec_recipe_fragment(output_dataset=mydataset, query=sql, overwrite_output_schema=True)

Just to be clear, the behavior required is the retention of state, such that temporary tables do not disappear so the final select statement can access those temporary tables. 

thx much

---

Operating system used: Windows 10

[ZachM]

Hi @info-rchitect,

You can accomplish this by using pre_queries to create the temporary tables.

For example, the following script will create a temporary table based on the existing table "other_table", then access the temporary table in the main query:

from dataiku import SQLExecutor2

executor = SQLExecutor2(connection="MY_CONNECTION")

pre_queries = [
    "CREATE TEMPORARY TABLE temp_table AS SELECT * FROM other_table",
]
query = "SELECT * from temp_table"

df = executor.query_to_df(query, pre_queries=pre_queries)

Reference documentation: https://developer.dataiku.com/12/api-reference/python/sql.html#dataiku.SQLExecutor2.query_to_df