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
|
"""
Compare two tables
"""
from sqlplain import util
TEMPL = '''
SELECT %(csfields)s FROM %(table1)s AS a
INNER JOIN %(table2)s AS b
ON %(condition)s
WHERE %(clause)s
'''
SUB = '''
SELECT %(csfields)s FROM %(table1)s AS a
LEFT OUTER JOIN %(table2)s AS b
ON %(condition)s
WHERE %(clause)s
'''
def sub(db, table1, table2, kfields=(), dfields=()):
"""
Returns the kfields and dfields in table1 but not in table2.
If kfields and/or dfields are not given, they are reflected
from the database. table1 and table2 must have the same primary key.
"""
if not kfields:
kfields = k1 = util.get_kfields(db, table1)
k2 = util.get_kfields(db, table2)
assert k1 == k2, '%s and %s have different primary key!' % (
table1, table2)
csfields = ', '.join('a.%s' % k for k in (kfields + dfields))
condition = ' AND '.join('a.%s=b.%s' % (k, k) for k in kfields)
clause = ' AND '.join('b.%s IS NULL' % k for k in kields)
return db.execute(SUB % locals())
def compare(db, table1, table2, kfields=(), dfields=()):
"""
Compare table1 and table2; returns the rows in table1 and not
in table2, the rows in table2 and not in table1, and the rows
in both tables such that the dfields differ.
"""
if not kfields:
kfields = k1 = util.get_kfields(db, table1)
k2 = util.get_kfields(db, table2)
assert k1 == k2, '%s and %s have different primary key!' % (
table1, table2)
sub12 = sub(db, table1, table2, kfields)
sub21 = sub(db, table2, table1, kfields)
csfields = ', '.join('a.%s' % k for k in kfields)
condition = ' AND '.join('a.%s=b.%s' % (k, k) for k in kfields)
if not dfields:
d1 = util.get_dfields(db, table1)
d2 = util.get_dfields(db, table2)
assert d1 == d2, '%s and %s have different data fields!' % (
table1, table2)
dfields = d1
clause = ' OR '.join('a.%s<>b.%s' % (d, d) for d in dfields)
return sub12, sub21, db.execute(TEMPL % locals())
if __name__ == '__main__':
from sqlplain import connect
db = connect('dbserver2')
#compare(db, 'riskfactor', 'riskfactor2')
print sub(db, 'riskfactor', 'riskfactor2')
|
