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
A connection is made to the access database byconnection = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path_to_file;")A query is executed via a cursor objecteg: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()
>>>
The database should contain atleast one table - dist_code and this table would need to contain one column dist_codeNotice 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
