Python Programming/Database Programming

From Wikibooks, the open-content textbooks collection

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

[edit] 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, autocommit=True)
cursor = conn.cursor()
 
SQL = 'SELECT Artist, AlbumName FROM RecordCollection ORDER BY Year'
cursor.execute(SQL)
for row in cursor: # cursors are iterable
    print row.Artist, row.AlbumName
 
cursor.close()
conn.close()

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

[edit] Postgres connection in Python

import psycopg2
conn = psycopg2.connect("dbname=test")
cursor = conn.cursor()
cursor.execute("select * from test");
for i in cursor.next():
    print i
conn.close()

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

[edit] References

[edit] External links

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