如何使用Python Snowflake连接器执行文件中的SQL语句并将每个查询结果保存至独立CSV文件

Hey there! Let's walk through this step by step—since you're new to Python and the Snowflake connector, I'll make sure this is straightforward and easy to follow. Here's a complete plan with actionable code examples:

1. First, Set Up Your Dependencies #

You'll need two key packages to pull this off: the Snowflake Python connector to establish a connection with Snowflake, and Pandas to handle query results and save them to files. Install them via pip:

pip install snowflake-connector-python pandas

2. Read SQL Queries from Your Text File #

Assuming your text file (let's call it queries.sql) has queries separated by semicolons (like SELECT * FROM customers; SELECT COUNT(*) FROM orders;), we can split the content into individual queries cleanly. If your queries are separated by blank lines instead, just adjust the splitting logic—I can help with that if needed!

Here's how to read and split the queries:

def read_queries_from_file(file_path):
    with open(file_path, 'r') as f:
        sql_content = f.read()
    # Split by semicolon, strip extra whitespace, and filter out empty entries
    queries = [q.strip() for q in sql_content.split(';') if q.strip()]
    return queries

# Load your queries
queries = read_queries_from_file('queries.sql')

3. Connect to Snowflake & Execute Queries #

Next, we'll connect to Snowflake, loop through each query, execute it, and save the results. Critical note: Never hardcode your credentials! Use environment variables or a secure secrets manager instead—I'll use environment variables here as a best practice.

import os
import snowflake.connector
import pandas as pd

# Load credentials from environment variables (replace these with your own values)
snowflake_account = os.getenv('SNOWFLAKE_ACCOUNT')
snowflake_user = os.getenv('SNOWFLAKE_USER')
snowflake_password = os.getenv('SNOWFLAKE_PASSWORD')
snowflake_warehouse = os.getenv('SNOWFLAKE_WAREHOUSE')
snowflake_database = os.getenv('SNOWFLAKE_DATABASE')
snowflake_schema = os.getenv('SNOWFLAKE_SCHEMA')

# Establish connection to Snowflake
conn = snowflake.connector.connect(
    account=snowflake_account,
    user=snowflake_user,
    password=snowflake_password,
    warehouse=snowflake_warehouse,
    database=snowflake_database,
    schema=snowflake_schema
)

# Loop through queries and save results
for idx, query in enumerate(queries, start=1):
    print(f"Running query {idx}...")
    try:
        # Execute query and load results into a Pandas DataFrame
        df = pd.read_sql(query, conn)
        # Save to CSV (swap to .xlsx if you prefer Excel files)
        output_file = f"query_result_{idx}.csv"
        df.to_csv(output_file, index=False)
        print(f"Results saved to {output_file} successfully!")
    except Exception as e:
        print(f"Failed to run query {idx}: {str(e)}")
        continue

# Close the connection to Snowflake
conn.close()

4. Key Notes & Optional Improvements #

• Handling Complex Queries: If your SQL includes semicolons inside statements (like stored procedures), splitting by semicolon will break them. In that case, mark each query with a unique comment (e.g., -- QUERY START) and split based on that instead.
• Context Managers for Safety: To avoid forgetting to close connections, use with blocks to auto-manage resources:

  with snowflake.connector.connect(...) as conn:
      with conn.cursor() as cur:
          for idx, query in enumerate(queries, 1):
              cur.execute(query)
              # Convert results to DataFrame manually
              df = pd.DataFrame(cur.fetchall(), columns=[desc[0] for desc in cur.description])
              df.to_csv(f"query_result_{idx}.csv", index=False)
  

• Better Error Handling: Add specific Snowflake exceptions (like snowflake.connector.errors.ProgrammingError) to debug syntax issues or permission problems more easily.
• Excel Output: If you want Excel files instead of CSV, use df.to_excel(f"query_result_{idx}.xlsx", index=False)—you'll need to install openpyxl first with pip install openpyxl.

That's all you need to get started! Tweak the code based on your SQL file structure or output preferences, and you'll be running and saving queries in no time.

内容的提问来源于stack exchange，提问作者Kris K