My favorites | Sign in
Project Home Downloads Wiki
READ-ONLY: This project has been archived. For more information see this post.
Search
for
Connection  
Connection API documentation
Updated Jul 28, 2011 by mkleehammer

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.

Comment by Pho...@gmail.com, Aug 2, 2010

Hi, I want to know if i can customize the number of SQL_MAX_COLUMN_NAME_LEN. Because 30 is too small in my application.

Comment by project member mkleehammer, Sep 6, 2010

You cannot customize these numbers. The value is determined by the database - you use SQL_MAX_COLUMN_NAME_LEN simply to ask the database what its maximum size is.

Comment by dream...@gmail.com, May 7, 2013

\COPY command of postgresql is giving error "pyodbc.Error: ('HY000', 'HY000? ERROR: syntax error at or near "\\" at character 7;\nError while executing the query (7) (SQLExecDirectW)')"

please Help Thanks in advance

Comment by webmas...@agilion.de, Mar 12, 2015

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

is wrong in Version 1.3.3. The autocommit is only set during connect. Changing the autocommit member has no effect.

Powered by Google Project Hosting