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

Functions¶

connect(parameters...)¶

Purpose:

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.

Parameters:

The valid input parameters are:

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)¶

For the required account parameter, specify your account identifier.

Note that the account identifier does not include the snowflakecomputing.com domain name. Snowflake automatically appends this when creating the connection.

The following example uses the account name as an identifier for the account myaccount in the organization myorganization.

ctx = snowflake.connector.connect(
    user='<user_name>',
    password='<password>',
    account='myorganization-myaccount',
    ... )

The following example uses the account locator xy12345 as the account identifier:

ctx = snowflake.connector.connect(
    user='<user_name>',
    password='<password>',
    account='xy12345',
    ... )

Note that this example uses an account in the AWS US West (Oregon) region. If the account is in a different region or if the account uses a different cloud provider, you need to specify additional segments after the account locator.

Methods¶

autocommit(True|False)¶

Purpose:

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

close()¶

Purpose:

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()¶

Purpose:

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

rollback()¶

Purpose:

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

cursor()¶

Purpose:

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)

Purpose:

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)¶

Purpose:

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.

Example:

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])

Note

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)¶

Purpose:

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.

If sql_stream ends with comment lines, you must set remove_comments to True, similar to the following:

sql_script = """
-- This is first comment line;
select 1;
select 2;
-- This is comment in middle;
-- With some extra comment lines;
select 3;
-- This is the end with last line comment;
"""
sql_stream = StringIO(sql_script)
with con.cursor() as cur:
        for result_cursor in con.execute_stream(sql_stream,remove_comments=True):
            for result in result_cursor:
                print(f"Result: {result}")

get_query_status(query_id)¶

Purpose:

Returns the status of a query.

Parameters:

query_id

Returns:

Returns the QueryStatus object that represents the status of the query.

Example:

See Checking the status of a query.

get_query_status_throw_if_error(query_id)¶

Purpose:

Returns the status of a query. If the query results in an error, this method raises a ProgrammingError (as the execute() method would).

Parameters:

query_id

Returns:

Returns the QueryStatus object that represents the status of the query.

Example:

See Checking the status of a query.

is_valid()¶

Purpose:

Returns True if the connection is stable enough to receive queries.

is_still_running(query_status)¶

Purpose:

Returns True if the query status indicates that the query has not yet completed or is still in process.

Parameters:

query_status

Example:

See Checking the status of a query.

is_an_error(query_status)¶

Purpose:

Returns True if the query status indicates that the query resulted in an error.

Parameters:

query_status

Example:

See Checking the status of a query.

Attributes¶

expired¶

Tracks whether the connection’s master token has expired.

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.

Methods¶

close()

Purpose:

Closes the cursor object.

describe(command [, parameters][, timeout][, file_stream])¶

Purpose:

Returns metadata about the result set without executing a database command. This returns the same metadata that is available in the description attribute after executing a query.

This method was introduced in version 2.4.6 of the Snowflake Connector for Python.

Parameters:

See the parameters for the execute() method.

Returns:

Returns a list of ResultMetadata objects that describe the columns in the result set.

Example:

See Retrieving column metadata.

execute(command [, parameters][, timeout][, file_stream])¶

Purpose:

Prepares and executes a database command.

Parameters:

command

A string containing the SQL statement to execute.

parameters

(Optional) If you used parameters for binding data in the SQL statement, set this to the list or dictionary of variables that should be bound to those parameters.

For more information about mapping the Python data types for the variables to the SQL data types of the corresponding columns, see Data type mappings for qmark and numeric bindings.

timeout

(Optional) Number of seconds to wait for the query to complete. If the query has not completed after this time has passed, the query should be aborted.

file_stream

(Optional) When executing a PUT command, you can use this parameter to upload an in-memory file-like object (e.g. the I/O object returned from the Python open() function), rather than a file on the filesystem. Set this parameter to that I/O object.

When specifying the URI for the data file in the PUT command:

• You can use any directory path. The directory path that you specify in the URI is ignored.

• For the filename, specify the name of the file that should be created on the stage.

For example, to upload a file from a file stream to a file named:

use the following call:

cursor.execute(
    "PUT file://this_directory_path/is_ignored/myfile.csv @mystage",
    file_stream=<io_object>)

