Ive got an MS Access accdb file that from which I need to extract data programatically and insert it into Postgresql
This post deals with connecting to and querying data from MS Access 2007 on Windows 7 using Python 2.7

The simplest approach is to use pyodbc - a python package which is based on the Open Database Connectivity (ODBC) API

First get pyodbc: http://code.google.com/p/pyodbc/

Its available as an exe so simply double click to install on windows. For linux the source is available and is installed via distutils by running...

sudo python setup.py install

...from within the pyodbc directory


A connection is made to the access database by

connection = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path_to_file;")

A query is executed via a cursor object

eg:

Start python within cmd or terminal and enter the following:


>>> import pyodbc
>>> conn = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ
=C:\\access\\site_be.accdb;")
>>> cursor = conn.cursor()
>>> sql = "select * from dist_code;"
>>> for row in cursor.execute(sql):
...  print row.dist_code
...
250
500
750
1000
1250
1500
1750
2000
2250
2500
2750
3000
WT140
WT180
>>> cursor.close()
>>> conn.close()
>>>

Note: For this eg to work "literally" you would need to create an access db site_be.accdb and place it in C:\access\
The database should contain atleast one table - dist_code and this table would need to contain one column dist_code

Notice the escaped backslashes in the connection assignment - "C:\\access\\site_be.accdb"
If \ is used instead of \\ you would get an error:

Traceback (most recent call last):
File "", line 1, in conn = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=;")

Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x167c Thread 0x1568 DBC 0x1c67a5c

Jet'. (63) (SQLDriverConnectW); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x167c Thread 0x1568 DBC 0x1c67a5c

Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] Not a valid file name. (-1044); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x167c Thread 0x1568 DBC 0x1c67a5c

Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x167c Thread 0x1568 DBC 0x1c67a5c

Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] Not a valid file name. (-1044)")

 
Compiled from:
http://en.wikibooks.org/wiki/Python_Programming/Database_Programming
http://code.google.com/p/pyodbc/wiki/GettingStarted