Connecting to Snowflake from Python: A Concise Guide in 4 Easy Steps

Python, the top programming language used by data engineers, is proficient in setting up pipelines, maintaining data flows, and transforming data with its simple syntax and proficiency in automation. On the other hand, Snowflake, built completely for and in the cloud, has become an industry leader in cloud data platforms. It offers unmatched versatility by scaling compute and storage independently to meet user needs.

This article explains how to connect to Snowflake from Python, detailing the process and demonstrating how developers can write Python scripts to manage core Snowflake resources, without using SQL. Users will also learn the necessary steps and discover best practices to efficiently integrate Python with Snowflake, enabling them to leverage both tools in their data management tasks.

4 Steps to connect Snowflake to Python

Python can be used to migrate your data from a legacy platform to Snowflake, create or manage data pipelines for Extract, Transform, and Load (ETL) processes, perform data science tasks such as machine learning, or create data analysis visualizations.

You can connect Snowflake to Python in four easy steps:

1. Set up the environment: Ensure you have a Snowflake account, a Snowflake user, and a supported version of Python installed (3.8, 3.9, 3.10, 3.11).
2. Install the CData Snowflake Python Connector: CData provides a specific package to allow easy connection to Python. The CData Snowflake Python Connector is available in Linux, Windows, and macOS.
3. Create the connection: Use the connect() function of the package to connect to Snowflake. This function requires the Snowflake account identifier to be passed along with authentication information based on the type of auth used.
4. Execute queries and manage data: The connector supports data manipulation operations like inserting, updating, and deleting data with appropriate SQL commands.

How to set up the environment to connect Python to Snowflake

Python’s package installer,pip, is a crucial tool for Python developers. It allows you to install and manage additional packages that are not part of the Python standard library. One such package is the CData Snowflake Python Connector, which enables Python to connect with the Snowflake data warehouse.

Here’s how you can set up your environment to connect Python to Snowflake using the CData Snowflake Python Connector:

1. Verify Python installation: Ensure that you have a supported version of Python installed (3.8, 3.9, 3.10, 3.11). You can check this by running the command python --version in your terminal.
2. Check pip functionality: Pip is usually installed with Python. Verify its installation by running pip --versionin your terminal. If pip is not installed, you can download and install it from the official pip website.
3. Download the CData Snowflake Python Connector: Download the CData Snowflake Python Connector from the CData website.

How to install the CData Snowflake Python Connector

The CData Snowflake Python Connector is a powerful tool that allows developers to write Python scripts with connectivity to the Snowflake data warehouse. This connector simplifies the complexity of accessing Snowflake data, wrapping it in an interface commonly used by Python connectors to common database systems.

Installing the CData Snowflake Python connector: Once the contents are extracted after download, use pip to install the .whl (Windows) or .tar.gz (Linux/Mac) file appropriate for your Python distribution.

• Windows installation: The command below installs the appropriate package for a 64-bit Python 3.10 distribution on a Windows environment (where xxxx is the version number):

pip install PATH\\TO\\cdata_snowflake_connector-22.0.xxxx-cp310-cp310-win_amd64.whl

• Linux/Mac installation: The command below installs the appropriate package for a Python 3.10 distribution on a Linux environment (where xxxx is the version number):

pip install PATH/TO/cdata_snowflake_connector-22.0.xxxx-python310.tar.gz

How to establish the connection between Python and Snowflake

The cdata.snowflake.connect() function is used to establish a connection between your Python script and the Snowflake data warehouse, using the CData Snowflake Python Connector. This function requires a string of name-value pairs separated by semi-colons for the required connection properties.

Here are the three types of authentications allowed:

• Default authentication: Default authentication is the most common type of authentication. It requires the following parameters:
• User: Your Snowflake username
• Password: Your Snowflake password
• Single sign-on (SSO) authentication: SSO authentication allows users to use their organization’s SSO provider to authenticate their Snowflake session.
• Key pair authentication: The key pair authentication method uses a public-private key pair. The public key is registered with Snowflake, and the private key is used to sign a JWT token for authentication.

Here is a basic code snippet demonstrating how to establish a connection:

conn = cdata.salesforce.connect("User=myUser; Password=myPassword; Security Token=myToken;")

How to execute queries and manage data in Snowflake from Python

After establishing the connection, use the execute function to create a cursor. A cursor is often denoted as cur, a Python object/control structure used to traverse and fetch the records from the database. It plays an important role in interacting with Snowflake by enabling the execution of SQL queries and retrieval of results.

• Executing queries: Queries are executed in Snowflake using the cur.execute(). This method takes a SQL query string as an argument and executes it against the Snowflake database. Here’s an example:

query = "SELECT * FROM my_table"
cur.execute(query)

• Fetching and handling query results: After executing a query, you can fetch the results using the cur.fetchall() method for all records or cur.fetchone() for a single record. Here’s how you can do it:

results = cur.fetchall()
for row in results:
    print(row)

• Error handling best practices: While interacting with Snowflake, it’s important to implement error handling to catch and handle exceptions that may occur. This can be done using Python’s try-except For instance:

try:
    cur.execute(query)
except Exception as e:
    print(f"An error occurred: {e}")

• Data manipulation capabilities: The CData Snowflake Python Connector supports various data manipulation operations such as inserting, updating, and deleting data. These operations can be performed by executing the appropriate SQL commands:

# Inserting data
insert_query = "INSERT INTO my_table (column1, column2) VALUES (value1, value2)"
cur.execute(insert_query)

# Updating data
update_query = "UPDATE my_table SET column1 = new_value WHERE condition"
cur.execute(update_query)

# Deleting data
delete_query = "DELETE FROM my_table WHERE condition"
cur.execute(delete_query)

Remember to always commit your changes using conn.commit() after performing data manipulation operations to ensure the changes are saved in the database. Also, please note that the above examples are simplified, and actual usage would require proper connection setup and teardown, as well as more robust error handling.

To get started with CData Python Connectors, follow our guide.

The CData difference

CData Python Connectors offer a straightforward way to connect with data using Python scripts, irrespective of the data source. CData Snowflake Python Connector allows smooth interaction with Snowflake using Python. It enables easy reading, writing, and updating of Snowflake data, thereby bridging the gap between Snowflake and Python-based applications.

Whether it’s data access, visualization, Object-Relational Mapping (ORM), Extract, Transform, Load (ETL) processes, AI/ML applications, or custom apps, CData ensures smooth and efficient connectivity with Snowflake.

As always, our support team is ready to answer any questions. Have you joined the CData Community? Ask questions, get answers, and share your knowledge in CData connectivity tools. Join us!