Python Connector 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 transaction, 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). For more details, see Usage Notes (in this topic).

user

Yes

Login name for the user.

password

Yes

Password for the user.

region

Deprecated Instead, please specify the region as part of the account parameter. This description of the parameter is for backwards compatibility only.

host

No longer used Host name. Used internally only (i.e. does not need to be set).

port

No longer used Port number (443 by default). Used internally only (i.e. 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 this to True if the MFA (Multi-Factor Authentication) passcode is embedded in the login password.

passcode

The passcode provided by Duo when using MFA (Multi-Factor Authentication) for login.

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.

client_prefetch_threads

Number of threads used to download the results sets (4 by default). Increasing the value improves fetch performance but requires more memory.

client_session_keep_alive

False by default. Set this to True to keep the session active indefinitely, even if there is no activity from the user. Make certain to call the close method to terminate the thread properly or the process may hang.

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:

  • Linux: ~/.cache/snowflake/ocsp_response_cache

  • macOS: ~/Library/Caches/Snowflake/ocsp_response_cache

  • Windows: %USERPROFILE%\AppData\Local\Snowflake\Caches\ocsp_response_cache

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 a valid time zone (e.g. America/Los_Angeles) to set the session time zone.

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.

Usage Notes for the account Parameter (for the connect Method)

  • The parameter specifies the Snowflake account you are connecting to and is required.

  • Do not include the Snowflake domain name (snowflakecomputing.com) as part of the parameter. Snowflake automatically appends the domain name to your account name to create the required connection.

  • Your full account name may include additional segments that identify the region and cloud platform where your account is hosted.

    Account name examples by region

    If your account name is xy12345:

    Cloud Platform / Region

    Full Account Name

    AWS

    US West (Oregon)

    xy12345

    US East (N. Virginia)

    xy12345.us-east-1

    Canada (Central)

    xy12345.ca-central-1.aws

    EU (Ireland)

    xy12345.eu-west-1

    EU (Frankfurt)

    xy12345.eu-central-1

    Asia Pacific (Singapore)

    xy12345.ap-southeast-1

    Asia Pacific (Sydney)

    xy12345.ap-southeast-2

    Azure

    East US 2

    xy12345.east-us-2.azure

    US Gov Virginia

    xy12345.us-gov-virginia.azure

    Canada Central

    xy12345.canada-central.azure

    West Europe

    xy12345.west-europe.azure

    Australia East

    xy12345.australia-east.azure

    Southeast Asia

    xy12345.southeast-asia.azure

    Important

    If either of the following conditions is true, your account name is different than the structure described in this example:

    • If your Snowflake Edition is VPS, please contact Snowflake Support for details about your account name.

    • If AWS PrivateLink is enabled for your account, your account name requires an additional privatelink segment. For more details, see AWS PrivateLink & Snowflake.

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.

For example:

# 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.

This example shows executing multiple commands in a single string and then using the sequence of cursors that is returned:

cursor_list = connection1.execute_string(
    "SELECT * FROM testtable WHERE col1 LIKE 'T%';"
    "SELECT * FROM testtable WHERE col2 LIKE 'A%';"
    )

for cursor in cursor_list:
   for row in cursor:
      print(row[0], row[1])

Methods such as execute_string() that allow multiple SQL statements in a single string are vulnerable to SQL injection attacks. Avoid using string concatenation, or functions such as Python’s format() function, to dynamically compose a SQL statement by combining SQL with data from users unless you have validated the user data. The example below demonstrates the problem:

# "Binding" data via the format() function (UNSAFE EXAMPLE)
value1_from_user = "'ok3'); DELETE FROM testtable WHERE col1 = 'ok1'; select pi("
sql_cmd = "insert into testtable(col1) values('ok1'); "                  \
          "insert into testtable(col1) values('ok2'); "                  \
          "insert into testtable(col1) values({col1});".format(col1=value1_from_user)
# Show what SQL Injection can do to a composed statement.
print(sql_cmd)

connection1.execute_string(sql_cmd)

The dynamically-composed statement looks like the following (newlines have been added for readability):

insert into testtable(col1) values('ok1');
insert into testtable(col1) values('ok2');
insert into testtable(col1) values('ok3');
DELETE FROM testtable WHERE col1 = 'ok1';
select pi();

If you are combining SQL statements with strings entered by untrusted users, then it is safer to bind data to a statement than to compose a string. The execute_string() method doesn’t take binding parameters, so to bind parameters use Cursor.execute() or Cursor.executemany().

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 found in seq_of_parameters.

Arguments:

operation

The operation is a string containing the code to execute. The string should contain one or more placeholders (such as question marks) for Binding Data.

For example:

"insert into testy (v1, v2) values (?, ?)"

seq_of_parameters

This should be a sequence (list or tuple) of lists or tuples. See the example code below for example sequences.

Example:

stmt1 = "create table testy (V1 varchar, V2 varchar)"
cs.execute(stmt1)

# A list of lists
sequence_of_parameters1 = [ ['Smith', 'Ann'], ['Jones', 'Ed'] ]
# A tuple of tuples
sequence_of_parameters2 = ( ('Cho', 'Kim'), ('Cooper', 'Pat') )

stmt2 = "insert into testy (v1, v2) values (?, ?)"
cs.executemany(stmt2, sequence_of_parameters1)
cs.executemany(stmt2, sequence_of_parameters2)

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 Connector for Python 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 datetime 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 datetime 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.