This module implements the Python Database API Specification, so you should first be familiar with it.
Make a direct connection to a database and create a cursor:
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass')
cursor = cnxn.cursor()
Select some values and print them:
cursor.execute("select user_id, user_name from users")
for row in cursor:
print row.user_id, row.user_name
Select the values, but use a more compact form. The execute function returns the cursor object when a SELECT is executed, so execute can be moved into the for loop:
for row in cursor.execute("select user_id, user_name from users"):
print row.user_id, row.user_name
Select a calculated value, giving it a name:
cursor.execute("select count(*) as user_count from users")
row = cursor.fetchone()
print '%d users' % row.user_count
Supply parameters:
cursor.execute("select count(*) as user_count from users where age > ?", 21)
row = cursor.fetchone()
print '%d users' % row.user_count
Delete some records and retrieve the count:
count = cursor.execute("delete from users where age < ?", 18)
print "deleted %s users" % count
Returns a new Connection object.
The connection string is passed unmodified to SQLDriverConnect. Connection strings can have driver specific components and you should refer to the SQLDriverConnect or other ODBC documentation for details, but below are two common examples.
To connect using a DSN (a data source specified in the Data Access control panel applet), use a string similar to the following.
cnxn = pyodbc.connect("DSN=dsnname")
cnxn = pyodbc.connect("DSN=dsnname;PWD=password")
cnxn = pyodbc.connect("DSN=dsnname;UID=user;PWD=password")
To connect to SQL Server directly (without a DSN), you must specify the server and database to connect to using SQL Server-specific keywords. Note that the braces are required around the driver name.
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=server;DATABASE=database;UID=user;PWD=password)
The DB API defines a set of functions that convert from well-known types to data types required by the database module. If your code does not need to be portable between database modules (if you will only use pyodbc), you do not need to use these.
datetime.date and
datetime.time, but there is no way to specify this.string type.float type. This is not entirely accurate since the module uses different types of numbers
for different ODBC data types. Instead of using this, simply pass int, float, double, or decimal objects.int type.buffer instance.buffer type.The following ODBC constants are defined. They only used with ODBC specific functions such as Cursor.tables.
False if the connection is in manual-commit mode (the default), which is the mode described by the DB API. True if the connection is in auto-commit mode. This can be set using the autocommit keyword in the connection function or can be changed by setting this attribute.
The search pattern escape character used to escape '%' and '_' in search patterns, as returned by SQLGetInfo(SQL_SEARCH_PATTERN_ESCAPE). The value is driver specific.
This is a new method (not in the DB API) that creates a new Cursor object and returns Cursor.execute(...). See Cursor.execute for a description of the parameters.
for results in cnxn.execute("select user_id from tmp"):
print results.user_id
Since a new Cursor is created by each call, do not use when executing multiple statements in a row.
Close the connection now (rather than whenever __del__ is called). The connection will be unusable from this point forward; a ProgrammingError exception 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.
Commit any pending transaction to the database.
Note that Connections do not (yet) support autocommit; Connection.commit() must
be called or changes will be rolled back when the connection is closed.
Causes the the database to roll back to the start of any pending transaction.
Return a new Cursor object using the connection.
Calls SQLGetInfo, passing infotype and returns the result as a Boolean, string,
integer, or long value. The return type is determined by infotype.
The infotype value should be one of the following constants, defined in the pyodbc module. The table below shows the data type returned. See SQLGetInfo for the meaning of each constant.
| Constant | Return Type |
| SQL_ACCESSIBLE_PROCEDURES | True or False |
| SQL_ACCESSIBLE_TABLES | True or False |
| SQL_ACTIVE_ENVIRONMENTS | int |
| SQL_AGGREGATE_FUNCTIONS | int or long |
| SQL_ALTER_DOMAIN | int or long |
| SQL_ALTER_TABLE | int or long |
| SQL_ASYNC_MODE | int or long |
| SQL_BATCH_ROW_COUNT | int or long |
| SQL_BATCH_SUPPORT | int or long |
| SQL_BOOKMARK_PERSISTENCE | int or long |
| SQL_CATALOG_LOCATION | int |
| SQL_CATALOG_NAME | True or False |
| SQL_CATALOG_NAME_SEPARATOR | string |
| SQL_CATALOG_TERM | string |
| SQL_CATALOG_USAGE | int or long |
| SQL_COLLATION_SEQ | string |
| SQL_COLUMN_ALIAS | True or False |
| SQL_CONCAT_NULL_BEHAVIOR | int |
| SQL_CONVERT_FUNCTIONS | int or long |
| SQL_CONVERT_VARCHAR | int or long |
| SQL_CORRELATION_NAME | int |
| SQL_CREATE_ASSERTION | int or long |
| SQL_CREATE_CHARACTER_SET | int or long |
| SQL_CREATE_COLLATION | int or long |
| SQL_CREATE_DOMAIN | int or long |
| SQL_CREATE_SCHEMA | int or long |
| SQL_CREATE_TABLE | int or long |
| SQL_CREATE_TRANSLATION | int or long |
| SQL_CREATE_VIEW | int or long |
| SQL_CURSOR_COMMIT_BEHAVIOR | int |
| SQL_CURSOR_ROLLBACK_BEHAVIOR | int |
| SQL_DATABASE_NAME | string |
| SQL_DATA_SOURCE_NAME | string |
| SQL_DATA_SOURCE_READ_ONLY | True or False |
| SQL_DATETIME_LITERALS | int or long |
| SQL_DBMS_NAME | string |
| SQL_DBMS_VER | string |
| SQL_DDL_INDEX | int or long |
| SQL_DEFAULT_TXN_ISOLATION | int or long |
| SQL_DESCRIBE_PARAMETER | True or False |
| SQL_DM_VER | string |
| SQL_DRIVER_HDESC | int or long |
| SQL_DRIVER_HENV | int or long |
| SQL_DRIVER_HLIB | int or long |
| SQL_DRIVER_HSTMT | int or long |
| SQL_DRIVER_NAME | string |
| SQL_DRIVER_ODBC_VER | string |
| SQL_DRIVER_VER | string |
| SQL_DROP_ASSERTION | int or long |
| SQL_DROP_CHARACTER_SET | int or long |
| SQL_DROP_COLLATION | int or long |
| SQL_DROP_DOMAIN | int or long |
| SQL_DROP_SCHEMA | int or long |
| SQL_DROP_TABLE | int or long |
| SQL_DROP_TRANSLATION | int or long |
| SQL_DROP_VIEW | int or long |
| SQL_DYNAMIC_CURSOR_ATTRIBUTES1 | int or long |
| SQL_DYNAMIC_CURSOR_ATTRIBUTES2 | int or long |
| SQL_EXPRESSIONS_IN_ORDERBY | True or False |
| SQL_FILE_USAGE | int |
| SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1 | int or long |
| SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES2 | int or long |
| SQL_GETDATA_EXTENSIONS | int or long |
| SQL_GROUP_BY | int |
| SQL_IDENTIFIER_CASE | int |
| SQL_IDENTIFIER_QUOTE_CHAR | string |
| SQL_INDEX_KEYWORDS | int or long |
| SQL_INFO_SCHEMA_VIEWS | int or long |
| SQL_INSERT_STATEMENT | int or long |
| SQL_INTEGRITY | True or False |
| SQL_KEYSET_CURSOR_ATTRIBUTES1 | int or long |
| SQL_KEYSET_CURSOR_ATTRIBUTES2 | int or long |
| SQL_KEYWORDS | string |
| SQL_LIKE_ESCAPE_CLAUSE | True or False |
| SQL_MAX_ASYNC_CONCURRENT_STATEMENTS | int or long |
| SQL_MAX_BINARY_LITERAL_LEN | int or long |
| SQL_MAX_CATALOG_NAME_LEN | int |
| SQL_MAX_CHAR_LITERAL_LEN | int or long |
| SQL_MAX_COLUMNS_IN_GROUP_BY | int |
| SQL_MAX_COLUMNS_IN_INDEX | int |
| SQL_MAX_COLUMNS_IN_ORDER_BY | int |
| SQL_MAX_COLUMNS_IN_SELECT | int |
| SQL_MAX_COLUMNS_IN_TABLE | int |
| SQL_MAX_COLUMN_NAME_LEN | int |
| SQL_MAX_CONCURRENT_ACTIVITIES | int |
| SQL_MAX_CURSOR_NAME_LEN | int |
| SQL_MAX_DRIVER_CONNECTIONS | int |
| SQL_MAX_IDENTIFIER_LEN | int |
| SQL_MAX_INDEX_SIZE | int or long |
| SQL_MAX_PROCEDURE_NAME_LEN | int |
| SQL_MAX_ROW_SIZE | int or long |
| SQL_MAX_ROW_SIZE_INCLUDES_LONG | True or False |
| SQL_MAX_SCHEMA_NAME_LEN | int |
| SQL_MAX_STATEMENT_LEN | int or long |
| SQL_MAX_TABLES_IN_SELECT | int |
| SQL_MAX_TABLE_NAME_LEN | int |
| SQL_MAX_USER_NAME_LEN | int |
| 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 | int |
| SQL_NULL_COLLATION | int |
| SQL_NUMERIC_FUNCTIONS | int or long |
| SQL_ODBC_INTERFACE_CONFORMANCE | int or long |
| SQL_ODBC_VER | string |
| SQL_OJ_CAPABILITIES | int or long |
| SQL_ORDER_BY_COLUMNS_IN_SELECT | True or False |
| SQL_PARAM_ARRAY_ROW_COUNTS | int or long |
| SQL_PARAM_ARRAY_SELECTS | int or long |
| SQL_PROCEDURES | True or False |
| SQL_PROCEDURE_TERM | string |
| SQL_QUOTED_IDENTIFIER_CASE | int |
| SQL_ROW_UPDATES | True or False |
| SQL_SCHEMA_TERM | string |
| SQL_SCHEMA_USAGE | int or long |
| SQL_SCROLL_OPTIONS | int or long |
| SQL_SEARCH_PATTERN_ESCAPE | string |
| SQL_SERVER_NAME | string |
| SQL_SPECIAL_CHARACTERS | string |
| SQL_SQL92_DATETIME_FUNCTIONS | int or long |
| SQL_SQL92_FOREIGN_KEY_DELETE_RULE | int or long |
| SQL_SQL92_FOREIGN_KEY_UPDATE_RULE | int or long |
| SQL_SQL92_GRANT | int or long |
| SQL_SQL92_NUMERIC_VALUE_FUNCTIONS | int or long |
| SQL_SQL92_PREDICATES | int or long |
| SQL_SQL92_RELATIONAL_JOIN_OPERATORS | int or long |
| SQL_SQL92_REVOKE | int or long |
| SQL_SQL92_ROW_VALUE_CONSTRUCTOR | int or long |
| SQL_SQL92_STRING_FUNCTIONS | int or long |
| SQL_SQL92_VALUE_EXPRESSIONS | int or long |
| SQL_SQL_CONFORMANCE | int or long |
| SQL_STANDARD_CLI_CONFORMANCE | int or long |
| SQL_STATIC_CURSOR_ATTRIBUTES1 | int or long |
| SQL_STATIC_CURSOR_ATTRIBUTES2 | int or long |
| SQL_STRING_FUNCTIONS | int or long |
| SQL_SUBQUERIES | int or long |
| SQL_SYSTEM_FUNCTIONS | int or long |
| SQL_TABLE_TERM | string |
| SQL_TIMEDATE_ADD_INTERVALS | int or long |
| SQL_TIMEDATE_DIFF_INTERVALS | int or long |
| SQL_TIMEDATE_FUNCTIONS | int or long |
| SQL_TXN_CAPABLE | int |
| SQL_TXN_ISOLATION_OPTION | int or long |
| SQL_UNION | int or long |
| SQL_USER_NAME | string |
| SQL_XOPEN_CLI_YEAR | string |
These objects represent a database cursor, which is used to manage the context of a fetch operation. Cursors created from the same connection are not isolated, i.e., any changes done to the database by a cursor are immediately visible by the other cursors.
This read-only attribute is a sequence of 7-item sequences. Each of these sequences contains information describing one result column: (name, type_code, display_size, internal_size, precision, scale, null_ok). pyodbc only provides values for name, type_code, internal_size, and null_ok. The other values are set to None. This attribute will be None for operations that do not return rows or if the cursor has not had an operation invoked via the executeXXX() method yet. The type_code member is the class type used to create the Python objects when reading rows. For example, a varchar column's type will be str. The complete list of types supported is listed in the Data Types section.
This is always -1.
This is not yet supported.
Close the cursor now (rather than whenever __del__ is called). The cursor will be unusable from this point forward; a ProgrammingError exception will be raised if any operation is attempted with the cursor.
Prepare and execute SQL. Parameters may be passed as a sequence, as specified by the DB API, or as individual parameters.
# standard
cursor.execute("select a from tbl where b=? and c=?", (x, y))
# pyodbc extension
cursor.execute("select a from tbl where b=? and c=?", x, y)
The DB API specification does not specify the return value of this method. Cursors in pyodbc return different things based on the SQL statement executed. Select statements return the Cursor object itself to allow more compact code such as putting the execute method into for loops or appending fetchone or fetchall:
for row in cursor.execute("select album_id, photo_id from photos"):
print row.album_id, row.photo_id
row = cursor.execute("select count(*) from tmp").fetchone()
rows = cursor.execute("select * from tmp").fetchall()
Update and delete statements return the number of rows affected:
count = cursor.execute("update photos set processed=1 where user_id=1")
count = cursor.execute("delete from photos where user_id=1")
All other statements return None.
Prepare a database operation (query or command) and then execute it against all parameter sequences or mappings
found in the sequence seq_of_parameters. This method returns None.
Fetch the next row of a query result set, returning a single Row, or None when no more
data is available.
A ProgrammingError exception is raised if the previous call to executeXXX() did not produce any result set or no call was issued yet.
cursor.execute("select user_name from photos where user_id=?", userid)
row = cursor.fetchone()
if row:
print row.user_name
These are optional in the API and are not supported.
Fetch the next set of rows of a query result, returning a list of Rows. An empty list is returned when no more rows are available.
The number of rows to fetch per call is specified by the parameter. If it is not given, the cursor's arraysize, which defaults to 1, determines the number of rows to be fetched. If this is not possible due to the specified number of rows not being available, fewer rows may be returned.
A ProgrammingError exception is raised if the previous call to executeXXX() did not produce any result set or no call was issued yet.
Fetch all remaining rows of a query result, returning them as a list of Rows. Since this reads all rows into memory, it should not be used if there are a lot of rows. Consider iterating over the rows instead.
A ProgrammingError exception is raised if the previous call to executeXXX() did not produce any result set or no call was issued yet.
cursor.execute("select photo_id from photos where user_id=1")
rows = cursor.fetchall()
for row in rows:
print row.user_name
These methods allow a cursor to be used in a for loop, returning a single Row for
each iteration. This allows all rows to be visited easily.
cursor.execute("select photo_id from photos where user_id=1")
for row in cursor:
print row.photo_id
Executes SQLTables and creates a results set of tables defined in the data source. Returns the Cursor.
The table, catalog, and schema interpret the '_' and '%' characters as wildcards. The escape character is driver specific, so use Connection.searchescape.
Each row has the following columns. See the SQLTables documentation for more information.
for row in cursor.tables():
print row.table_name
Creates a results set of column names in specified tables by executing the ODBC SQLColumns function. Each row fetched has the following columns:
True, only unique indexes are retured. Otherwise all
indexes are returned.True, CARDINALITY and PAGES are returned only if they are
readily available from the serverEach row fetched has the following columns:
Executes SQLSpecialColumns with SQL_BEST_ROWID which creates a result set of columns that uniquely identify a row. Returns the Cursor object. Each row fetched has the following columns.
Executes SQLSpecialColumns with SQL_ROWVER which creates a result set of columns that are automatically updated when any value in the row is updated. Returns the Cursor object. Each row fetched has the following columns.
Creates a results set of column names that make up the primary key for a table by executing the SQLPrimaryKeys function. Each row fetched has the following columns:
Executes the SQLForeignKeys function and creates a results set of column names that are foreign keys in the specified table (columns in the specified table that refer to primary keys in other tables) or foreign keys in other tables that refer to the primary key in the specified table. Each row fetched has the following columns:
Executes SQLProcedures and creates a result set of information about the procedures in the data source. Each row fetched has the following columns:
Executes SQLGetTypeInfo a creates a result set with information about the specified data type or all data types supported by the ODBC driver if not specified. Each row fetched has the following columns:
The column metadata from Cursor.description is also accessible from Row objects as
the cursor_description attribute. This is convenient when Rows are used as ad-hoc
data structures and are passed to other functions that need the metadata; now the Cursor does
not need to be passed with them.
row = cursor.execute("select name, account_id from persons").fetchone()
column_names = [ t[0] for t in row.cursor_description ]
The DB API specifies that results must be tuple-like, so columns are normally accessed by indexing into the
sequence (e.g. row[0]) and pyodbc supports this. However, columns can also be accessed by name:
cursor.execute("select album_id, photo_id from photos where user_id=1")
row = cursor.fetchone()
print row.album_id, row.photo_id
print row[0], row[1] # same as above, but less readable
This makes the code easier to maintain when modifying SQL, more readable, and allows rows to be used where a
custom class might otherwise be used. All rows from a single execute share the same dictionary of
column names, so using Row objects to hold a large result set may also use less memory than creating a object for
each row.
The SQL "as" keyword allows the name of a column in the result set to be specified. This is useful if a column name has a spaces or if there is no name:
cursor.execute("select count(*) as photo_count from photos where user_id=1")
row = cursor.fetchone()
print row.photo_count
Though SQL is very powerful, values sometimes need to be modified before they can be used. Rows allow their values to be replaced, which makes Rows even more convenient ad-hoc dat structures.
# Replace a datetime in each row with one that has a time zone. rows = cursor.fetchall() for row in rows: row.start_date = row.start_date.astimezone(tz)
Note that only existing columns can be replaced; new columns cannot be added to rows. If you want to add a value that doesn't exist in the database, add a NULL to the select statement as a placeholder:
row = cursor.execute("select name, NULL as account_id from persons").fetchone()
row.account_id = 1
The following table shows the ODBC data types supported and the Python type used to represent values.
None is always used for NULL values.
| ODBC | Python |
| char, varchar, longvarchar, GUID | string |
| wchar, wvarchar, wlongvarchar | unicode |
| smallint, integer, tinyint | int |
| bigint | long |
| decimal, numeric | decimal |
| real, float, double | double |
| date | datetime.date |
| time | datetime.time |
| timestamp | datetime.datetime |
| bit | bool |
| binary, varbinary, longvarbinary | buffer |
When an error occurs, the type of exception raised is based on the SQLSTATE.
| SQLSTATE | Exception Class |
| 0A000 | NotSupportedError |
| 22xxx | DataError |
| 23xxx | IntegrityError |
| 40002 | IntegrityError |
| 24xxx, 25xxx, 42xxx | Programming Error |
| All Others | DatabaseError |
Most of the ODBC catalog functions are available as methods on Cursor objects. The results are presented as SELECT results in rows that are fetched normally. Refer to Microsoft's ODBC documentation for details of how to use each function.
cnxn = pyodbc.connect(...)
cursor = cnxn.cursor()
for row in cursor.tables():
print row.table_name
Some of the parameters, such as table in Cursor.tables (SQLTables) accept search patterns. In these parameters, the underscore character (_) is represents a single-character wildcard and the percent character (%) represents any sequence of zero or more characters. To include these characters as literals, precede them with the escape character Connection.searchesc. (The escape character is driver dependent.)
| ODBC Function | Method | Description |
| SQLTables | Cursor.tables | Returns a list of table, catalog, or schema names, and table types. |
| SQLColumns | Cursor.columns | Returns a list of column names in specified tables. |
| SQLStatistics | Cursor.statistics | Returns a list of statistics about a single table and the indexes associated with the table. |
| SQLSpecialColumns | Cursor.rowIdColumns | Returns a list of columns that uniquely identify a row. |
| SQLSpecialColumns | Cursor.rowVerColumns | Returns a list of columns that are automatically updated any any value in the row is updated. |
| SQLPrimaryKeys | Cursor.primaryKeys | Returns a list of column names that make up the primary key for a table. |
| SQLForeignKeys | Cursor.foreignKeys | Returns a list of column names that are foreign keys in the specified table (columns in the specified table that refer to primary keys in other tables) or foreign keys in other tables that refer to the primary key in the specified table. |
| SQLProcedures | Cursor.procedures | Returns information about the procedures in the data source. |
| SQLGetTypeInfo | Cursor.getTypeInfo | Returns a information about the specified data type or all data types supported by the driver. |