Connection objects manage connections to the database.

Each manages a single ODBC HDBC.

There is no constructor; Connections can only be created by the module's connect function.

variables

autocommit

True if the connection is in autocommit mode; False otherwise. Changing this value updates the ODBC autocommit setting.

searchescape

The ODBC search pattern escape character, as returned by SQLGetInfo(SQL_SEARCH_PATTERN_ESCAPE), used to escape special characters such as '%' and ''. These are driver specific.

timeout

An optional integer query timeout, in seconds. Use zero, the default, to disable.

The timeout is applied to all cursors created by the connection, so it cannot be changed for a given connection.

If a query timeout occurs, the database should raise an OperationalError with SQLSTATE HYT00 or HYT01.

Note: This attribute only affects queries. To set the timeout for the actual connection process, use the timeout keyword of the pyodbc.connect function.

methods

cursor

cnxn.cursor() --> Cursor

Returns a new Cursor Object using the connection.

pyodbc supports multiple cursors per connection but your database may not.

commit

cnxn.commit()

Commits any pending transaction to the database.

Pending transactions are automatically rolled back when a connection is closed, so be sure to call this.

rollback

cnxn.rollback()

Causes the the database to roll back to the start of any pending transaction.

You can call this even if no work has been performed on the cursor, allowing it to be used in finally statements, etc.

close

cnxn.close()

Closes the connection. Connections are automatically closed when they are deleted, but you should call this if the connection is referenced in more than one place.

The connection will be unusable from this point forward and a ProgrammingError will be raised if any operation is attempted with the connection. The same applies to all cursor objects trying to use the connection.

Note that closing a connection without committing the changes first will cause an implicit rollback to be performed.

getinfo

getinfo(type) --> str | int | bool

Returns general information about the driver and data source associated with a connection by calling SQLGetInfo and returning its results. See Microsoft's SQLGetInfo documentation for the types of information.

The supported types and data type of the return values are:

