MS Access library for python

Question

Is there a library for using MS Access database in python? The win32 module is not as easy as the MySQL library. Is there a simpler way to use MS Access with Python?

score 43 · Accepted Answer · edited May 27 '19 at 01:10

43

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.

edited May 27 '19 at 01:10

ankostis

8,579
3
47
61

answered Jun 26 '09 at 06:13

stephan

10,104
1
51
64

1

@Cristian Ciupitu: thanks for adding the sample code – stephan Jan 16 '13 at 15:58
3

Right direction for sure. The only edit I had to do was `*.mdb, *.accdb` instead of `*.mdb` – demongolem Dec 28 '15 at 20:26
Just to say that I have to change office and python from 64 to 32 bits to make it work – Alberto Perez Dec 21 '18 at 09:45

score 13 · Answer 2 · answered Jun 26 '09 at 06:27

I don't think win32 is hard. Try use its odbc module. Example of code working with ODBC and PostgreSQL database:

import odbc

def get_pg_ver(db_alias):
    connection = odbc.odbc(db_alias)
    try:
        cursor = connection.cursor()
        cursor.execute('SELECT version()')
        for row in cursor.fetchall():
            print row[0]
    finally:
        connection.close()

get_pg_ver('odbc_name/user/passwd')

This is very similar for every db driver I used in Python and Jython (I work with PostgreSQL, Oracle and Informix).

Agreed: 'odbc' works fine with M$ Access. – mechanical_meat Jun 26 '09 at 06:35 — mechanical_meat, Jun 26 '09 at 06:35

score 8 · Answer 3 · answered Mar 11 '13 at 04:07

You can use pypyodbc to easily create an empty Access MDB file on win32 platform, and also compact existing Access MDB files.

It can be as easy as:

import pypyodbc
pypyodbc.win_create_mdb( "D:\\Your_MDB_file_path.mdb" )

More over, as an dbi 2.0 ODBC library, pypyodbc is highly compatible with pyodbc, you can do SQL database queries like SELECT, INSERT, UPDATE with the library.

Here is the full Tutorial about pypyodbc's Access support.

Disclaimer: I'm the developer of pypyodbc.

score 4 · Answer 4 · answered Sep 30 '11 at 15:22

I had some recent success using pywin32's adodbapi module.

The following snippet was taken from this website:

import adodbapi

database = "db1.mdb"
constr = 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=%s'  % database
tablename = "address"

# connect to the database
conn = adodbapi.connect(constr)

# create a cursor
cur = conn.cursor()

# extract all the data
sql = "select * from %s" % tablename
cur.execute(sql)

# show the result
result = cur.fetchall()
for item in result:
    print item

# close the cursor and connection
cur.close()
conn.close()

MS Access library for python

4 Answers4

Linked

Related