What Do I Need to Read Microsoft Access Databases Using Python

What do I need to read Microsoft Access databases using Python?

I've used PYODBC to connect succesfully to an MS Access db - on Windows though. Install was easy, usage is fairly simple, you just need to set the right connection string (the one for MS Access is given in the list) and of you go with the examples.

how to deal with .mdb access files with python

Below is some code I wrote for another SO question.

It requires the 3rd-party pyodbc module.

This very simple example will connect to a table and export the results to a file.

Feel free to expand upon your question with any more specific needs you might have.

import csv, pyodbc

# set up some constants
MDB = 'c:/path/to/my.mdb'
DRV = '{Microsoft Access Driver (*.mdb)}'
PWD = 'pw'

# connect to db
con = pyodbc.connect('DRIVER={};DBQ={};PWD={}'.format(DRV,MDB,PWD))
cur = con.cursor()

# run a query and get the results 
SQL = 'SELECT * FROM mytable;' # your query goes here
rows = cur.execute(SQL).fetchall()
cur.close()
con.close()

# you could change the mode from 'w' to 'a' (append) for any subsequent queries
with open('mytable.csv', 'w') as fou:
    csv_writer = csv.writer(fou) # default field-delimiter is ","
    csv_writer.writerows(rows)

Read only method for querying an Access database (.mdb) file?

You can force the connection to read-only by appending ;ReadOnly=1 to your connection string. However, it certainly is possible for other users to update the database while you are reading it.

Check the permissions on the folder in which the .mdb file resides; all users of the database must have read/write access to the folder in order for concurrent multi-user access to work properly. See this answer for details.

MS Access library for python

Depending on what you want to do, pyodbc might be what you are looking for.

import pyodbc

def mdb_connect(db_file, user='admin', password = '', old_driver=False):
    driver_ver = '*.mdb'
    if not old_driver:
        driver_ver += ', *.accdb'

    odbc_conn_str = ('DRIVER={Microsoft Access Driver (%s)}'
                     ';DBQ=%s;UID=%s;PWD=%s' %
                     (driver_ver, db_file, user, password))

    return pyodbc.connect(odbc_conn_str)

conn = mdb_connect(r'''C:\x.mdb''')  # only absolute paths!

Note: you may download the freely-redistributable new-driver, if you don't have MSOffice installed.

---

Related Topics