web/docs.html

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>pyodbc</title>
<link rel="stylesheet" type="text/css" href="styles.css" />
</head>
<body>

<div id="titlebox">
  pyodbc - A Python DB API module for ODBC
</div>

<div id="nav">
    <a href="index.html">Home</a>
  : <a href="docs.html">Documentation</a>
  : <a href="http://sourceforge.net/project/showfiles.php?group_id=162557">Download</a>
  : <a href="http://sourceforge.net/news/?group_id=162557">News</a>
  : <a href="http://github.com/mkleehammer/pyodbc">Source Code (github)</a>
  : <a href="http://sourceforge.net/tracker/?group_id=162557&amp;atid=824254">Bug&nbsp;Tracker</a>
  : <a href="http://sourceforge.net/projects/pyodbc">SourceForge</a>
  : <a href="http://www.python.org/peps/pep-0249.html">Python DB API</a>
  : <a href="license.html">MIT License</a>
</div>

<div id="contents">

<ul id="toc">
  <li><a href="#examples">Quick Examples</a></li>
  <li><a href="#pyodbc">Module Interface</a></li>
  <li><a href="#connection">Connection Objects</a></li>
  <li><a href="#cursor">Cursor Objects</a></li>
  <li><a href="#row">Row Objects</a></li>
  <li><a href="#datatypes">Data Types</a></li>
  <li><a href="#datatypes">Errors</a></li>
  <li><a href="#catalog">Catalog Functions</a></li>
</ul>

<p>This module implements the <a href="http://www.python.org/peps/pep-0249.html">Python Database API Specification</a>,
so you should first be familiar with it.</p>

<h1 id="examples">Quick Examples</h1>

<p>Make a direct connection to a database and create a cursor:</p>

<pre>
  cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass')
  cursor = cnxn.cursor()</pre>

<p>Select some values and print them:</p>

<pre>
  cursor.execute("select user_id, user_name from users")
  for row in cursor:
      print row.user_id, row.user_name</pre>
  
<p>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:</p>

<pre>
  for row in cursor.execute("select user_id, user_name from users"):
      print row.user_id, row.user_name</pre>
  

<p>Select a calculated value, giving it a name:</p>

<pre>
  cursor.execute("select count(*) as user_count from users")
  row = cursor.fetchone()
  print '%d users' % row.user_count</pre>

<p>Supply parameters:</p>

<pre>
  cursor.execute("select count(*) as user_count from users where age &gt; ?", 21)
  row = cursor.fetchone()
  print '%d users' % row.user_count</pre>

<p>Delete some records and retrieve the count:</p>

<pre>
  count = cursor.execute("delete from users where age &lt; ?", 18)
  print "deleted %s users" % count</pre>

<h1 id="pyodbc">Module Interface</h1>

<h2 id="connect">connect(connectionstring, autocommit=False)</h2>

<dl>
  <dt>connectionstring</dt>
  <dd>The ODBC connection string.</dd>
  
  <dt>autocommit</td>
  <dd>A Boolean that determines if the connection should be in autocommit mode or manual-commit
    mode.</dd>
</dl>

<p>Returns a new <a href="#connection">Connection</a> object.</p>

<p>The connection string is passed unmodified to <a
href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqldriverconnect.asp">SQLDriverConnect</a>.
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.</p>

<p>To connect using a DSN (a data source specified in the Data Access control panel applet),
use a string similar to the following.</p>

<pre>
  cnxn = pyodbc.connect("DSN=<i>dsnname</i>")
  cnxn = pyodbc.connect("DSN=<i>dsnname</i>;PWD=<i>password</i>")
  cnxn = pyodbc.connect("DSN=<i>dsnname</i>;UID=<i>user</i>;PWD=<i>password</i>")</pre>

<p>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.</p>

<pre>
  cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=<i>server</i>;DATABASE=<i>database</i>;UID=<i>user</i>;PWD=<i>password</i>)</pre>

<h2>Module Description Variables</h2>
<dl>
  <dt>version</dt>
  <dd>The pyodbc version as a string in the format <i>major</i>.<i>minor</i>.<i>revision</i>.</dd>

  <dt>apilevel</dt>
  <dd>The string constant '2.0' indicating this module supports DB API level 2.0.</dd>

  <dt>lowercase</dt>
  <dd>A Boolean that controls whether column names in result rows are lowercased.  This can be
  changed any time and affects queries executed after the change.  The default is False.  This
  can be useful when database columns have inconsistent capitalization.</dd>

  <dt>pooling</dt>
  <dd>A Boolean indicating whether connection pooling is enabled.  This is a global (HENV)
    setting, so it can only be modified before the first connection is made.  The default is
    True, which enables ODBC connection pooling.</dd>

  <dt>threadsafety</dt>
  <dd>The integer 1, indicating that threads may share the module but not connections.  Note
      that connections and cursors may be used by different threads, just not at the same time.</dd>

  <dt>qmark</dt>
  <dd>The string constant "qmark" to indicate parameters are identified using question marks.</dd>
</dl>

<h2>DB API Type Functions</h2>

<p>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.</p>

<dl>

  <dt>Date(year,month,day), DateFromTicks(ticks)</dt>
  <dd>Both of these return
  a <a href="http://docs.python.org/lib/datetime-date.html">datetime.date</a> instance.</dd>

  <dt>Time(hour,minute,second), TimeFromTicks(ticks)</dt>
  <dd>Both of these return
  a <a href="http://docs.python.org/lib/datetime-time.html">datetime.time</a> instance.</dd>

  <dt>Timestamp(year,month,day,hour,minute,second), TimestampFromTicks(ticks)</dt>
  <dd>Both of these return
  a <a href="http://docs.python.org/lib/datetime-datetime.html">datetime.datetime</a>
  instance.</dd>

  <dt>DATETIME</dt>

  <dd>Set to the <a href="http://docs.python.org/lib/datetime-datetime.html">datetime.datetime</a> type.  This is not
    entirely accurate since dates and times actually use two different classes, <code>datetime.date</code> and
    <code>datetime.time</code>, but there is no way to specify this.</dd>

  <dt>STRING</dt>
  <dd>Set to the <code>string</code> type.</dd>
  
  <dt>NUMBER</dt>
  
  <dd>Set to the <code>float</code> 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.</dd>
  
  <dt>ROWID</dt>
  
  <dd>Set to the <code>int</code> type.</dd>
    
  <dt>Binary(string)</dt>
  
  <dd>Returns a <code>buffer</code> instance.</dd>
  
  <dt>BINARY</dt>
  
  <dd>Set to the <code>buffer</code> type.</dd>
  
