Skip to content

ramiro/pyodbc

 
 

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

18 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

Overview

This project is a Python database module for ODBC that implements the Python DB API 2.0 specification.

homepage:http://code.google.com/p/pyodbc
source:http://github.com/mkleehammer/pyodbc

This module requires:

  • Python 2.4 or greater
  • ODBC 3.0 or greater

On Windows, the easiest way to install is to use the Windows installers from:

http://code.google.com/p/pyodbc/downloads/list

Source can be obtained at

http://github.com/mkleehammer/pyodbc/tree

To build from source, either check the source out of version control or download a source extract and run:

python setup.py build install

Module Specific Behavior

General

  • The pyodbc.connect function accepts a single parameter: the ODBC connection string. This string is not read or modified by pyodbc, so consult the ODBC documentation or your ODBC driver's documentation for details. The general format is:

    cnxn = pyodbc.connect('DSN=mydsn;UID=userid;PWD=pwd')
    
  • Connection caching in the ODBC driver manager is automatically enabled.

  • Call cnxn.commit() since the DB API specification requires a rollback when a connection is closed that was not specifically committed.

  • When a connection is closed, all cursors created from the connection are closed.

Data Types

  • Dates, times, and timestamps use the Python datetime module's date, time, and datetime classes. These classes can be passed directly as parameters and will be returned when querying date/time columns.
  • Binary data is passed and returned in Python buffer objects.
  • Decimal and numeric columns are passed and returned using the Python 2.4 decimal class.

Convenience Methods

  • Cursors are iterable and returns Row objects.

    cursor.execute("select a,b from tmp")
    for row in cursor:
        print row
    
  • The DB API PEP does not specify the return type for Cursor.execute, so pyodbc tries to be maximally convenient:

    1. If a SELECT is executed, the Cursor itself is returned to allow code like the following:

      for row in cursor.execute("select a,b from tmp"):
          print row
      
    2. If an UPDATE, INSERT, or DELETE statement is issued, the number of rows affected is returned:

      count = cursor.execute("delete from tmp where a in (1,2,3)")
      
    3. Otherwise (CREATE TABLE, etc.), None is returned.

  • An execute method has been added to the Connection class. It creates a Cursor and returns whatever Cursor.execute returns. This allows for the following:

    for row in cnxn.execute("select a,b from tmp"):
        print row
    

    or

    rows = cnxn.execute("select * from tmp where a in (1,2,3)").fetchall()
    

    Since each call creates a new Cursor, only use this when executing a single statement.

  • Both Cursor.execute and Connection.execute allow parameters to be passed as additional parameters following the query.

    cnxn.execute("select a,b from tmp where a=? or a=?", 1, 2)
    

    The specification is not entirely clear, but most other drivers require parameters to be passed in a sequence. To ensure compatibility, pyodbc will also accept this format:

    cnxn.execute("select a,b from tmp where a=? or a=?", (1, 2))
    
  • Row objects are derived from tuple to match the API specification, but they also support accessing columns by name.

    for row in cnxn.execute("select A,b from tmp"):
        print row.a, row.b
    
  • The following are not supported or are ignored: nextset, setinputsizes, setoutputsizes.

  • Values in Row objects can be replaced, either by name or index. Sometimes it is convenient to "preprocess" values.

    row = cursor.execute("select a,b from tmp").fetchone()
    
    row.a  = calc(row.a)
    row[1] = calc(row.b)
    

Goals / Design

  • This module should not require any 3rd party modules other than ODBC.
  • Only built-in data types should be used where possible.
    1. Reduces the number of libraries to learn.
    2. Reduces the number of modules and libraries to install.
    3. Eventually a standard is usually introduced. For example, many previous database drivers used the mxDate classes. Now that Python 2.3 has introduced built-in date/time classes, using those modules is more complicated than using the built-ins.
  • It should adhere to the DB API specification, but be maximally convenient where possible. The most common usages should be optimized for convenience and speed.

Packages

No packages published

Languages

  • C++ 58.9%
  • Python 41.1%