| SQL_EXPRESSIONS_IN_ORDERBY | True or False | |:-----------------------------------|:--------------| | SQL_FILE_USAGE | number | | SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1 | number | | SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES2 | number | | SQL_GETDATA_EXTENSIONS | number | | SQL_GROUP_BY | number | | SQL_IDENTIFIER_CASE | number | | SQL_IDENTIFIER_QUOTE_CHAR | string | | SQL_INDEX_KEYWORDS | number | | SQL_INFO_SCHEMA_VIEWS | number | | SQL_INSERT_STATEMENT | number | | SQL_INTEGRITY | True or False | | SQL_KEYSET_CURSOR_ATTRIBUTES1 | number | | SQL_KEYSET_CURSOR_ATTRIBUTES2 | number | | SQL_KEYWORDS | string | | SQL_LIKE_ESCAPE_CLAUSE | True or False | | SQL_MAX_ASYNC_CONCURRENT_STATEMENTS | number | | SQL_MAX_BINARY_LITERAL_LEN | number | | SQL_MAX_CATALOG_NAME_LEN | number | | SQL_MAX_CHAR_LITERAL_LEN | number | | SQL_MAX_COLUMNS_IN_GROUP_BY | number | | SQL_MAX_COLUMNS_IN_INDEX | number | | SQL_MAX_COLUMNS_IN_ORDER_BY | number | | SQL_MAX_COLUMNS_IN_SELECT | number | | SQL_MAX_COLUMNS_IN_TABLE | number | | SQL_MAX_COLUMN_NAME_LEN | number | | SQL_MAX_CONCURRENT_ACTIVITIES | number | | SQL_MAX_CURSOR_NAME_LEN | number | | SQL_MAX_DRIVER_CONNECTIONS | number | | SQL_MAX_IDENTIFIER_LEN | number | | SQL_MAX_INDEX_SIZE | number | | SQL_MAX_PROCEDURE_NAME_LEN | number | | SQL_MAX_ROW_SIZE | number | | SQL_MAX_ROW_SIZE_INCLUDES_LONG | True or False | | SQL_MAX_SCHEMA_NAME_LEN | number | | SQL_MAX_STATEMENT_LEN | number | | SQL_MAX_TABLES_IN_SELECT | number | | SQL_MAX_TABLE_NAME_LEN | number | | SQL_MAX_USER_NAME_LEN | number | | SQL_MULTIPLE_ACTIVE_TXN | True or False | | SQL_MULT_RESULT_SETS | True or False | | SQL_NEED_LONG_DATA_LEN | True or False | | SQL_NON_NULLABLE_COLUMNS | number | | SQL_NULL_COLLATION | number | | SQL_NUMERIC_FUNCTIONS | number | | SQL_ODBC_INTERFACE_CONFORMANCE | number | | SQL_ODBC_VER | string | | SQL_OJ_CAPABILITIES | number | | SQL_ORDER_BY_COLUMNS_IN_SELECT | True or False | | SQL_PARAM_ARRAY_ROW_COUNTS | number | | SQL_PARAM_ARRAY_SELECTS | number | | SQL_PROCEDURES | True or False | | SQL_PROCEDURE_TERM | string | | SQL_QUOTED_IDENTIFIER_CASE | number | | SQL_ROW_UPDATES | True or False | | SQL_SCHEMA_TERM | string | | SQL_SCHEMA_USAGE | number | | SQL_SCROLL_OPTIONS | number | | SQL_SEARCH_PATTERN_ESCAPE | string | | SQL_SERVER_NAME | string | | SQL_SPECIAL_CHARACTERS | string | | SQL_SQL92_DATETIME_FUNCTIONS | number | | SQL_SQL92_FOREIGN_KEY_DELETE_RULE | number | | SQL_SQL92_FOREIGN_KEY_UPDATE_RULE | number | | SQL_SQL92_GRANT | number | | SQL_SQL92_NUMERIC_VALUE_FUNCTIONS | number | | SQL_SQL92_PREDICATES | number | | SQL_SQL92_RELATIONAL_JOIN_OPERATORS | number | | SQL_SQL92_REVOKE | number | | SQL_SQL92_ROW_VALUE_CONSTRUCTOR | number | | SQL_SQL92_STRING_FUNCTIONS | number | | SQL_SQL92_VALUE_EXPRESSIONS | number | | SQL_SQL_CONFORMANCE | number | | SQL_STANDARD_CLI_CONFORMANCE | number | | SQL_STATIC_CURSOR_ATTRIBUTES1 | number | | SQL_STATIC_CURSOR_ATTRIBUTES2 | number | | SQL_STRING_FUNCTIONS | number | | SQL_SUBQUERIES | number | | SQL_SYSTEM_FUNCTIONS | number | | SQL_TABLE_TERM | string | | SQL_TIMEDATE_ADD_INTERVALS | number | | SQL_TIMEDATE_DIFF_INTERVALS | number | | SQL_TIMEDATE_FUNCTIONS | number | | SQL_TXN_CAPABLE | number | | SQL_TXN_ISOLATION_OPTION | number | | SQL_UNION | number | | SQL_USER_NAME | string | | SQL_XOPEN_CLI_YEAR | string |

execute

execute(sql, *params) --> Cursor

Create a new Cursor object, call its execute method, and returns it. See Cursor.execute for more details.

This is a convenience method that is not part of the DB API. Since a new Cursor is allocated by each call, this should not be used if more than one SQL statement needs to be executed.

add_output_converter

add_output_converter(sqltype, func)

Register an output converter function that will be called whenever a value with the given SQL type is read from the database.

sqltype

The integer SQL type value to convert, which can be one of the defined standard constants (e.g. pyodbc.SQL_VARCHAR) or a database-specific value (e.g. -151 for the SQL Server 2008 geometry data type).

func

The converter function which will be called with a single parameter, the value, and should return the converted value. If the value is NULL, the parameter will be None. Otherwise it will be a Python string.

clear_output_converters

clear_output_converters()

Remove all output converter functions.