Python Programming/Database Programming

From Wikibooks, open books for an open world
Jump to: navigation, search


Generic Database Connectivity using ODBC[edit]

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[edit]

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 Python[edit]

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

MySQL connection in Python[edit]

-> see Python Programming/Databases

SQLAlchemy in Action[edit]

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[edit]

References[edit]

  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 links[edit]