</dl>

<h2>Module Constants</h2>

<p>The following ODBC constants are defined.  They only used with ODBC specific functions such
as Cursor.tables.</p>

<ul>
    <li>SQL_ACCESSIBLE_PROCEDURES</li>
    <li>SQL_ACCESSIBLE_TABLES</li>
    <li>SQL_ACTIVE_ENVIRONMENTS</li>
    <li>SQL_AGGREGATE_FUNCTIONS</li>
    <li>SQL_ALTER_DOMAIN</li>
    <li>SQL_ALTER_TABLE</li>
    <li>SQL_ASYNC_MODE</li>
    <li>SQL_BATCH_ROW_COUNT</li>
    <li>SQL_BATCH_SUPPORT</li>
    <li>SQL_BIGINT</li>
    <li>SQL_BINARY</li>
    <li>SQL_BIT</li>
    <li>SQL_BOOKMARK_PERSISTENCE</li>
    <li>SQL_CATALOG_LOCATION</li>
    <li>SQL_CATALOG_NAME</li>
    <li>SQL_CATALOG_NAME_SEPARATOR</li>
    <li>SQL_CATALOG_TERM</li>
    <li>SQL_CATALOG_USAGE</li>
    <li>SQL_CHAR</li>
    <li>SQL_COLLATION_SEQ</li>
    <li>SQL_COLUMN_ALIAS</li>
    <li>SQL_CONCAT_NULL_BEHAVIOR</li>
    <li>SQL_CONVERT_FUNCTIONS</li>
    <li>SQL_CONVERT_VARCHAR</li>
    <li>SQL_CORRELATION_NAME</li>
    <li>SQL_CREATE_ASSERTION</li>
    <li>SQL_CREATE_CHARACTER_SET</li>
    <li>SQL_CREATE_COLLATION</li>
    <li>SQL_CREATE_DOMAIN</li>
    <li>SQL_CREATE_SCHEMA</li>
    <li>SQL_CREATE_TABLE</li>
    <li>SQL_CREATE_TRANSLATION</li>
    <li>SQL_CREATE_VIEW</li>
    <li>SQL_CURSOR_COMMIT_BEHAVIOR</li>
    <li>SQL_CURSOR_ROLLBACK_BEHAVIOR</li>
    <li>SQL_DATABASE_NAME</li>
    <li>SQL_DATA_SOURCE_NAME</li>
    <li>SQL_DATA_SOURCE_READ_ONLY</li>
    <li>SQL_DATETIME_LITERALS</li>
    <li>SQL_DBMS_NAME</li>
    <li>SQL_DBMS_VER</li>
    <li>SQL_DDL_INDEX</li>
    <li>SQL_DECIMAL</li>
    <li>SQL_DEFAULT_TXN_ISOLATION</li>
    <li>SQL_DESCRIBE_PARAMETER</li>
    <li>SQL_DM_VER</li>
    <li>SQL_DOUBLE</li>
    <li>SQL_DRIVER_NAME</li>
    <li>SQL_DRIVER_ODBC_VER</li>
    <li>SQL_DRIVER_VER</li>
    <li>SQL_DROP_ASSERTION</li>
    <li>SQL_DROP_CHARACTER_SET</li>
    <li>SQL_DROP_COLLATION</li>
    <li>SQL_DROP_DOMAIN</li>
    <li>SQL_DROP_SCHEMA</li>
    <li>SQL_DROP_TABLE</li>
    <li>SQL_DROP_TRANSLATION</li>
    <li>SQL_DROP_VIEW</li>
    <li>SQL_DYNAMIC_CURSOR_ATTRIBUTES1</li>
    <li>SQL_DYNAMIC_CURSOR_ATTRIBUTES2</li>
    <li>SQL_EXPRESSIONS_IN_ORDERBY</li>
    <li>SQL_FILE_USAGE</li>
    <li>SQL_FLOAT</li>
    <li>SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1</li>
    <li>SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES2</li>
    <li>SQL_GETDATA_EXTENSIONS</li>
    <li>SQL_GROUP_BY</li>
    <li>SQL_GUID</li>
    <li>SQL_IDENTIFIER_CASE</li>
    <li>SQL_IDENTIFIER_QUOTE_CHAR</li>
    <li>SQL_INDEX_KEYWORDS</li>
    <li>SQL_INFO_SCHEMA_VIEWS</li>
    <li>SQL_INSERT_STATEMENT</li>
    <li>SQL_INTEGER</li>
    <li>SQL_INTEGRITY</li>
    <li>SQL_INTERVAL_DAY</li>
    <li>SQL_INTERVAL_DAY_TO_HOUR</li>
    <li>SQL_INTERVAL_DAY_TO_MINUTE</li>
    <li>SQL_INTERVAL_DAY_TO_SECOND</li>
    <li>SQL_INTERVAL_HOUR</li>
    <li>SQL_INTERVAL_HOUR_TO_MINUTE</li>
    <li>SQL_INTERVAL_HOUR_TO_SECOND</li>
    <li>SQL_INTERVAL_MINUTE</li>
    <li>SQL_INTERVAL_MINUTE_TO_SECOND</li>
    <li>SQL_INTERVAL_MONTH</li>
    <li>SQL_INTERVAL_SECOND</li>
    <li>SQL_INTERVAL_YEAR</li>
    <li>SQL_INTERVAL_YEAR_TO_MONTH</li>
    <li>SQL_KEYSET_CURSOR_ATTRIBUTES1</li>
    <li>SQL_KEYSET_CURSOR_ATTRIBUTES2</li>
    <li>SQL_KEYWORDS</li>
    <li>SQL_LIKE_ESCAPE_CLAUSE</li>
    <li>SQL_LONGVARBINARY</li>
    <li>SQL_LONGVARCHAR</li>
    <li>SQL_MAX_ASYNC_CONCURRENT_STATEMENTS</li>
    <li>SQL_MAX_BINARY_LITERAL_LEN</li>
    <li>SQL_MAX_CATALOG_NAME_LEN</li>
    <li>SQL_MAX_CHAR_LITERAL_LEN</li>
    <li>SQL_MAX_COLUMNS_IN_GROUP_BY</li>
    <li>SQL_MAX_COLUMNS_IN_INDEX</li>
    <li>SQL_MAX_COLUMNS_IN_ORDER_BY</li>
    <li>SQL_MAX_COLUMNS_IN_SELECT</li>
    <li>SQL_MAX_COLUMNS_IN_TABLE</li>
    <li>SQL_MAX_COLUMN_NAME_LEN</li>
    <li>SQL_MAX_CONCURRENT_ACTIVITIES</li>
    <li>SQL_MAX_CURSOR_NAME_LEN</li>
    <li>SQL_MAX_DRIVER_CONNECTIONS</li>
    <li>SQL_MAX_IDENTIFIER_LEN</li>
    <li>SQL_MAX_INDEX_SIZE</li>
    <li>SQL_MAX_PROCEDURE_NAME_LEN</li>
    <li>SQL_MAX_ROW_SIZE</li>
    <li>SQL_MAX_ROW_SIZE_INCLUDES_LONG</li>
    <li>SQL_MAX_SCHEMA_NAME_LEN</li>
    <li>SQL_MAX_STATEMENT_LEN</li>
    <li>SQL_MAX_TABLES_IN_SELECT</li>
    <li>SQL_MAX_TABLE_NAME_LEN</li>
    <li>SQL_MAX_USER_NAME_LEN</li>
    <li>SQL_MULTIPLE_ACTIVE_TXN</li>
    <li>SQL_MULT_RESULT_SETS</li>
    <li>SQL_NEED_LONG_DATA_LEN</li>
    <li>SQL_NON_NULLABLE_COLUMNS</li>
    <li>SQL_NO_NULLS</li>
    <li>SQL_NULLABLE</li>
    <li>SQL_NULLABLE_UNKNOWN</li>
    <li>SQL_NULL_COLLATION</li>
    <li>SQL_NUMERIC</li>
    <li>SQL_NUMERIC_FUNCTIONS</li>
    <li>SQL_ODBC_INTERFACE_CONFORMANCE</li>
    <li>SQL_ODBC_VER</li>
    <li>SQL_OJ_CAPABILITIES</li>
    <li>SQL_ORDER_BY_COLUMNS_IN_SELECT</li>
    <li>SQL_PARAM_ARRAY_ROW_COUNTS</li>
    <li>SQL_PARAM_ARRAY_SELECTS</li>
    <li>SQL_PC_NOT_PSEUDO</li>
    <li>SQL_PC_PSEUDO</li>
    <li>SQL_PC_UNKNOWN</li>
    <li>SQL_PROCEDURES</li>
    <li>SQL_PROCEDURE_TERM</li>
    <li>SQL_QUOTED_IDENTIFIER_CASE</li>
    <li>SQL_REAL</li>
    <li>SQL_ROW_UPDATES</li>
    <li>SQL_SCHEMA_TERM</li>
    <li>SQL_SCHEMA_USAGE</li>
    <li>SQL_SCOPE_CURROW</li>
    <li>SQL_SCOPE_SESSION</li>
    <li>SQL_SCOPE_TRANSACTION</li>
    <li>SQL_SCROLL_OPTIONS</li>
    <li>SQL_SEARCH_PATTERN_ESCAPE</li>
    <li>SQL_SERVER_NAME</li>
    <li>SQL_SMALLINT</li>
    <li>SQL_SPECIAL_CHARACTERS</li>
    <li>SQL_SQL92_DATETIME_FUNCTIONS</li>
    <li>SQL_SQL92_FOREIGN_KEY_DELETE_RULE</li>
    <li>SQL_SQL92_FOREIGN_KEY_UPDATE_RULE</li>
    <li>SQL_SQL92_GRANT</li>
    <li>SQL_SQL92_NUMERIC_VALUE_FUNCTIONS</li>
    <li>SQL_SQL92_PREDICATES</li>
    <li>SQL_SQL92_RELATIONAL_JOIN_OPERATORS</li>
    <li>SQL_SQL92_REVOKE</li>
    <li>SQL_SQL92_ROW_VALUE_CONSTRUCTOR</li>
    <li>SQL_SQL92_STRING_FUNCTIONS</li>
    <li>SQL_SQL92_VALUE_EXPRESSIONS</li>
    <li>SQL_SQL_CONFORMANCE</li>
    <li>SQL_STANDARD_CLI_CONFORMANCE</li>
    <li>SQL_STATIC_CURSOR_ATTRIBUTES1</li>
    <li>SQL_STATIC_CURSOR_ATTRIBUTES2</li>
    <li>SQL_STRING_FUNCTIONS</li>
    <li>SQL_SUBQUERIES</li>
    <li>SQL_SYSTEM_FUNCTIONS</li>
    <li>SQL_TABLE_TERM</li>
    <li>SQL_TIMEDATE_ADD_INTERVALS</li>
    <li>SQL_TIMEDATE_DIFF_INTERVALS</li>
    <li>SQL_TIMEDATE_FUNCTIONS</li>
    <li>SQL_TINYINT</li>
    <li>SQL_TXN_CAPABLE</li>
    <li>SQL_TXN_ISOLATION_OPTION</li>
    <li>SQL_TYPE_DATE</li>
    <li>SQL_TYPE_TIME</li>
    <li>SQL_TYPE_TIMESTAMP</li>
    <li>SQL_UNION</li>
    <li>SQL_UNKNOWN_TYPE</li>
    <li>SQL_USER_NAME</li>
    <li>SQL_VARBINARY</li>
    <li>SQL_VARCHAR</li>
    <li>SQL_WCHAR</li>
    <li>SQL_WLONGVARCHAR</li>
    <li>SQL_WVARCHAR</li>
    <li>SQL_XOPEN_CLI_YEAR</li>
