
Connect to Snowflake using Snowflake Connector for Python
Connecting to Snowflake in Python by using the Snowflake Connector allows you to write data to and from tables and execute SQL commands as if you were working within the UI.
Connecting to Snowflake in Python by using the Snowflake Connector allows you to write data to and from tables and execute SQL commands as if you were working within the UI.
To connect to Snowflake using the Snowflake Connector for Python, the process is as follows:
- Pip install the
snowflake-connector-python
Python package - Use the function
connect
to create a connection using your user credentials - Create a cursor object using the
cursor
method - Execute SQL queries using the
execute
method
Connecting to Snowflake using Snowflake Connector for Python, what can you do?
The benefit of being able to connect through the Snowflake Connector is that you can do the following from within your scripts:
- Write data to existing tables
- Read data from views or tables
- Read data from custom SQL queries
- Run SQL queries to edit tables and views
This functionality is especially helpful for those working with machine learning applications, who will benefit from:
- Writing model training statistics to a table to track over time
- Using SQL to prepare data before reading into Python
- Storing datasets at various stages of preparation in tables
Pip install snowflake-connector-python to your environment
In order to replicate the code in these examples locally on your machine you will need to have the snowflake-connector-python
package installed.
It is always recommended to create an isolated Python environment for your projects, so we will do this by creating a Conda environment:
conda create -n env_name python=3.9
conda activate env_name
pip install snowflake-connector-python
Connect using Snowflake Connector for Python
Making the connection a fairly simple process, but still requires us to enter in some information. The fields we need to enter are:
account_indentifier
: The identifier for your instance, which is normally found in the URL use when loading up the Snowflake UI:โaccount_identifier".snowflakecomputing.com
user
: Your usernamepassword
: Your password
from snowflake.connector import connect
#Use your Snowfake user credentials to connect
conn = connect(
user='<user_login_name>',
password='<password>',
account='<account_identifier>'
)
Additional parameters for Snowflake Connector for Python
There are also a number of optional fields that we could provide in this connection function, some of the most useful are:
database
: The initial database for the sessionschema
: The initial schema for the sessionwarehouse
: The warehouse for the sessionrole
: The user role for the session
These additional parameters would be added to the same connection function:
from snowflake.connector import connect
#Use your Snowfake user credentials to connect
conn = connect(
user='<user_login_name>',
password='<password>',
account='<account_identifier>',
database='<database>'
schema='<schema>'
warehouse='<warehouse>'
role='<role>'
)
Executing SQL commands using Snowflake Connector for Python connection
Now that we have our connection open with Snowflake using their connector, we can start to undertake some actions. In this example we will execute a simple SQL query to create a view in our database:
from snowflake.connector import connect
#Use your Snowfake user credentials to connect
conn = connect(
user='<user_login_name>',
password='<password>',
account='<account_identifier>'
)
sql = 'create view my-view as (select * from my-table)'
cursor = conn.cursor()
cursor.execute(sql)
cursor.close()
Snowflake Connector for Python examples
For further examples of how to use Snowflake Connector, check out the other posts that I have written on the topic:
Create table from Pandas using Snowflake Connector for Python
Credentials when using Snowflake Connector
When using the Snowflake Connector, you are required to enter your credentials to create the connection. You can of course decide what the best insertion method is for your project, whether that be environment variables or something else, but given that this is sensitive information you should remember the golden rule:
Do not enter your credentials in plain text, especially when working within a Git repository
Related articles
Write Pandas data to Snowflake table
Create Snowflake table from Pandas