python 操作access数据库

说明：建立两个连接是因为，只使用一个连接时会出现以下错误： [24000] [Microsoft][ODBC Microsoft Access Driver]Invalid cursor state (43) (SQLExecDirectW)

例程1

以下是测试代码：

import pyodbc import random import threading import time DBfile = r"C:\Users\eagle\Desktop\Database21.accdb" # 数据库文件 conn = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + DBfile + ";Uid=;Pwd=;") conn1 = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + DBfile + ";Uid=;Pwd=;") cursor = conn.cursor() cursor1 = conn1.cursor() TABLE = 'xuesheng' SQL = "delete * from %s;" % TABLE cursor.execute(SQL) cursor.commit() def write(): name_base = 'zhang' for i in range(0,100000): ID = i name = name_base + str(i) age = random.randint(20,26) sex = 'fmale' if (age % 2) == 0 else 'male' chinese = random.randint(0,100) eng = random.randint(0,100) math = random.randint(0,100) SQL = "insert into %s values(%d, '%s', %d, '%s', %d, %d, %d) ;" \ % (TABLE, ID, name, age, sex, chinese, eng, math) try: cursor1.execute(SQL) cursor1.commit() #print(SQL) except Exception as e: print('write ERROR: %s' %e) def read(): for i in range(0,10): SQL = "SELECT * from %s;" % TABLE try: cursor.execute(SQL) #for row in cursor.execute(SQL): #print (row) except Exception as e: print('read ERROR:%s' %e) time.sleep(0.1) w = threading.Thread(target=write,args=()) w.setDaemon(True) r = threading.Thread(target=read,args=()) r.setDaemon(True) w.start() r.start() w.join() r.join() SQL = "select count(*) from %s;" % TABLE for row in cursor.execute(SQL): print(row) cursor.commit() cursor.close() conn.close() conn1.close()

例程2

该例程完成access数据库的复制

import pyodbc import random import threading import time import getopt import sys import logging import re logging.basicConfig(level=logging.DEBUG, format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s', datefmt='%a, %d %b %Y %H:%M:%S', filename='access_copy.log', filemode='a') SourceDatabase = None SourceTable = None DestDatabase = None DestTable = None # connection and cursor conn_s = None cursor_s = None conn_d = None cursor_d = None def write(row): error = 0 try: SQL = "insert into %s values %s" \ %(DestTable, row) logging.info(SQL) cursor_d.execute(SQL) cursor_d.commit() except Exception as e: if re.search('duplicate data', e): pass else: logging.error("write to %s error: %s" %(DestTable, e)) error = 1 finally: return error def read(line): try: SQL = "SELECT top %d * from %s;" % (line, SourceTable) logging.info(SQL) rows = cursor_s.execute(SQL).fetchall() return rows except Exception as e: logging.error('read from %s error: %s'%(SourceTable, e)) return None #count the dest table,decide the place begin to copy def count_s(): try: SQL = "SELECT count(*) from %s;" % SourceTable lines = cursor_s.execute(SQL).fetchone() logging.info('%s %s count is %d' %(SourceDatabase, SourceTable, lines[0])) return lines except Exception as e: logging.error(e) return None def count_d(): try: SQL = "SELECT count(*) from %s;" % DestTable lines = cursor_d.execute(SQL).fetchone() logging.info('%s %s count is %d' %(DestDatabase, DestTable, lines[0])) return lines except Exception as e: logging.error(e) return None def databaseCopy(): global conn_s global conn_d global cursor_s global cursor_d try: conn_s = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + SourceDatabase + ";Uid=;Pwd=;") conn_d = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + DestDatabase + ";Uid=;Pwd=;") cursor_s = conn_s.cursor() cursor_d = conn_d.cursor() lines_s = count_s() if(lines_s == None): return lines_d = count_d() if(lines_d == None): return rows = read(lines_s[0]) if(rows == None): return if (lines_s == lines_d): logging.info('source database %s table %s not change, no need to copy' %(SourceDatabase ,SourceTable)) else: logging.info('rows: %d' % len(rows)) for i in range(0, lines_s[0]-1): error = write(rows[i]) if error: logging.info('rows:%d %s' %(i, rows[i])) break except Exception as e: logging.error(e) def main(): global SourceDatabase global SourceTable global DestDatabase global DestTable try: opts, args = getopt.getopt(sys.argv[1:],"h",["help","sd=","st=","dd=", "dt="]) except getopt.GetoptError as e: print(e) help() sys.exit(2) for opt, arg in opts: if opt in ('-h', '--help'): help() sys.exit() elif opt == '--sd': SourceDatabase = arg elif opt == '--st': SourceTable = arg elif opt == '--dd': DestDatabase = arg elif opt == '--dt': DestTable = arg else : print('error %s %s' %(opt, arg) ) help() sys.exit(2) if (SourceDatabase and SourceTable and DestDatabase and DestTable): databaseCopy() else: print('some empty %s %s %s %s' % (SourceDatabase, SourceTable, DestDatabase, DestTable)) help() sys.exit(2) count_s() count_d() conn_s.close() conn_d.close() def help(): print('---------------------------------') print('usage:') print('help: %s -h' % sys.argv[0]) print('%s --sd=<SourceDatabase> --st=<SourceTable> --dd=<DestDatabase> --dt=<DestTable>' % sys.argv[0]) if __name__ == '__main__': main()

使用方法： 可以将下面的代码保存为bat文件，自行替换 --sd --st --dd --dt 的内容。

python access_copy.py --sd="\\192.168.1.112\Users\eagle\Desktop\Database31.accdb" --st=xuesheng --dd="C:\Users\eagle\Desktop\Database31.accdb" --dt=xuesheng pause

说明：

access数据库的复制支持通过共享文件夹的方式读取远程的数据库。

access 数据库的问题：

假设程序A对A数据库执行写入操作，程序B读取A数据库中的数据写入B库。

如果执行select*那么程序会一直写入，直到A库的写操作完成。如果想写入程序执行时A数据库的数据的条数，需加上top限制，但是不能保证读出数据的次序。加上 order by 后会死等，直到程序A的写操作完成，B程序才开始执行写操作。