MS Access library for python [duplicate]

问题:

This question already has an answer here:

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

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?

回答1:

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

import pyodbc
db_file = r'''C:\x.mdb'''
user = 'admin'
password = ''

odbc_conn_str = 'DRIVER={Microsoft Access Driver (*.mdb)};DBQ=%s;UID=%s;PWD=%s' %\
                (db_file, user, password)
# Or, for newer versions of the Access drivers:
odbc_conn_str = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s;UID=%s;PWD=%s' %\
                (db_file, user, password)

conn = pyodbc.connect(odbc_conn_str)

回答2:

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).

回答3:

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.

回答4:

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()