Python Programming/Database Programming


Generic Database Connectivity using ODBCEdit

The Open Database Connectivity (ODBC) API standard allows transparent connections with any database that supports the interface. This includes most popular databases, such as PostgreSQL or Microsoft Access. The strengths of using this interface is that a Python script or module can be used on different databases by only modifying the connection string.

There are four ODBC modules for Python:

  1. PythonWin ODBC Module: provided by Mark Hammond with the PythonWin package for Microsoft Windows (only). This is a minimal implementation of ODBC, and conforms to Version 1.0 of the Python Database API. Although it is stable, it will likely not be developed any further.[1]
  2. mxODBC: a commercial Python package (http://www.egenix.com/products/python/mxODBC/), which features handling of DateTime objects and prepared statements (using parameters).
  3. pyodbc: an open-source Python package (http://code.google.com/p/pyodbc), which uses only native Python data-types and uses prepared statements for increased performance. The present version supports the Python Database API Specification v2.0.[2]
  4. pypyodbc: a "pure Python" DBAPI adapter based on the ctypes module, (https://pypi.python.org/pypi/pypyodbc/1.3.0) , (http://code.google.com/p/pypyodbc/), with a focus on keeping code "Simple - the whole module is implemented in a single script with less than 3000 lines".

pyodbcEdit

An example using the pyodbc Python package with a Microsoft Access file (although this database connection could just as easily be a MySQL database):

import pyodbc
 
DBfile = '/data/MSAccess/Music_Library.mdb'
conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb)};DBQ='+DBfile)
cursor = conn.cursor()
 
SQL = 'SELECT Artist, AlbumName FROM RecordCollection ORDER BY Year;'
for row in cursor.execute(SQL): # cursors are iterable
    print row.Artist, row.AlbumName
 
cursor.close()
conn.close()

Many more features and examples are provided on the pyodbc website.

code create problem shown below. ImportError: DLL load failed: The specified procedure could not be found.

Postgres connection in PythonEdit

-> see Python Programming/Databases code create problem shown below ImportError: DLL load failed: The specified procedure could not be found.

MySQL connection in PythonEdit

-> see Python Programming/Databases

SQLAlchemy in ActionEdit

SQLAlchemy has become the favorite choice for many large Python projects that use databases. A long, updated list of such projects is listed on the SQLAlchemy site. Additionally, a pretty good tutorial can be found there, as well. Along with a thin database wrapper, Elixir, it behaves very similarly to the ORM in Rails, ActiveRecord.

See alsoEdit

ReferencesEdit

  1. Hammond, M.; Robinson, A. (2000). Python Programming on Win32. O'Reilly. ISBN 1-56592-621-8. 
  2. Lemburg, M.-A. (2007). "Python Database API Specification v2.0". Python. http://www.python.org/dev/peps/pep-0249/. 

External linksEdit


Last modified on 12 February 2014, at 17:22