Returns:

Returns the reference of a Cursor object.

executemany(command, seq_of_parameters)¶

Purpose:

Prepares a database command and executes it against all parameter sequences found in seq_of_parameters. You can use this method to perform a batch insert operation.

Parameters:

command

The command 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.

Returns:

Returns the reference of a Cursor object.

Example:

# This example uses qmark (question mark) binding, so
# you must configure the connector to use this binding style.
from snowflake import connector
connector.paramstyle='qmark'

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.

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.

execute_async(...)¶

Purpose:

Prepares and submits a database command for asynchronous execution. See Performing an asynchronous query.

Parameters:

This method uses the same parameters as the execute() method.

Returns:

Returns the reference of a Cursor object.

Example:

See Examples of asynchronous queries.

fetch_arrow_all()¶

Purpose:

This method fetches all the rows in a cursor and loads them into a PyArrow table.

Parameters:

force_microsecond_precision

When True, all timestamp columns are converted to microsecond precision, ensuring consistent schema across all batches. This feature is useful when your data contains timestamps outside the nanosecond range (1677-2262), such as ‘9999-12-31’ or ‘0001-01-01’. When False (default), precision is determined per-batch based on the data, which might cause pyarrow schema mismatch errors when combining batches. Note that enabling this truncates sub-microsecond precision (scale 7-9).

Returns:

Returns a PyArrow table containing all the rows from the result set.

If there are no rows, this returns None.

Example:

See Distributing workloads that fetch results with the Snowflake Connector for Python.

fetch_arrow_batches()¶

Purpose:

This method fetches a subset of the rows in a cursor and delivers them to a PyArrow table.

Parameters:

force_microsecond_precision

When True, all timestamp columns are converted to microsecond precision, ensuring consistent schema across all batches. This feature is useful when your data contains timestamps outside the nanosecond range (1677-2262), such as ‘9999-12-31’ or ‘0001-01-01’. When False (default), precision is determined per-batch based on the data, which might cause pyarrow schema mismatch errors when combining batches. Note that enabling this truncates sub-microsecond precision (scale 7-9).

Returns:

Returns a PyArrow table containing a subset of the rows from the result set.

Returns None if there are no more rows to fetch.

Example:

See Distributing workloads that fetch results with the Snowflake Connector for Python.

get_result_batches()¶

Purpose:

Returns a list of ResultBatch objects that you can use to fetch a subset of rows from the result set.

Parameters:

None.

Returns:

Returns a list of ResultBatch objects or None if the query has not finished executing.

Example:

See Distributing workloads that fetch results with the Snowflake Connector for Python.

get_results_from_sfqid(query_id)¶

Purpose:

Retrieves the results of an asynchronous query or a previously submitted synchronous query.

Parameters:

query_id

Example:

See Using the query ID to retrieve the results of a query.

fetchone()¶

Purpose:

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])¶

Purpose:

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()¶

Purpose:

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

fetch_pandas_all()¶

Purpose:

This method fetches all the rows in a cursor and loads them into a pandas DataFrame.

Parameters:

force_microsecond_precision

When True, all timestamp columns are converted to microsecond precision, ensuring consistent schema across all batches. This feature is useful when your data contains timestamps outside the nanosecond range (1677-2262), such as ‘9999-12-31’ or ‘0001-01-01’. When False (default), precision is determined per-batch based on the data, which might cause pyarrow schema mismatch errors when combining batches. Note that enabling this truncates sub-microsecond precision (scale 7-9).

Returns:

Returns a DataFrame containing all the rows from the result set.

For more information about pandas data frames, see the pandas DataFrame documentation .

If there are no rows, this returns None.

Usage Notes:

• This method is not a complete replacement for the read_sql() method of pandas; this method is to provide a fast way to retrieve data from a SELECT query and store the data in a pandas DataFrame.

• Currently, this method works only for SELECT statements.

Examples:

ctx = snowflake.connector.connect(
          host=host,
          user=user,
          password=password,
          account=account,
          warehouse=warehouse,
          database=database,
          schema=schema,
          protocol='https',
          port=port)

# Create a cursor object.
cur = ctx.cursor()

# Execute a statement that will generate a result set.
sql = "select * from t"
cur.execute(sql)