</ul>

<h1 id="connection">Connection Objects</h1>

<h2 id="connection_autocommit">autocommit</h2>

<p>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.</p>

<h2 id="connection_searchesc">searchesc</h2>

<p>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.</p>

<h2>execute(sql, [params])</h2>

<p>This is a new method (not in the DB API) that creates a new Cursor object and returns
Cursor.execute(...).  See <a href="#cursor_execute">Cursor.execute</a> for a description of the
parameters.</p>

<pre>
  for results in cnxn.execute("select user_id from tmp"):
      print results.user_id</pre>

<p>Since a new Cursor is created by each call, do not use when executing multiple statements in
a row.</p>

<h2>close()</h2>

<p>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.</p>

<h2>commit()</h2>

<p>Commit any pending transaction to the database.</p>

<p>Note that Connections do not (yet) support autocommit; <code>Connection.commit()</code> must
be called or changes will be rolled back when the connection is closed.</p>

<h2>rollback()</h2>

<p>Causes the the database to roll back to the start of any pending transaction.</p>

<h2>cursor()</h2>

<p>Return a new <a href="#cursor">Cursor</a> object using the connection.</p>

<h2 id="connection_getinfo">getinfo(infotype)</h2>

<p>Calls SQLGetInfo, passing <code>infotype</code> and returns the result as a Boolean, string,
integer, or long value.  The return type is determined by <code>infotype</code>.</p>

