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 by default, which applies to Python extended format codes, e.g. ...WHERE name=%s or ...WHERE name=%(name)s. Connection.connect can override paramstyle to change the bind variable formats to "qmark" or "numeric" where the variables are ? or :N, respectively.

For example:

format: .execute("... WHERE my_column = %s", (value,))
pyformat: .execute("... WHERE my_column = %(name)s", {"name": value})
qmark: .execute("... WHERE my_column = ?", (value,))
numeric: .execute("... WHERE my_column = :1", (value,))

Note

The binding variable occurs on the client side if paramstyle is "pyformat" or "format", and on the server side if "qmark" or "numeric". Currently, there is no significant difference between those options in terms of performance or features because the connector doesn’t support compiling SQL text followed by multiple executions. Instead, the "qmark" and "numeric" options align with the query text compatibility of other drivers, i.e., JDBC, ODBC, Go Snowflake Driver, which support server side bindings with the variable format ? or :N.

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 of your account (provided by Snowflake).
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.
    %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) to use the internal Snowflake authenticator.
    externalbrowser to authenticate using your web browser and Okta, ADFS, or any other SAML 2.0-compliant identify provider (IdP) that has been defined for your account.
    https://<your_okta_account_name>.okta.com (i.e. the URL endpoint for Okta) to authenticate through native Okta.
    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.
paramstyle   pyformat by default for client side binding. Specify qmark or numeric to change bind variable formats for server side binding.
timezone   None by default, which honors the Snowflake parameter TIMEZONE. Set to the timezone, e.g., America/Los_Angeles to set the session timezone.

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.

Closing the connection explicitly removes the active session from the server; otherwise, the active session continues until it is eventually purged from the server, limiting the number of concurrent queries.

# context manager ensures the connection is closed
with snowflake.connector.connect(...) as con:
    con.cursor().execute(...)

# try & finally to ensure the connection is closed.
con = snowflake.connector.connect(...)
try:
    con.cursor().execute(...)
finally:
    con.close()
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 paramstyle parameter.

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 uses 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

Data Type mappings for qmark and numeric Bindings

If paramstyle is either "qmark" or "numeric", the following default mappings from Python to Snowflake data type are used:

Python Data Type Data Type in String
int FIXED
long FIXED
decimal FIXED
float REAL
str TEXT
unicode TEXT
bytes BINARY
bytearray BINARY
bool BOOLEAN
date DATE
time TIME
timedelta TIME
datetime TIMESTAMP_NTZ
struct_time TIMESTAMP_NTZ

If you need to map to another Snowflake type, e.g., datetime to TIMESTAMP_LTZ, specify the Snowflake data type in a tuple consisting of the Snowflake data type followed by the value. See Binding datetime with TIMESTAMP for examples.

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.