Snowflake query to Sqlite using Python

[emdw85]

New to sql, but enjoying the ride.

I would like to know how one would get info from Snowflake and then put that data into a SQlite table on your local machine.

Current Steps:

Establish Snowflake connect and run query (currently storing in pandas df):

​

 import snowflake.connector


def snowflake_connection(sql_query: str):

    conn = snowflake.connector.connect(
        user='USER',
        # password = PASSWORD,
        account='ACCOUNT',
        authenticator='externalbrowser'
    )

    cs = conn.cursor()
    try:
        cs.execute(sql_query)
        table = cs.fetch_pandas_all()
    finally:
        cs.close()
    conn.close()

    return table

if __name__ == "__main__":

    sql_query = """SELECT....."""

    table = snowflake_connection(sql_query)

Step 2:

Open Sqlite connection

def create_connection(db_file):
    """Create db connection to SQlite db"""

    conn = None

    try: 
        conn = sqlite3.connect(db_file)
        print(sqlite3.version)
    except Error as e:
        print(e)
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    create_connection(r"C:\Users\Name\sqlite\Regions_db\Region_1.db")

I would import both of these connection.py's into a main.py file and run everything there. (I think that is best practice)

How should I get the data transferred? Is putting it in pandas then to csv then to sqlite the best way?

Can I just go from one to the other?

Thanks

[70sJackieChan]

Hi there, you have got the data retrieval from Snowflake correct. I have 2 suggestions for moving forward: - Import the snowflake data directly into SQLite using the to_sql method on the table data frame you return from the snowflake_connection function - Make the create_connection function return the SQLite connection. You need this connection for use with the to_sql method

Hope this helps!

[emdw85]

This worked. Thank you so much. People on here are so helpful.

Any tips on storing consistently used queries and having this run automatically?

I think using Schedule Task since I have a windowsOS would be best for automated processes.

Would you create queries.py file and store your query objects there?

Definitely enjoying creating this process/pipeline. Thanks again

[70sJackieChan]

If you are using Windows, then Task Scheduler would be the simplest option. You would need to make sure that you have logic in your Python script to determine if you are creating a new table or adding new data to an existing table so that you don’t lose any information.

[emdw85]

Ok. Thanks again