<p>The infotype value should be one of the following constants, defined in the pyodbc module.
The table below shows the data type returned.  See
<a href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlgetinfo.asp?frame=true&amp;hidetoc=true">
SQLGetInfo</a> for the meaning of each constant.</p>

<table class="general">
  <thead>
    <tr><td>Constant</td><td>Return Type</td></tr>
  </thead>
  <tbody>
    <tr><td>SQL_ACCESSIBLE_PROCEDURES</td><td>True or False</td></tr>
    <tr class="treven"><td>SQL_ACCESSIBLE_TABLES</td><td>True or False</td></tr>
    <tr><td>SQL_ACTIVE_ENVIRONMENTS</td><td>int</td></tr>
    <tr class="treven"><td>SQL_AGGREGATE_FUNCTIONS</td><td>int or long</td></tr>
    <tr><td>SQL_ALTER_DOMAIN</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_ALTER_TABLE</td><td>int or long</td></tr>
    <tr><td>SQL_ASYNC_MODE</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_BATCH_ROW_COUNT</td><td>int or long</td></tr>
    <tr><td>SQL_BATCH_SUPPORT</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_BOOKMARK_PERSISTENCE</td><td>int or long</td></tr>
    <tr><td>SQL_CATALOG_LOCATION</td><td>int</td></tr>
    <tr class="treven"><td>SQL_CATALOG_NAME</td><td>True or False</td></tr>
    <tr><td>SQL_CATALOG_NAME_SEPARATOR</td><td>string</td></tr>
    <tr class="treven"><td>SQL_CATALOG_TERM</td><td>string</td></tr>
    <tr><td>SQL_CATALOG_USAGE</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_COLLATION_SEQ</td><td>string</td></tr>
    <tr><td>SQL_COLUMN_ALIAS</td><td>True or False</td></tr>
    <tr class="treven"><td>SQL_CONCAT_NULL_BEHAVIOR</td><td>int</td></tr>
    <tr><td>SQL_CONVERT_FUNCTIONS</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_CONVERT_VARCHAR</td><td>int or long</td></tr>
    <tr><td>SQL_CORRELATION_NAME</td><td>int</td></tr>
    <tr class="treven"><td>SQL_CREATE_ASSERTION</td><td>int or long</td></tr>
    <tr><td>SQL_CREATE_CHARACTER_SET</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_CREATE_COLLATION</td><td>int or long</td></tr>
    <tr><td>SQL_CREATE_DOMAIN</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_CREATE_SCHEMA</td><td>int or long</td></tr>
    <tr><td>SQL_CREATE_TABLE</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_CREATE_TRANSLATION</td><td>int or long</td></tr>
    <tr><td>SQL_CREATE_VIEW</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_CURSOR_COMMIT_BEHAVIOR</td><td>int</td></tr>
    <tr><td>SQL_CURSOR_ROLLBACK_BEHAVIOR</td><td>int</td></tr>
    <tr class="treven"><td>SQL_DATABASE_NAME</td><td>string</td></tr>
    <tr><td>SQL_DATA_SOURCE_NAME</td><td>string</td></tr>
    <tr class="treven"><td>SQL_DATA_SOURCE_READ_ONLY</td><td>True or False</td></tr>
    <tr><td>SQL_DATETIME_LITERALS</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_DBMS_NAME</td><td>string</td></tr>
    <tr><td>SQL_DBMS_VER</td><td>string</td></tr>
    <tr class="treven"><td>SQL_DDL_INDEX</td><td>int or long</td></tr>
    <tr><td>SQL_DEFAULT_TXN_ISOLATION</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_DESCRIBE_PARAMETER</td><td>True or False</td></tr>
    <tr><td>SQL_DM_VER</td><td>string</td></tr>
    <tr class="treven"><td>SQL_DRIVER_HDESC</td><td>int or long</td></tr>
    <tr><td>SQL_DRIVER_HENV</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_DRIVER_HLIB</td><td>int or long</td></tr>
    <tr><td>SQL_DRIVER_HSTMT</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_DRIVER_NAME</td><td>string</td></tr>
    <tr><td>SQL_DRIVER_ODBC_VER</td><td>string</td></tr>
    <tr class="treven"><td>SQL_DRIVER_VER</td><td>string</td></tr>
    <tr><td>SQL_DROP_ASSERTION</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_DROP_CHARACTER_SET</td><td>int or long</td></tr>
    <tr><td>SQL_DROP_COLLATION</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_DROP_DOMAIN</td><td>int or long</td></tr>
    <tr><td>SQL_DROP_SCHEMA</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_DROP_TABLE</td><td>int or long</td></tr>
    <tr><td>SQL_DROP_TRANSLATION</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_DROP_VIEW</td><td>int or long</td></tr>
    <tr><td>SQL_DYNAMIC_CURSOR_ATTRIBUTES1</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_DYNAMIC_CURSOR_ATTRIBUTES2</td><td>int or long</td></tr>
    <tr><td>SQL_EXPRESSIONS_IN_ORDERBY</td><td>True or False</td></tr>
    <tr class="treven"><td>SQL_FILE_USAGE</td><td>int</td></tr>
    <tr><td>SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES2</td><td>int or long</td></tr>
    <tr><td>SQL_GETDATA_EXTENSIONS</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_GROUP_BY</td><td>int</td></tr>
    <tr><td>SQL_IDENTIFIER_CASE</td><td>int</td></tr>
    <tr class="treven"><td>SQL_IDENTIFIER_QUOTE_CHAR</td><td>string</td></tr>
    <tr><td>SQL_INDEX_KEYWORDS</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_INFO_SCHEMA_VIEWS</td><td>int or long</td></tr>
    <tr><td>SQL_INSERT_STATEMENT</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_INTEGRITY</td><td>True or False</td></tr>
    <tr><td>SQL_KEYSET_CURSOR_ATTRIBUTES1</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_KEYSET_CURSOR_ATTRIBUTES2</td><td>int or long</td></tr>
    <tr><td>SQL_KEYWORDS</td><td>string</td></tr>
    <tr class="treven"><td>SQL_LIKE_ESCAPE_CLAUSE</td><td>True or False</td></tr>
    <tr><td>SQL_MAX_ASYNC_CONCURRENT_STATEMENTS</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_MAX_BINARY_LITERAL_LEN</td><td>int or long</td></tr>
    <tr><td>SQL_MAX_CATALOG_NAME_LEN</td><td>int</td></tr>
    <tr class="treven"><td>SQL_MAX_CHAR_LITERAL_LEN</td><td>int or long</td></tr>
    <tr><td>SQL_MAX_COLUMNS_IN_GROUP_BY</td><td>int</td></tr>
    <tr class="treven"><td>SQL_MAX_COLUMNS_IN_INDEX</td><td>int</td></tr>
    <tr><td>SQL_MAX_COLUMNS_IN_ORDER_BY</td><td>int</td></tr>
    <tr class="treven"><td>SQL_MAX_COLUMNS_IN_SELECT</td><td>int</td></tr>
    <tr><td>SQL_MAX_COLUMNS_IN_TABLE</td><td>int</td></tr>
    <tr class="treven"><td>SQL_MAX_COLUMN_NAME_LEN</td><td>int</td></tr>
    <tr><td>SQL_MAX_CONCURRENT_ACTIVITIES</td><td>int</td></tr>
    <tr class="treven"><td>SQL_MAX_CURSOR_NAME_LEN</td><td>int</td></tr>
    <tr><td>SQL_MAX_DRIVER_CONNECTIONS</td><td>int</td></tr>
    <tr class="treven"><td>SQL_MAX_IDENTIFIER_LEN</td><td>int</td></tr>
    <tr><td>SQL_MAX_INDEX_SIZE</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_MAX_PROCEDURE_NAME_LEN</td><td>int</td></tr>
    <tr><td>SQL_MAX_ROW_SIZE</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_MAX_ROW_SIZE_INCLUDES_LONG</td><td>True or False</td></tr>
    <tr><td>SQL_MAX_SCHEMA_NAME_LEN</td><td>int</td></tr>
    <tr class="treven"><td>SQL_MAX_STATEMENT_LEN</td><td>int or long</td></tr>
    <tr><td>SQL_MAX_TABLES_IN_SELECT</td><td>int</td></tr>
    <tr class="treven"><td>SQL_MAX_TABLE_NAME_LEN</td><td>int</td></tr>
    <tr><td>SQL_MAX_USER_NAME_LEN</td><td>int</td></tr>
    <tr class="treven"><td>SQL_MULTIPLE_ACTIVE_TXN</td><td>True or False</td></tr>
    <tr><td>SQL_MULT_RESULT_SETS</td><td>True or False</td></tr>
    <tr class="treven"><td>SQL_NEED_LONG_DATA_LEN</td><td>True or False</td></tr>
    <tr><td>SQL_NON_NULLABLE_COLUMNS</td><td>int</td></tr>
    <tr class="treven"><td>SQL_NULL_COLLATION</td><td>int</td></tr>
    <tr><td>SQL_NUMERIC_FUNCTIONS</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_ODBC_INTERFACE_CONFORMANCE</td><td>int or long</td></tr>
    <tr><td>SQL_ODBC_VER</td><td>string</td></tr>
    <tr class="treven"><td>SQL_OJ_CAPABILITIES</td><td>int or long</td></tr>
    <tr><td>SQL_ORDER_BY_COLUMNS_IN_SELECT</td><td>True or False</td></tr>
    <tr class="treven"><td>SQL_PARAM_ARRAY_ROW_COUNTS</td><td>int or long</td></tr>
    <tr><td>SQL_PARAM_ARRAY_SELECTS</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_PROCEDURES</td><td>True or False</td></tr>
    <tr><td>SQL_PROCEDURE_TERM</td><td>string</td></tr>
    <tr class="treven"><td>SQL_QUOTED_IDENTIFIER_CASE</td><td>int</td></tr>
    <tr><td>SQL_ROW_UPDATES</td><td>True or False</td></tr>
    <tr class="treven"><td>SQL_SCHEMA_TERM</td><td>string</td></tr>
    <tr><td>SQL_SCHEMA_USAGE</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_SCROLL_OPTIONS</td><td>int or long</td></tr>
    <tr><td>SQL_SEARCH_PATTERN_ESCAPE</td><td>string</td></tr>
    <tr class="treven"><td>SQL_SERVER_NAME</td><td>string</td></tr>
    <tr><td>SQL_SPECIAL_CHARACTERS</td><td>string</td></tr>
    <tr class="treven"><td>SQL_SQL92_DATETIME_FUNCTIONS</td><td>int or long</td></tr>
    <tr><td>SQL_SQL92_FOREIGN_KEY_DELETE_RULE</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_SQL92_FOREIGN_KEY_UPDATE_RULE</td><td>int or long</td></tr>
    <tr><td>SQL_SQL92_GRANT</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_SQL92_NUMERIC_VALUE_FUNCTIONS</td><td>int or long</td></tr>
    <tr><td>SQL_SQL92_PREDICATES</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_SQL92_RELATIONAL_JOIN_OPERATORS</td><td>int or long</td></tr>
    <tr><td>SQL_SQL92_REVOKE</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_SQL92_ROW_VALUE_CONSTRUCTOR</td><td>int or long</td></tr>
    <tr><td>SQL_SQL92_STRING_FUNCTIONS</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_SQL92_VALUE_EXPRESSIONS</td><td>int or long</td></tr>
    <tr><td>SQL_SQL_CONFORMANCE</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_STANDARD_CLI_CONFORMANCE</td><td>int or long</td></tr>
    <tr><td>SQL_STATIC_CURSOR_ATTRIBUTES1</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_STATIC_CURSOR_ATTRIBUTES2</td><td>int or long</td></tr>
    <tr><td>SQL_STRING_FUNCTIONS</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_SUBQUERIES</td><td>int or long</td></tr>
    <tr><td>SQL_SYSTEM_FUNCTIONS</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_TABLE_TERM</td><td>string</td></tr>
    <tr><td>SQL_TIMEDATE_ADD_INTERVALS</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_TIMEDATE_DIFF_INTERVALS</td><td>int or long</td></tr>
    <tr><td>SQL_TIMEDATE_FUNCTIONS</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_TXN_CAPABLE</td><td>int</td></tr>
    <tr><td>SQL_TXN_ISOLATION_OPTION</td><td>int or long</td></tr>
    <tr class="treven"><td>SQL_UNION</td><td>int or long</td></tr>
    <tr><td>SQL_USER_NAME</td><td>string</td></tr>
    <tr class="treven"><td>SQL_XOPEN_CLI_YEAR</td><td>string</td></tr>
  </tbody>