# Fetch the result set from the cursor and deliver it as the pandas DataFrame.
df = cur.fetch_pandas_all()

# ...

fetch_pandas_batches()¶

Purpose:

This method fetches a subset of the rows in a cursor and delivers them to a pandas DataFrame.

Parameters:

force_microsecond_precision

When True, all timestamp columns are converted to microsecond precision, ensuring consistent schema across all batches. This feature is useful when your data contains timestamps outside the nanosecond range (1677-2262), such as ‘9999-12-31’ or ‘0001-01-01’. When False (default), precision is determined per-batch based on the data, which might cause pyarrow schema mismatch errors when combining batches. Note that enabling this truncates sub-microsecond precision (scale 7-9).

Returns:

Returns a DataFrame containing a subset of the rows from the result set.

For more information about pandas data frames, see the pandas DataFrame documentation.

Returns None if there are no more rows to fetch.

Usage Notes:

• Depending upon the number of rows in the result set, as well as the number of rows specified in the method call, the method might need to be called more than once, or it might return all rows in a single batch if they all fit.

• This method is not a complete replacement for the read_sql() method of pandas; this method is to provide a fast way to retrieve data from a SELECT query and store the data in a pandas DataFrame.

• Currently, this method works only for SELECT statements.

Examples:

ctx = snowflake.connector.connect(
          host=host,
          user=user,
          password=password,
          account=account,
          warehouse=warehouse,
          database=database,
          schema=schema,
          protocol='https',
          port=port)

# Create a cursor object.
cur = ctx.cursor()

# Execute a statement that will generate a result set.
sql = "select * from t"
cur.execute(sql)

# Fetch the result set from the cursor and deliver it as the pandas DataFrame.
for df in cur.fetch_pandas_batches():
    my_dataframe_processing_function(df)

# ...

__iter__()¶

Returns self to make cursors compatible with the iteration protocol.

Attributes¶

description¶

Read-only attribute that returns metadata about the columns in the result set.

This attribute is set after you call the execute() method to execute the query. (In version 2.4.6 or later, you can retrieve this metadata without executing the query by calling the describe() method.)

This attribute is set to one of the following:

• Versions 2.4.5 and earlier: This attribute is set to a list of tuples.

• Versions 2.4.6 and later: This attribute is set to a list of ResultMetadata objects.

Each tuple or ResultMetadata object contains the metadata that describes a column in the result set. You can access the metadata by index or, in versions 2.4.6 and later, by ResultMetadata object attribute:

For examples of getting this attribute, see Retrieving column metadata.

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 or execute_async 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)

stats

Provides detailed row-level statistics for DML operations, particularly useful for CTAS (CREATE TABLE AS SELECT) statements where DML statistics were previously unavailable.

Returns a QueryResultStats NamedTuple with four fields:

• num_rows_inserted : Number of rows inserted (int | None)

• num_rows_deleted : Number of rows deleted (int | None)

• num_rows_updated : Number of rows updated (int | None)

• num_dml_duplicates : Number of duplicate rows in DML statement (int | None)

If no DML stats are available, returns a QueryResultStats instance with all fields set to None, including the following situations:

• DML operations where no rows were affected (such as a DELETE … clause with a WHERE condition returning FALSE for all entries)

• Non-DML type of SQL statements (such as DDL and DQL)

• Multi-statements

• Async queries (execute_async)

• Result retrieval with QueryID (get_results_from_sfqid)

Note that the stats property does not return None in these cases; it always returns a QueryResultStats instance with all fields set to None.

Type codes¶

In the Cursor object, the description attribute and the describe() method provide a list of tuples (or, in versions 2.4.6 and later, ResultMetadata objects) that describe the columns in the result set.

In a tuple, the value at the index 1 (the type_code attribute In the ResultMetadata object) represents the column data type. The Snowflake Connector for Python uses the following map to get the string representation, based on the type code:

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:

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.

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.

Attributes¶

Methods¶

to_arrow()¶

Purpose:

This method returns a PyArrow table containing the rows in the ResultBatch object.

Parameters:

None.

Returns:

Returns a PyArrow table containing the rows from the ResultBatch object.

If there are no rows, this returns None.

