說明：建立兩個連接是因為，只使用一個連接時會出現以下錯誤：
[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=
              
                 --st=
                
                   --dd=
                  
                     --dt=
                    
                      ' % 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庫。

1. 如果執行select*那么程序會一直寫入，直到A庫的寫操作完成。
2. 如果想寫入程序執行時A數據庫的數據的條數，需加上top限制，但是不能保證讀出數據的次序。
3. 加上 order by 后會死等，直到程序A的寫操作完成，B程序才開始執行寫操作。