</table>

<h1 id="cursor">Cursor Objects</h1>

<p>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.</p>

<h2>description</h2>

<p>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 <i>str</i>.  The complete list of types supported is listed in the <a href="#datatypes">Data
Types</a> section.</p>

<h2>rowcount</h2>

<p>This is always -1.</p>

<h2>callproc(procname[,parameters])</h2>

<p>This is not yet supported.</p>

<h2>close()</h2>
          
<p>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.</p>

<h2 id="cursor_execute">execute(sql [,parameters])</h2>

<p>Prepare and execute SQL.  Parameters may be passed as a sequence, as specified by the DB API, or as individual
parameters.</p>

<pre>
  # 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)</pre>

<p>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:</p>

<pre>
  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()</pre>

<p>Update and delete statements return the number of rows affected:</p>

<pre>
  count = cursor.execute("update photos set processed=1 where user_id=1")

  count = cursor.execute("delete from photos where user_id=1")</pre>

<p>All other statements return <code>None</code>.</p>

<h2>executemany(sql, seq_of_parameters)</h2>

<p>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 <code>None</code>.</p>

<h2>fetchone()</h2>
          
<p>Fetch the next row of a query result set, returning a single <a href="#row">Row</a>, or <code>None</code> when no more
data is available.</p>
            
