Appending tuples returned by for loop

Question

I am running some SQL queries using sqlalchemy. I have a for loop that runs queries against Snowflake tables.

Current Code:

for x in list:
    results = cursor.execute(f"SELECT TABLE_NAME as TABLE_NAME, 'TABLE_SCHEMA as TABLE_SCHEMA, MAX(DATE) as DATE  FROM {database}.{schema}.{table}")
    for result in results:
        print(result)

This for loop returns a tuple with each iteration, like below:

('SCHEMA_1','TABLE_1','DATE_1')
('SCHEMA_2','TABLE_2','DATE_2')
('SCHEMA_3','TABLE_3','DATE_3')

etc.

How can I create a tuple of tuples from this from loop? So I can then later pass it into a DataFrame.

All I want to do is create a dataframe from the loop that will append the results each time, so I can then write the complete df back to Snowflake.

I cannot use the snowflake-connector-python[pandas] module and need to do it with sqlalchemy + pandas.

Answer 1

I don't understand what the list iteration is here since you don't seem to use x anywhere, so have omitted that.

Just append the tuples into a list:

data = []

results = cursor.execute(f"SELECT TABLE_NAME as TABLE_NAME, 'TABLE_SCHEMA as TABLE_SCHEMA, MAX(DATE) as DATE  FROM {database}.{schema}.{table}")
for result in results:
    data.append(result)

df = pd.DataFrame(data, columns=["schema_name", "table_name", "max_date"])