Fetch Queries from Snowflake table and execute each query using Python

Question

I have stored queries (like select * from table) in a Snowflake table and want to execute each query row-by-row and generate a CSV file for each query. Below is the python code where I am able to print the queries but don't know how to execute each query and create a CSV file:

I believe I am close to what I want to achieve. I would really appreciate if someone can help over here.

import pyodbc
import pandas as pd
import snowflake.connector
import os

conn = snowflake.connector.connect(
        user = 'User',
        password = 'Pass',
        account = 'Account',
        autocommit = True
        )

try:
    cursor = conn.cursor()
    query=('Select Column from Table;')--This will return two select 
    statements
        output = cursor.execute(query)

    for i in cursor:
        print(i)
    cursor.close()
    del cursor 
    conn.close()
except Exception as e:
    print(e)

David Garrison · Answer 1 · 2021-12-15T17:22:18.397

0

You're pretty close. just need to execute the code instead of printing, and put the data into a file.

I haven't used pandas much myself, but this is the code that Snowflake documentation provides for running a query and putting it into a pandas dataframe.

cursor = conn.cursor()
query=('Select Column, row_number() over(order by Column) as Rownum from Table;')
cursor.execute(query)
resultset = cursor.fetchall()

for result in resultset:
    cursor.execute(result[0])
    df = cursor.fetch_pandas_all()

    df.to_csv(r'C:\Users\...<your filename here>'+ result[1], index = False)

may take some fiddling, but here's a couple links for references:

Update: added an example of a way to create separate files for each record. This just adds a distinct number to each row of your sql output so you can use that number as part of the filename. Ultimately, you need to have some logic in your loop to create a filename, whether that's adding a random number, a timestamp, whatever. That can come from the SQL or from the python, up to you. I'd probably add a filename column to your table, but I don't know if that makes sense for you.

edited Dec 15 '21 at 17:22

answered Dec 14 '21 at 18:32

David Garrison

2,546
15
25

After code changes it gives me an error as "No such file or directory: 'C:/Users/.../file.csv'" I want to create a CSV file on the fly and that too dynamic file creation – user3776623 Dec 14 '21 at 18:47
looks like pandas will create a file, but will not create directories that don't exist. Multiple useful answers here: https://stackoverflow.com/questions/47143836/pandas-dataframe-to-csv-raising-ioerror-no-such-file-or-directory – David Garrison Dec 14 '21 at 18:49
Ok. Now I am able to generate a CSV file. But now want to generate separate CSV file for each query. Like for 2 sql statement there should be 2 CSV files. – user3776623 Dec 14 '21 at 19:01
Any update on how to generate separate CSV file for each query? – user3776623 Dec 14 '21 at 22:13
updated with an example of a way to do that. Really you just need SOMETHING in your loop that is different for each file. even if it's just a timestamp or a random number – David Garrison Dec 15 '21 at 17:17

Fetch Queries from Snowflake table and execute each query using Python

1 Answers1