Connecting to Snowflake Using Python A Comprehensive Beginner’s Tutorial

Connecting to Snowflake Using Python: A Comprehensive Beginner’s Tutorial

by

Snowflake, a cloud-based data warehousing platform, has revolutionized the way businesses store and access their data. For beginners in data science or software engineering, learning to connect to Snowflake using Python can open doors to efficient data handling and analysis. This guide provides a comprehensive overview of how to connect to Snowflake using two different methods in Python, catering to both beginners and those looking to expand their skill set.

Why Choose Python for Snowflake Connectivity?

Python stands out for its simplicity and readability, making it a popular choice among beginners and professionals alike. Its extensive libraries and community support make Python an ideal language for data-related tasks. When it comes to Snowflake, Python’s connector allows for easy integration, enabling users to perform various data operations seamlessly.

Preparing Your Environment

Before diving into the connectivity methods, ensure you have the following prerequisites in place:

  • Python Installation: Make sure Python is installed on your system. You can download it from the official Python website.
  • Snowflake Account: You need access to a Snowflake account. If you don’t have one, you can sign up for a free trial on the Snowflake website.
  • Required Libraries: Install the Snowflake connector for Python using pip: pip install snowflake-connector-python.

Method 1: Username and Password Authentication

The first method to connect to Snowflake involves using your username and password. It’s a straightforward approach suitable for beginners.

Step 1: Import Necessary Libraries

import snowflake.connector

You need the snowflake.connector library to establish a connection.

Step 2: Establishing a Connection

Use the following function to connect using your Snowflake credentials:

def connect_snowflake_username_pwd(username, password, account, warehouse, database, schema):
    ctx = snowflake.connector.connect(
        user=username,
        password=password,
        account=account,
        warehouse=warehouse,
        database=database,
        schema=schema
    )
    return ctx

Replace username, password, account, warehouse, database, and schema with your Snowflake account details.

Method 2: Keypair Authentication

For enhanced security, Snowflake supports keypair authentication. This method uses an RSA private key for authentication instead of a password. To know more about how to generate key pair auth you can read this blog.

Step 1: Generate an RSA Key Pair

You can generate an RSA key pair using tools like OpenSSL. Store the private key securely and register the public key with your Snowflake account.

Step 2: Modify the Connection Function

Use the following function to connect using the RSA key:

import os
from cryptography.hazmat.backends import default_backend
from cryptography.hazmat.primitives import serialization

def connect_snowflake_keypair(user, account, private_key_path, warehouse, database, schema):
    with open(private_key_path, 'rb') as key_file:
        p_key = serialization.load_pem_private_key(
            key_file.read(),
            password=None,
            backend=default_backend()
        )

    pkb = p_key.private_bytes(
        encoding=serialization.Encoding.DER,
        format=serialization.PrivateFormat.PKCS8,
        encryption_algorithm=serialization.NoEncryption()
    )

    ctx = snowflake.connector.connect(
        user=user,
        account=account,
        private_key=pkb,
        warehouse=warehouse,
        database=database,
        schema=schema
    )
    return ctx

Step 3: Running Your Python Script

Once you have set up the connection function, you can execute SQL queries to interact with your Snowflake data. Remember to close the connection after completing your tasks to maintain security and resource efficiency.

Full code guide on how to connect to snowflake using python

Below is the config.ini file

[Snowflake]
SnowflakeAccount = accountName.region
SnowflakeUserWithRSAKey = USER
SnowflakeUserName = USER
SnowflakePassword = PWD
SnowflakePrivateKeyPath = path/to/rsa_key.p8
SnowflakePrivateKeyPassword = password
SnowflakeDatabase = DEMODB
SnowflakeSchema = DEMOSCHEMA
SnowflakeWarehouse = COMPUTE_WH
SnowflakeRole = ACCOUNTADMIN
SnowflakeTable = TABLE NAME

below is the main.py file

from configparser import ConfigParser
import snowflake.connector
import os
from cryptography.hazmat.backends import default_backend
from cryptography.hazmat.primitives.asymmetric import rsa
from cryptography.hazmat.primitives.asymmetric import dsa
from cryptography.hazmat.primitives import serialization

def read_config(config_file='config.ini'):
    config = ConfigParser()
    config.read(config_file)
    return config['Snowflake']

def connect_snowflake_username_pwd(config):
    ctx = snowflake.connector.connect(
        user=config.get('SnowflakeUserName'),
        password=config.get('SnowflakePassword'),
        account=config.get('SnowflakeAccount'),
        warehouse=config.get('SnowflakeWarehouse'),
        database=config.get('SnowflakeDatabase'),
        schema=config.get('SnowflakeSchema')
    )
    return ctx

def connect_snowflake_keypair(config):
    private_key_path = config.get('SnowflakePrivateKeyPath')
    private_key_passphrase = config.get('SnowflakePrivateKeyPassword')
    print(private_key_passphrase)

    with open(private_key_path, 'rb') as key_file:
        if private_key_passphrase:
            private_key_passphrase = private_key_passphrase.encode()
        else:
            private_key_passphrase = None

        p_key = serialization.load_pem_private_key(
            key_file.read(),
            password=private_key_passphrase,
            backend=default_backend()
        )

    pkb = p_key.private_bytes(
        encoding=serialization.Encoding.DER,
        format=serialization.PrivateFormat.PKCS8,
        encryption_algorithm=serialization.NoEncryption()
    ) 

    ctx = snowflake.connector.connect(
        user=config.get('SnowflakeUserWithRSAKey'),
        account=config.get('SnowflakeAccount'),
        private_key=pkb,
        warehouse=config.get('SnowflakeWarehouse'),
        database=config.get('SnowflakeDatabase'),
        schema=config.get('SnowflakeSchema')  ,
        role='ACCOUNTADMIN'        
    )
    return ctx

def fetch_data(conn, table):
    cursor = conn.cursor()
    cursor.execute(f'SELECT * FROM {table}')
    results = cursor.fetchall()
    cursor.close()
    return results

def main():
    snowflake_config = read_config()

    # Choose the connection method based on your requirement
    # For username & password authentication
    # conn = connect_snowflake_username_pwd(snowflake_config)

    # For keypair authentication
    conn = connect_snowflake_keypair(snowflake_config)

    try:
        results = fetch_data(conn, snowflake_config.get('SnowflakeTable'))
        print(results[0])
    finally:
        conn.close()

if __name__ == "__main__":
    main()

Conclusion

Connecting to Snowflake using Python is a valuable skill in today’s data-driven world. Whether you’re a beginner or an experienced developer, understanding these two methods of connectivity opens up a realm of possibilities in data analysis and management. With this guide, you can confidently connect to Snowflake and leverage the power of Python for your data needs. Remember to always handle your credentials securely and explore which method of connection suits your project requirements best.

Leave a Comment