說明:建立兩個連接是因為,只使用一個連接時會出現以下錯誤:
[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庫。
- 如果執行select*那么程序會一直寫入,直到A庫的寫操作完成。
- 如果想寫入程序執行時A數據庫的數據的條數,需加上top限制,但是不能保證讀出數據的次序。
- 加上 order by 后會死等,直到程序A的寫操作完成,B程序才開始執行寫操作。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
