1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
| 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
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
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()
|