<p>A ProgrammingError exception is raised if the previous call to executeXXX() did not produce any result set or no
call was issued yet.</p>

<pre>
  cursor.execute("select user_name from photos where user_id=?", userid)
  row = cursor.fetchone()
  if row:
      print row.user_name</pre>

<h2>nextset, setinputsizes, setoutputsize</h2>

<p>These are optional in the API and are not supported.</p>

<h2>fetchmany([size=cursor.arraysize])</h2>
          
<p>Fetch the next set of rows of a query result, returning a list of <a href="#row">Rows</a>. An empty list is returned
when no more rows are available.</p>
            
<p>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.</p>
            
<p>A ProgrammingError exception is raised if the previous call to executeXXX() did not produce any result set or no
call was issued yet.</p>
            
<h2>fetchall()</h2>

<p>Fetch all remaining rows of a query result, returning them as a list of <a href="#row">Rows</a>.  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.</p>
            
<p>A ProgrammingError exception is raised if the previous call to executeXXX() did not produce any result set or no
call was issued yet.</p>

<pre>
  cursor.execute("select photo_id from photos where user_id=1")
  rows = cursor.fetchall()
  for row in rows:
      print row.user_name</pre>

<h2>__iter__, next</h2>

<p>These methods allow a cursor to be used in a <code>for</code> loop, returning a single <a href="#row">Row</a> for
each iteration.  This allows all rows to be visited easily.</p>

<pre>
  cursor.execute("select photo_id from photos where user_id=1")
  for row in cursor:
      print row.photo_id</pre>

<h2 id="cursor_tables">tables(table=None, catalog=None, schema=None, tableType=None)</h2>

<p>Executes SQLTables and creates a results set of tables defined in the data source.  Returns the Cursor.</p>

<p>The table, catalog, and schema interpret the '_' and '%' characters as wildcards.  The escape character is driver
specific, so use <a href="#connection_searchesc">Connection.searchescape</a>.</p>

<p>Each row has the following columns.  See the SQLTables documentation for more information.</p>

<ol start="0">
<li>table_cat: The catalog name.</li>
<li>table_schem: The schema name.</li>
<li>table_name: The table name.</li>
<li>table_type: One of 'TABLE', 'VIEW', SYSTEM TABLE', 'GLOBAL TEMPORARY'
'LOCAL TEMPORARY', 'ALIAS', 'SYNONYM', or a data source-specific type name.</li>
</ol>

<pre>
  for row in cursor.tables():
      print row.table_name</pre>

