python 操作access数据库

使用pypyodbc库，【官方说明文档】    【使用介绍】

 

#!/usr/bin/env python
# -*- conding: utf-8 -*-

import pypyodbc
if __name__=="__main__":

    str = 'Driver={Microsoft Access Driver (*.mdb)};DBQ=E:\\123.mdb'
    conn = pypyodbc.win_connect_mdb(str)
    #conn = pypyodbc.connect(str)
    cur = conn.cursor()
    cur.execute("SELECT * FROM catalog")
    for row in cur.fetchall():
        for field in row:
            print field,
        print ''
    conn.commit()
    cur.close()
    conn.close()

 

注意这里使用的是pypyodbc.win_connect_mdb，如果使用pypyodbc.connect则会出现：

Traceback (most recent call last):
  File "E:\odbc.py", line 6, in <module>
    conn = pypyodbc.connect(str)
  File "E:\pypyodbc.py", line 2434, in __init__
    self.connect(connectString, autocommit, ansi, timeout, unicode_results, read
only)
  File "E:\pypyodbc.py", line 2483, in connect
    check_success(self, ret)
  File "E:\pypyodbc.py", line 988, in check_success
    ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)
  File "E:\pypyodbc.py", line 964, in ctrl_err
    raise Error(state,err_text)
pypyodbc.Error: (u'IM002', u'[IM002] [Microsoft][ODBC \u9a71\u52a8\u7a0b\u5e8f\u
7ba1\u7406\u5668] \u672a\u53d1\u73b0\u6570\u636e\u6e90\u540d\u79f0\u5e76\u4e14\u
672a\u6307\u5b9a\u9ed8\u8ba4\u9a71\u52a8\u7a0b\u5e8f')

 

如果出现：

C:\Python27\python.exe E:/odbc.py
Traceback (most recent call last):
  File "E:/odbc.py", line 5, in <module>
    conn = pypyodbc.win_connect_mdb(str)
  File "E:\pypyodbc.py", line 2744, in win_connect_mdb
    return connect('Driver={'+driver_name+"};DBQ="+mdb_path, unicode_results = use_unicode, readonly = False)
  File "E:\pypyodbc.py", line 2434, in __init__
    self.connect(connectString, autocommit, ansi, timeout, unicode_results, readonly)
  File "E:\pypyodbc.py", line 2483, in connect
    check_success(self, ret)
  File "E:\pypyodbc.py", line 988, in check_success
    ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)
  File "E:\pypyodbc.py", line 964, in ctrl_err
    raise Error(state,err_text)
pypyodbc.Error: (u'HY000', u'[HY000] [Microsoft][ODBC Microsoft Access Driver]\u5e38\u89c1\u9519\u8bef \u65e0\u6cd5\u6253\u5f00\u6ce8\u518c\u8868\u9879\u201cTemporary (volatile) Ace DSN for process 0x19d8 Thread 0x17f0 DBC 0x36d00b8                                                              Jet\u201d\u3002')

则可能是此文件已加密

import pypyodbc,socket
if __name__=="__main__":
    s = socket.socket()
    str = 'Driver={Microsoft Access Driver (*.mdb)};PWD=123;DBQ=E:\\123.mdb'
    conn = pypyodbc.win_connect_mdb(str)
    #print conn.dbc_h

    #rec = conn.ConnectByDSN('E:\\123.mdb',"yinsua",'123')
    #conn.ConnectByDSN("ORACLE-PYTHON","yinsua",123)
    cur = conn.cursor()
    cur.execute("SELECT * FROM catalog")
    for row in cur.fetchall():
        for field in row:
         print   field,
        print   ''
    conn.commit()
    cur.close()
    conn.close()

只需在DBQ前面加上这么一段即可：PWD=YourPWD