Snowflake Connector for Python API

The Snowflake Connector for Python implements the Python Database API v2.0 specification (PEP-249). This topic covers the standard API and the Snowflake-specific extensions.

In this Topic:

Module: snowflake.connector

The main module is snowflake.connector, which creates a Connection object and provides Error classes.

Constants

apilevel

String constant stating the supported API level. The connector supports API "2.0".

threadsafety

Integer constant stating the level of thread safety the interface supports. The Snowflake Connector for Python supports level 2, which states that threads may share the module and connections.

paramstyle

String constant stating the type of parameter marker formatting expected by the interface. The connector supports the "pyformat" type, which applies to Python extended format codes, e.g. ...WHERE name=%(name)s.

Methods

connect(parameters...)

Constructor for creating a connection to the database. Returns a Connection object. By default, autocommit mode is enabled (i.e. if the connection is closed, all changes are committed). If you need a transaction, use the BEGIN command to start the trasaction, and COMMIT or ROLLBACK to commit or roll back any changes.

The valid parameters are:

Parameter Required Description
account Yes Name assigned to your account by Snowflake. In the URL you received from Snowflake, your account name is the first segment in the domain (e.g. abc123 in https://abc123.snowflakecomputing.com or https://abc123.eu-central-1.snowflakecomputing.com).
user Yes Login name for the user.
password Yes Password for the user.
region   Region ID of the Snowflake Region where your account is located. By default, the region is US West. If your account is located in a region other than US West, the region ID must be explicitly specified.
host   Host name. Only used internally. Does not need to be set.
port   Port number. By default, 443. Only used internally. Does not need to be set.
database   Name of the default database to use. After login, you can use USE DATABASE to change the database.
schema   Name of the default schema to use for the database. After login, you can use USE SCHEMA to change the schema.
role   Name of the default role to use. After login, you can use USE ROLE to change the role.
warehouse   Name of the default warehouse to use. After login, you can use USE WAREHOUSE to change the warehouse.
passcode_in_password   False by default. Set to True if the MFA passcode is embedded in the login password.
passcode   The passcode provided by Duo when using MFA for login.
proxy_host   Proxy host name.
proxy_port   Proxy port number.
proxy_user   Proxy port user.
proxy_password   Proxy port password.
autocommit   None by default, which honors the Snowflake parameter AUTOCOMMIT. Set to True or False to enable or disable autocommit mode in the session, respectively.
login_timeout   Timeout in seconds for login. By default, 60 seconds. The login request gives up after the timeout length if the HTTP response is “success”.
network_timeout   Timeout in seconds for all other operations. By default, none/infinite. A general request gives up after the timeout length if the HTTP response is not “success”.
ocsp_response_cache_filename   URI for the OCSP response cache file. By default, the OCSP response cache file is created in the cache directory, i.e. ~/.cache/snowflake/ocsp_response_cache for Linux, ~/Library/Caches/Snowflake/ocsp_response_cache for Mac OS, and %USERPROFILE%\AppData\Local\Snowflake\Caches\ocsp_response_cache for Windows. To locate the file in a different directory, specify the path and file name in the URI, e.g. file:///tmp/my_ocsp_response_cache.txt.
authenticator   Authenticator for Snowflake: snowflake (default) if Snowflake authenticates the username and password parameters; externalbrowser if a third-party identify provider (Idp) is called via a web browser; or the URL for your OKTA endpoint, e.g., https://<okta_account_name>.okta.com. If the value is not snowflake, the user and password parameters must be your login credentials for the IdP.
validate_default_parameters   False by default. Raise an exception if either one of specified database, schema or warehouse doesn’t exists if True.

Attributes

Error, Warning, ...

All exception classes defined by the Python database API standard. The Snowflake Connector for Python provides the attributes msg, errno, sqlstate, sfqid and raw_msg.

Object: Connection

A Connection object holds the connection and session information to keep the database connection active. If it is closed or the session expires, any subsequent operations will fail.

Methods

autocommit(True|False)

Enables or disables autocommit mode. By default, autocommit is enabled (True).

close()

Closes the connection. If a transaction is still open when the connection is closed, the changes are rolled back.

commit()

If autocommit is disabled, commits the current transaction. If autocommit is enabled, this method is ignored.

rollback()

If autocommit is disabled, rolls back the current transaction. If autocommit is enabled, this method is ignored.

cursor()

Constructor for creating a Cursor object. The return values from fetch*() calls will be a single sequence or list of sequences.

cursor(snowflake.connector.DictCursor)

Constructor for creating a DictCursor object. The return values from fetch*() calls will be a single dict or list of dict objects. This is useful for fetching values by column name from the results.

execute_string(sql_text, remove_comments=False, return_cursors=True)

Execute one or more SQL statements passed as strings. If remove_comments is set to True, comments are removed from the query. If return_cursors is set to True, this method returns a sequence of Cursor objects in the order of execution.

Don’t bind data using Python’s formatting function because you risk SQL injection. As this method does’t take binding parameters, use Cursor.execute() or Cursor.executemany() to bind parameters.

# Binding data (UNSAFE EXAMPLE)
cnx.execute_string(
    "INSERT INTO testtable(col1, col2) "
    "VALUES({col1}, {col2});"
    "INSERT INTO testtable(col1, col2) "
    "VALUES({col1}, {col2});".format(
        col1=1, col2=2))
execute_stream(sql_stream, remove_comments=False)

Execute one or more SQL statements passed as a stream object. If remove_comments is set to True, comments are removed from the query. This generator yields each Cursor object as SQL statements run.

Attributes

messages

The list object including sequences (exception class, exception value) for all messages received from the underlying database for this connection.

The list is cleared automatically by any method call.

errorhandler

Read/Write attribute that references an error handler to call in case an error condition is met.

The handler must be a Python callable that accepts the following arguments:

errorhandler(connection, cursor, errorclass, errorvalue)
Error, Warning, ...

All exception classes defined by the Python database API standard.

Object: Cursor

A Cursor object represents a database cursor for execute and fetch operations. Each cursor has its own attributes, description and rowcount, such that cursors are isolated.

Methods

close()

Closes the cursor object.

execute(operation[, parameters])

Prepares and executes a database operation (i.e. query or command).

Parameters may be provided as a sequence or mapping and will be bound to variables in the operation. Variables are specified in pyformat parameter style.

Returns the reference of a Cursor object.

Note

If the result set is large enough, the Snowflake Connector for Python may consume a significant amount of memory and generate an “out of memory” error. If you encounter this issue, try setting the _use_ijson=True option in the Cursor.execute() method to reduce the memory footprint. There is a speed trade off with this option.

executemany(operation, seq_of_parameters)

Prepares a database operation and executes it against all parameter sequences or mappings found in seq_of_parameters.

Internally, multiple execute methods are called and the result set from the last execute call will remain.

Returns the reference of a Cursor object.

Note

The executemany method can only be used to execute a single parameterized SQL statement and pass multiple bind values to it.

Executing multiple SQL statements separated by a semicolon in one execute call is not supported. Instead, issue a separate execute call for each statement.

fetchone()

Fetches the next row of a query result set and returns a single sequence/dict or None when no more data is available.

fetchmany([size=cursor.arraysize])

Fetches the next rows of a query result set and returns a list of sequences/dict. An empty sequence is returned when no more rows are available.

fetchall()

Fetches all or remaining rows of a query result set and returns a list of sequences/dict.

__iter__()

Returns self to make cursors compatible with the iteration protocol.

Attributes

description

Read-only attribute that returns a sequence of 7 values:

Value Description
name Column name.
type_code Internal type code.
display_size (Not used. Same as internal_size.)
internal_size Internal data size.
precision Precision of numeric data.
scale Scale for numeric data.
null_ok True if NULL values allowed for the column or False.
rowcount

Read-only attribute that returns the number of rows in the last execute produced. The value is -1 or None if no execute is executed.

sfqid

Read-only attribute that returns the Snowflake query ID in the last execute executed.

arraysize

Read/write attribute that specifies the number of rows to fetch at a time with fetchmany(). It defaults to 1 meaning to fetch a single row at a time.

connection

Read-only attribute that returns a reference to the Connection object on which the cursor was created.

messages

List object that includes the sequences (exception class, exception value) for all messages which it received from the underlying database for the cursor.

The list is cleared automatically by any method call except for fetch*() calls.

errorhandler

Read/write attribute that references an error handler to call in case an error condition is met.

The handler must be a Python callable that accepts the following arguments:

errorhandler(connection, cursor, errorclass, errorvalue)

Type Codes

Cursor.description attribute returns the column metadata. type_code represents the column data type, and use the following map to get the string representation:

type_code String Representation Data Type
0 FIXED NUMBER/INT
1 REAL REAL
2 TEXT VARCHAR/STRING
3 DATE DATE
4 TIMESTAMP TIMESTAMP
5 VARIANT VARIANT
6 TIMESTAMP_LTZ TIMESTAMP_LTZ
7 TIMESTAMP_TZ TIMESTAMP_TZ
8 TIMESTAMP_NTZ TIMESTAMP_TZ
9 OBJECT OBJECT
10 ARRAY ARRAY
11 BINARY BINARY
12 TIME TIME
13 BOOLEAN BOOLEAN

Object: Exception

PEP-249 defines the exceptions that the Snowflake Python for Connector may raise in case of errors or warnings. The application must handle them properly and decide to continue or stop running the code.

Methods

No methods are available for Exception objects.

Attributes

errno

Snowflake DB error code.

msg

Error message including error code, SQL State code and query ID.

raw_msg

Error message. No error code, SQL State code or query ID is included.

sqlstate

ANSI-compliant SQL State code

sfqid

Snowflake query ID.

Date and Timestamp Support

Snowflake supports multiple DATE and TIMESTAMP data types, and the Snowflake Connector allows binding native datetime and date objects for update and fetch operations.

Fetching Data

When fetching date and time data, the Snowflake data types are converted into Python data types:

Snowflake Data Types Python Data Type Behavior
TIMESTAMP_TZ datetime with tzinfo Fetches data, including the time zone offset, and translates it into a datetime with tzinfo object.
TIMESTAMP_LTZ, TIMESTAMP datetime with tzinfo Fetches data, translates it into a datetime object, and attaches tzinfo based on the TIMESTAMP_TYPE_MAPPING session parameter.
TIMESTAMP_NTZ datetime Fetches data and translates it into a datetime object. No time zone information is attached to the object.
DATE date Fetches data and translates it into a date object. No time zone information is attached to the object.

Note

tzinfo is a UTC offset-based time zone object and not IANA time zone names. The time zone names may not match, but equivalent offset-based time zone objects are considered identical.

Updating Data

When updating date and time data, the Python data types are converted to Snowflake data types:

Python Data Type Snowflake Data Types Behavior
datetime TIMESTAMP_TZ, TIMESTAMP_LTZ, TIMESTAMP_NTZ, DATE Converts a datatime object into a string in the format of YYYY-MM-DD HH24:MI:SS.FF TZH:TZM and updates it. If no time zone offset is provided, the string will be in the format of YYYY-MM-DD HH24:MI:SS.FF. The user is responsible for setting the tzinfo for the datatime object.
struct_time TIMESTAMP_TZ, TIMESTAMP_LTZ, TIMESTAMP_NTZ, DATE Converts a struct_time object into a string in the format of YYYY-MM-DD HH24:MI:SS.FF TZH:TZM and updates it. The time zone information is retrieved from time.timezone, which includes the time zone offset from UTC. The user is responsible for setting the TZ environment variable for time.timezone.
date TIMESTAMP_TZ, TIMESTAMP_LTZ, TIMESTAMP_NTZ, DATE Converts a date object into a string in the format of YYYY-MM-DD. No time zone is considered.
time TIMESTAMP_TZ, TIMESTAMP_LTZ, TIMESTAMP_NTZ, DATE Converts a time object into a string in the format of HH24:MI:SS.FF. No time zone is considered.
timedelta TIMESTAMP_TZ, TIMESTAMP_LTZ, TIMESTAMP_NTZ, DATE Converts a timedelta object into a string in the format of HH24:MI:SS.FF. No time zone is considered.