Skip to content
This repository
Fetching contributors…

Cannot retrieve contributors at this time

file 234 lines (181 sloc) 9.129 kb
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234
<!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">

  <h1>About pyodbc</h1>
  
  <p>pyodbc is a Python module that allows you to access ODBC databases. It implements the
  <a href="http://www.python.org/peps/pep-0249.html">Python Database API Specification v2.0</a>.</p>

  <p>Some notable features include:</p>
  
  <ul>
    <li>The library is free for commercial and personal use.</li>
    <li>It conforms to the DB API standard.</li>
    <li>No 3rd party libraries are required. Only native Python datatypes are used, such as decimal and datetime.</li>
    <li>It requires Python 2.4 or higher, since the decimal type was added in 2.4. (We are open to suggestions regarding versions for
    earlier Python builds.)</li>
    <li>Additional features have been added to simplify database programming with Python.</li>
  </ul>

  <h1>Additional Features</h1>
  
  <p>The following features are beyond the requirements of the DB API. They are intended to provide a very
  Python-like, convenient programming experience, but you should not use them if your code needs to be portable between
  DB API modules. (Though we hope future DB API specifications will adopt some of these features.)</p>

  <h2>Access Values By Name</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 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 Values Can Be Replaced</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 them even more convenient ad-hoc data structures.</p>

  <pre>
  # Replace the 'start_date' 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 columns cannot be added to rows; only values for existing columns can be modified.</p>

  <h2>Cursors are Iterable</h2>

  <p>The DB API makes this an optional feature. Each iteration returns a row object.</p>

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

  <h2>Cursor.execute Returns the "Right" Thing</h2>
  
  <p>The DB API specification does not specify the return value of Cursor.execute, so pyodbc returns different types
  based on the SQL statement executed.</p>

  <p>A select statement returns the cursor itself, allowing the execute results to be iterated over and used to fetch.
  This makes the code very compact:</p>

  <pre>
  for row in cursor.execute("select album_id, photo_id from photos where user_id=1"):
      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 SQL statements return <code>None</code>.</p>

  <h2>Connection.execute</h2>
  
  <p>pyodbc Connection objects have an execute method that creates new Cursors automatically.</p>
  
  <pre>
    for row in <strong>cnxn</strong>.execute("select user_id from tmp"):
        print row.user_id</pre>

  <p>Since each call creates a new cursor, do not use this when executing multiple statements in a row.</p>
  
  <h2>Passing Parameters</h2>
  
  <p>As specified in the DB API, Cursor.execute accepts an optional sequence of parameters:</p>
  
  <pre>
  cursor.execute("select a from tbl where b=? and c=?", (x, y))</pre>
  
  <p>However, pyodbc also accepts the parameters directly. Note that the parameters are not in a tuple:</p>
      
  <pre>
  cursor.execute("select a from tbl where b=? and c=?", x, y)</pre>

  <h2>Autocommit Mode</h2>

  <p>Connections can be put into autocommit mode using the <code>autocommit</code> keyword of
  the <a href="docs.html#connect">connect</a> function or
  the <a href="docs.html#connection_autocommit">autocommit</a> attribute of the Connection
  object.</p>

  <h2 id="misc">Miscellaneous ODBC Functions</h2>

<p><a href="docs.html#connection_getinfo">Connection.getinfo</a> function is an interface to
SQLGetInfo.</p>


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

<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="docs.html#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="docs.html#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="docs.html#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="docs.html#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="docs.html#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="docs.html#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="docs.html#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="docs.html#cursor_procedures">Cursor.procedures</a></td>
      <td>Returns information about the procedures in the data source.</td>
    </tr>
    <tr>
      <td>SQLProcedures</td>
      <td><a href="docs.html#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>
Something went wrong with that request. Please try again.