<h2 id="cursor_columns">columns(table=None, catalog=None, schema=None, column=None)</h2>

<p>Creates a results set of column names in specified tables by executing the ODBC SQLColumns function.
Each row fetched has the following columns:</p>

<ol start="0">
  <li>table_cat</li>
  <li>table_schem</li>
  <li>table_name</li>
  <li>column_name</li>
  <li>data_type</li>
  <li>type_name</li>
  <li>column_size</li>
  <li>buffer_length</li>
  <li>decimal_digits</li>
  <li>num_prec_radix</li>
  <li>nullable</li>
  <li>remarks</li>
  <li>column_def</li>
  <li>sql_data_type</li>
  <li>sql_datetime_sub</li>
  <li>char_octet_length</li>
  <li>ordinal_position</li>
  <li>is_nullable: One of SQL_NULLABLE, SQL_NO_NULLS, SQL_NULLS_UNKNOWN.</li>
</ol>


<h2 id="cursor_statistics">statistics(table, catalog=None, schema=None, unique=False, quick=True)</h2>

Creates a results set of statistics about a single table and the indexes
associated with the table by executing SQLStatistics.

<dl>
<dt>unique</dt>
<dd>If <code>True</code>, only unique indexes are retured.  Otherwise all
indexes are returned.</dd>

<dt>quick</dt>
<dd>If <code>True</code>, CARDINALITY and PAGES are returned only if they are
readily available from the server</dd>
</dl>

<p>Each row fetched has the following columns:</p>

<ol start="0">
  <li>table_cat</li>
  <li>table_schem</li>
  <li>table_name</li>
  <li>non_unique</li>
  <li>index_qualifier</li>
  <li>index_name</li>
  <li>type</li>
  <li>ordinal_position</li>
  <li>column_name</li>
  <li>asc_or_desc</li>
  <li>cardinality</li>
  <li>pages</li>
  <li>filter_condition</li>
</ol>

<h2 id="cursor_rowid">rowIdColumns(table, catalog=None, schema=None, nullable=True)</h2>

<p>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.</p>

<ol start="0">
  <li>scope: One of SQL_SCOPE_CURROW, SQL_SCOPE_TRANSACTION, or SQL_SCOPE_SESSION</li>
  <li>column_name</li>
  <li>data_type: The ODBC SQL data type constant (e.g. SQL_CHAR)</li>
  <li>type_name</li>
  <li>column_size</li>
  <li>buffer_length</li>
  <li>decimal_digits</li>
  <li>pseudo_column: One of SQL_PC_UNKNOWN, SQL_PC_NOT_PSEUDO, SQL_PC_PSEUDO</li>
</ol>

<h2 id="cursor_rowver">rowVerColumns(table, catalog=None, schema=None, nullable=True)</h2>

<p>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.</p>

<ol start="0">
  <li>scope: One of SQL_SCOPE_CURROW, SQL_SCOPE_TRANSACTION, or SQL_SCOPE_SESSION</li>
  <li>column_name</li>
  <li>data_type: The ODBC SQL data type constant (e.g. SQL_CHAR)</li>
  <li>type_name</li>
  <li>column_size</li>
  <li>buffer_length</li>
  <li>decimal_digits</li>
  <li>pseudo_column: One of SQL_PC_UNKNOWN, SQL_PC_NOT_PSEUDO, SQL_PC_PSEUDO</li>
</ol>


<h2 id="cursor_primarykeys">primaryKeys(table, catalog=None, schema=None)</h2>

<p>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:</p>

<ol start="0">
  <li>table_cat</li>
  <li>table_schem</li>
  <li>table_name</li>
  <li>column_name</li>
  <li>key_seq</li>
  <li>pk_name</li>
</ol>

<h2 id="cursor_foreignkeys">foreignKeys(table=None, catalog=None, schema=None,
foreignTable=None, foreignCatalog=None, foreignSchema=None)</h2>

<p>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:</p>

<ol start="0">
  <li>pktable_cat</li>
  <li>pktable_schem</li>
  <li>pktable_name</li>
  <li>pkcolumn_name</li>
  <li>fktable_cat</li>
  <li>fktable_schem</li>
  <li>fktable_name</li>
  <li>fkcolumn_name</li>
  <li>key_seq</li>
  <li>update_rule</li>
  <li>delete_rule</li>
  <li>fk_name</li>
  <li>pk_name</li>
  <li>deferrability</li>
</ol>

<h2 id="cursor_procedures">procedures(procedure=None, catalog=None, schema=None)</h2>

<p>Executes SQLProcedures and creates a result set of information about the
procedures in the data source.  Each row fetched has the following columns:</p>

<ol start="0">
  <li>procedure_cat</li>
  <li>procedure_schem</li>
  <li>procedure_name</li>
  <li>num_input_params</li>
  <li>num_output_params</li>
  <li>num_result_sets</li>
  <li>remarks</li>
  <li>procedure_type</li>
</ol>

<h2 id="cursor_gettypeinfo">getTypeInfo(sqlType=None)</h2>

<p>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:</p>

<ol start="0">
  <li>type_name</li>
  <li>data_type</li>
  <li>column_size</li>
  <li>literal_prefix</li>
  <li>literal_suffix</li>
  <li>create_params</li>
  <li>nullable</li>
  <li>case_sensitive</li>
  <li>searchable</li>
  <li>unsigned_attribute</li>
  <li>fixed_prec_scale</li>
  <li>auto_unique_value</li>
  <li>local_type_name</li>
  <li>minimum_scale</li>
  <li>maximum_scale</li>
  <li>sql_data_type</li>
  <li>sql_datetime_sub</li>
  <li>num_prec_radix</li>
  <li>interval_precision</li>
</ol>

<h1 id="row">Row Objects</h1>

<h2>cursor_description</h2>

<p>The column metadata from Cursor.description is also accessible from Row objects as
the <code>cursor_description</code> 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.</p>

<pre>
row = cursor.execute("select name, account_id from persons").fetchone()
column_names = [ t[0] for t in row.cursor_description ]
</pre>

<h2>Accessing Values</h2>