to_pandas()¶

Purpose:

This method returns a pandas DataFrame containing the rows in the ResultBatch object.

Parameters:

None.

Returns:

Returns a pandas DataFrame containing the rows from the ResultBatch object.

If there are no rows, this returns an empty pandas DataFrame.

Methods¶

None.

Attributes¶

name¶

Name of the column

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.

is_nullable¶

True if NULL values allowed for the column or False.

Enums¶

class QueryStatus¶

Represents the status of an asynchronous query. This enum has the following constants:

class CertRevocationCheckMode¶

How to treat certificate revocation lists (CRLs) attached to a certificate. This enum has the following constants:

Functions¶

write_pandas(parameters...)¶

Purpose:

Writes a pandas DataFrame to a table in a Snowflake database.

To write the data to the table, the function saves the data to Parquet files, uses the PUT command to upload these files to a temporary stage, and uses the COPY INTO <table> command to copy the data from the files to the table. You can use some of the function parameters to control how the PUT and COPY INTO <table> statements are executed.

Parameters:

The valid input parameters are:

Returns:

Returns a tuple of (success, num_chunks, num_rows, output) where:

• success is True if the function successfully wrote the data to the table.

• num_chunks is the number of chunks of data that the function copied.

• num_rows is the number of rows that the function inserted.

• output is the output of the COPY INTO <table> command.

Example:

The following example writes the data from a pandas DataFrame to the table named ‘customers’.

import pandas
from snowflake.connector.pandas_tools import write_pandas

# Create the connection to the Snowflake database.
cnx = snowflake.connector.connect(...)

# Create a DataFrame containing data about customers
df = pandas.DataFrame([('Mark', 10), ('Luke', 20)], columns=['name', 'balance'])

# Write the data from the DataFrame to the table named "customers".
success, nchunks, nrows, _ = write_pandas(cnx, df, 'customers')

pd_writer(parameters...)¶

Purpose:

pd_writer is an insertion method for inserting data into a Snowflake database.

When calling pandas.DataFrame.to_sql, pass in method=pd_writer to specify that you want to use pd_writer as the method for inserting data. (You do not need to call pd_writer from your own code. The to_sql method calls pd_writer and supplies the input parameters needed.)

For more information see:

• insertion method documentation.

• pandas documentation.

Note

Please note that when column names in the pandas DataFrame contain only lowercase letters, you must enclose the column names in double quotes; otherwise the connector raises a ProgrammingError.

The snowflake-sqlalchemy library does not quote lowercase column names when creating a table, while pd_writer quotes column names by default. The issue arises because the COPY INTO command expects column names to be quoted.

Future improvements will be made in the snowflake-sqlalchemy library.

For example:

import pandas as pd
from snowflake.connector.pandas_tools import pd_writer

sf_connector_version_df = pd.DataFrame([('snowflake-connector-python', '1.0')], columns=['NAME', 'NEWEST_VERSION'])

# Specify that the to_sql method should use the pd_writer function
# to write the data from the DataFrame to the table named "driver_versions"
# in the Snowflake database.
sf_connector_version_df.to_sql('driver_versions', engine, index=False, method=pd_writer)

# When the column names consist of only lower case letters, quote the column names
sf_connector_version_df = pd.DataFrame([('snowflake-connector-python', '1.0')], columns=['"name"', '"newest_version"'])
sf_connector_version_df.to_sql('driver_versions', engine, index=False, method=pd_writer)

The pd_writer function uses the write_pandas() function to write the data in the DataFrame to the Snowflake database.

Parameters:

The valid input parameters are:

Example:

The following example passes method=pd_writer to the pandas.DataFrame.to_sql method, which in turn calls the pd_writer function to write the data in the pandas DataFrame to a Snowflake database.

import pandas
from snowflake.connector.pandas_tools import pd_writer

# Create a DataFrame containing data about customers
df = pandas.DataFrame([('Mark', 10), ('Luke', 20)], columns=['name', 'balance'])

# Specify that the to_sql method should use the pd_writer function
# to write the data from the DataFrame to the table named "customers"
# in the Snowflake database.
df.to_sql('customers', engine, index=False, method=pd_writer)

Fetching data¶

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

Updating data¶

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