Snowflake Python Connector

Python powers automation, data science, and countless business scripts. The Snowflake Connector for Python lets a Python program open a session, run SQL, and read results as plain lists or pandas DataFrames. A few lines of code replace manual clicking in a worksheet.

How the Connector Works

The connector acts like a phone line between a Python script and Snowflake. The script dials in with credentials, speaks SQL, and listens for the answer. When the conversation ends, the script hangs up and releases the line.

PYTHON TO SNOWFLAKE FLOW
========================

 +---------------+   connect()    +----------------+
 | Python script |--------------->|   SNOWFLAKE    |
 |               |   execute(sql) |   warehouse    |
 |               |--------------->|   runs query   |
 |               |<---------------|                |
 |  fetchall()   |   result rows  +----------------+
 +---------------+
        |
        v
 list, tuple, or pandas DataFrame

Installation

pip install snowflake-connector-python

# Add pandas support
pip install "snowflake-connector-python[pandas]"

Opening a Connection

import os
import snowflake.connector

conn = snowflake.connector.connect(
    account   = "myorg-myaccount",
    user      = "analyst_user",
    password  = os.environ["SNOWFLAKE_PASSWORD"],
    role      = "ANALYST_ROLE",
    warehouse = "ANALYST_WH",
    database  = "SALES_DB",
    schema    = "PUBLIC"
)

The password comes from an environment variable, which keeps secrets out of source code. Production jobs prefer key pair authentication through the private_key parameter, or single sign-on through authenticator = "externalbrowser" for interactive scripts.

Running a Query

with conn.cursor() as cur:
    cur.execute("SELECT region, SUM(amount) FROM orders GROUP BY region")
    for region, total in cur.fetchall():
        print(region, total)

conn.close()

A cursor sends statements and holds results. The with block closes the cursor automatically.

Fetch Methods

METHOD              RETURNS
------------------  --------------------------------------
fetchone()          The next single row
fetchmany(100)      Up to 100 rows
fetchall()          Every remaining row
fetch_pandas_all()  Everything as one pandas DataFrame

Safe Parameters

Building SQL by joining text invites SQL injection, a common attack where crafted input changes the query. Parameter binding passes values separately from the SQL text.

region = "West"
min_amount = 100

cur.execute(
    "SELECT order_id, amount FROM orders WHERE region = %s AND amount >= %s",
    (region, min_amount)
)

Working With pandas

import pandas as pd
from snowflake.connector.pandas_tools import write_pandas

# Read a query into a DataFrame
cur = conn.cursor()
cur.execute("SELECT * FROM orders WHERE order_date >= '2026-01-01'")
df = cur.fetch_pandas_all()
print(df.head())

# Write a DataFrame to a table
new_data = pd.DataFrame({"ORDER_ID": [1, 2], "AMOUNT": [250.0, 100.0]})
success, chunks, rows, _ = write_pandas(conn, new_data, "ORDERS_NEW",
                                        auto_create_table=True)
print(success, rows)

Column names in the DataFrame should use uppercase letters to match Snowflake's default identifier handling. The write_pandas function stages the data and runs COPY INTO behind the scenes, which beats inserting row by row.

Handling Errors

from snowflake.connector.errors import ProgrammingError

try:
    cur.execute("SELECT * FROM missing_table")
except ProgrammingError as err:
    print("Query failed:", err.msg)
finally:
    cur.close()
    conn.close()

Good Habits

• Close cursors and connections when work ends.
• Pick a small warehouse with auto-suspend for scripts.
• Bind parameters in place of building SQL text.
• Pull only needed columns and rows into memory.
• Add the query tag session parameter to mark script activity in query history.

cur.execute("ALTER SESSION SET QUERY_TAG = 'daily_sales_report'")

A Practical Scenario

A data analyst builds a weekly report that combines Snowflake sales data with a spreadsheet of targets. A Python script connects to Snowflake, pulls the sales figures into a DataFrame, merges them with the targets, and writes the final report to a shared folder. A scheduler runs the script every Monday at seven in the morning. The analyst reviews results over coffee rather than assembling them by hand.

Frequently Asked Questions

How do I avoid storing passwords in code?

Read credentials from environment variables or a secrets manager. Production scripts use key pair authentication for service users.

Why does a large result take long to download?

Pulling millions of rows into memory strains the network and the computer. Filter and aggregate in SQL, and fetch only the final numbers.

Which Python versions work?

Check the connector documentation for the supported Python versions before installing in a new environment.

Connection Lifetime

Opening a connection takes a moment, so long-running programs reuse one connection for many queries. Short scripts open a connection, finish their work, and close it. Programs that run for hours should handle expired sessions by reconnecting and retrying the query once.

Running Several Queries Together

Some jobs need many statements. Group related statements in one script run, and use transactions when changes must succeed or fail as a unit. The connector supports BEGIN, COMMIT, and ROLLBACK like any SQL client, so Python code can protect important updates with the same safety tools.

Key Points

• The connector lets Python open sessions, run SQL, and read results.
• connect() takes account, user, credentials, role, warehouse, database, and schema.
• Parameter binding blocks SQL injection and handles quotes correctly.
• fetch_pandas_all and write_pandas bridge Snowflake and pandas.
• Environment variables or key pairs keep credentials out of code.
• Query tags make script activity easy to trace.