<p>The DB API specifies that results must be tuple-like, so columns are normally accessed by indexing into the
sequence (e.g. <code>row[0]</code>) and pyodbc supports this.  However, columns can also be accessed by name:</p>

<pre>
cursor.execute("select <b>album_id</b>, <b>photo_id</b> from photos where user_id=1")
row = cursor.fetchone()
print <b>row.album_id</b>, <b>row.photo_id</b> 
print row[0], row[1] # same as above, but less readable</pre>

<p>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 <code>execute</code> 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.</p>

<p>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:</p>

<pre>
cursor.execute("select count(*) <b>as photo_count</b> from photos where user_id=1")
row = cursor.fetchone()
print <b>row.photo_count</b></pre>

<h2>Rows Are Mutable</h2>
  
<p>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.</p>

<pre>
# 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)</pre>

<p>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:</p>

<pre>
row = cursor.execute("select name, NULL as account_id from persons").fetchone()
row.account_id = 1
</pre>

<h1 id="datatypes">Data Types</h1>

<p>The following table shows the ODBC data types supported and the Python type used to represent values.
<code>None</code> is always used for <code>NULL</code> values.</p>

<table border="0" cellspacing="4">
  <thead>
    <tr>
      <td>ODBC</td><td>Python</td></tr>
  </thead>
  <tbody>
    <tr>
      <td>char, varchar, longvarchar, GUID</td>
      <td>string</td>
    </tr>
    <tr>
      <td class="treven">wchar, wvarchar, wlongvarchar</td>
      <td class="treven">unicode</td>
    </tr>
    <tr>
      <td>smallint, integer, tinyint</td>
      <td>int</td>
    </tr>
    <tr>
      <td class="treven">bigint</td>
      <td class="treven">long</td>
    </tr>
    <tr>
      <td>decimal, numeric</td>
      <td>decimal</td>
    </tr>
    <tr>
      <td class="treven">real, float, double</td>
      <td class="treven">double</td>
    </tr>
    <tr>
      <td>date</td>
      <td>datetime.date</td>
    </tr>
    <tr>
      <td class="treven">time</td> 
      <td class="treven">datetime.time</td>
    </tr>
    <tr>
      <td>timestamp</td>
      <td>datetime.datetime</td>
    </tr>
    <tr>
      <td class="treven">bit</td>
      <td class="treven">bool</td>
    </tr>
    <tr>
      <td>binary, varbinary, longvarbinary</td>
      <td>buffer</td>
    </tr>
  </tbody>
</table>

<h1 id="errors">Errors</h1>

<p>When an error occurs, the type of exception raised is based on the SQLSTATE.</p>

<table border="0" cellspacing="4">
  <thead>
    <tr>
      <td>SQLSTATE</td>
      <td>Exception Class</td></tr>
  </thead>
  <tbody>
    <tr>
      <td>0A000</td>
      <td>NotSupportedError</td>
    </tr>
    <tr>
      <td class="treven">22xxx</td>
      <td class="treven">DataError</td>
    </tr>
    <tr>
      <td>23xxx</td>
      <td>IntegrityError</td>
    </tr>
    <tr>
      <td class="treven">40002</td>
      <td class="treven">IntegrityError</td>
    </tr>
    <tr>
      <td>24xxx, 25xxx, 42xxx</td>
      <td>Programming Error</td>
    </tr>
    <tr>
      <td>All Others</td>
      <td>DatabaseError</td>
    </tr>
  </tbody>
</table>

<h1 id="catalog">Catalog Functions</h1>

<p>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.</p>

<pre>
cnxn   = pyodbc.connect(...)
cursor = cnxn.cursor()
for row in cursor.tables():
    print row.table_name</pre>

<p>Some of the parameters, such as <em>table</em> 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.)</p>

<table border="0" cellspacing="4">
  <thead>
    <tr>
      <td>ODBC Function</td>
      <td>Method</td>
      <td>Description</td>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>SQLTables</td>
      <td><a href="#cursor_tables">Cursor.tables</a></td>
      <td>Returns a list of table, catalog, or schema names, and table types.</td>
    </tr>
    <tr>
      <td>SQLColumns</td>
      <td><a href="#cursor_columns">Cursor.columns</a></td>
      <td>Returns a list of column names in specified tables.</td>
    </tr>
    <tr>
      <td>SQLStatistics</td>
      <td><a href="#cursor_statistics">Cursor.statistics</a></td>
      <td>Returns a list of statistics about a single table and the indexes associated with the table.</td>
    </tr>
    <tr>
      <td>SQLSpecialColumns</td>
      <td><a href="#cursor_rowid">Cursor.rowIdColumns</a></td>
      <td>Returns a list of columns that uniquely identify a row.</td>
    </tr>
    <tr>
      <td>SQLSpecialColumns</td>
      <td><a href="#cursor_rowver">Cursor.rowVerColumns</a></td>
      <td>Returns a list of columns that are automatically updated any any value in the row is updated.</td>
    </tr>
    <tr>
      <td>SQLPrimaryKeys</td>
      <td><a href="#cursor_primarykeys">Cursor.primaryKeys</a></td>
      <td>Returns a list of column names that make up the primary key for a table.</td>
    </tr>
    <tr>
      <td>SQLForeignKeys</td>
      <td><a href="#cursor_foreignkeys">Cursor.foreignKeys</a></td>
      <td>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.</td>
    </tr>
    <tr>
      <td>SQLProcedures</td>
      <td><a href="#cursor_procedures">Cursor.procedures</a></td>
      <td>Returns information about the procedures in the data source.</td>
    </tr>
    <tr>
      <td>SQLGetTypeInfo</td>
      <td><a href="#cursor_gettypeinfo">Cursor.getTypeInfo</a></td>
      <td>Returns a information about the specified data type or all data types supported by the driver.</td>
    </tr>
  </tbody>
</table>

</div> <!-- contents -->

<hr />
<a href="http://sourceforge.net"><img src="http://sflogo.sourceforge.net/sflogo.php?group_id=162557&amp;type=4" width="125" height="37" border="0" alt="SourceForge.net Logo" /></a>

</body>
</html>