Python Programming/Database Programming

Generic Database Connectivity using ODBC

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

pyodbc

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)
#use below conn if using with Access 2007, 2010 .accdb file
#conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};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
    # print row # if print row it will return tuple of all fields

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 Python

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

MySQL connection in Python

-> see Python Programming/Databases

SQLAlchemy in Action

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 also

• Python Programming/Databases

References

External links

• SQLAlchemy
• SQLObject
• PEP 249 - Python Database API Specification v2.0
• SQLite Tutorial
• Database Topic Guide on python.org
• MySQldb Tutorial