1 #!/usr/bin/env python3.2 |
1 #!/usr/bin/env python3.2 |
2 # |
2 # |
3 # Print differencies between data in two tables of same schema. |
3 # Print differencies between data in two tables of same schema. |
4 # |
4 # |
5 # Requirements: |
5 # Requirements: |
6 # * First column of both tables must be numerical primary key. |
6 # * First column of both tables must be numerical primary key. |
7 # * Destination table must contain all columns from source table. |
7 # * Destination table must contain all columns from source table. |
8 # Order is not important. |
8 # Order is not important. |
9 # |
9 # |
10 |
10 |
11 from pgtools import pgmanager, pgbrowser, pgdatadiff |
11 from tools import pgmanager, pgbrowser, pgdatadiff, toolbase |
12 from toolbase import SrcDstTool |
|
13 |
12 |
14 |
13 |
15 class TableDiffTool(SrcDstTool): |
14 class TableDiffTool(toolbase.SrcDstTool): |
16 def __init__(self): |
15 def __init__(self): |
17 SrcDstTool.__init__(self, name='tablediff', desc='Table diff.') |
16 toolbase.SrcDstTool.__init__(self, name='tablediff', desc='Table diff.') |
18 |
17 |
19 self.parser.add_argument('table', metavar='table', type=str, help='Table name') |
18 self.parser.add_argument('table', metavar='table', type=str, help='Table name') |
|
19 self.parser.add_argument('-s', dest='schema', metavar='schema', type=str, default='public', help='Schema name.') |
|
20 self.parser.add_argument('--sql', action='store_true', help='Output is SQL script.') |
20 |
21 |
21 self.init() |
22 self.init() |
22 |
23 |
23 def main(self): |
24 def main(self): |
24 if '.' in self.args.table: |
25 table = self.args.table |
25 schema, table = self.args.table.split('.', 1) |
26 schema = self.args.schema |
|
27 |
|
28 dd = pgdatadiff.PgDataDiff(self.pgm.get_conn('src'), self.pgm.get_conn('dst')) |
|
29 dd.settable1(table, schema) |
|
30 dd.settable2(table, schema) |
|
31 |
|
32 if self.args.sql: |
|
33 dd.print_patch() |
26 else: |
34 else: |
27 table = self.args.table |
35 dd.print_diff() |
28 |
|
29 srcbrowser = pgbrowser.PgBrowser(self.pgm.get_conn('src')) |
|
30 |
|
31 columns = srcbrowser.list_columns(schema=schema, table=table) |
|
32 columns_sel = ', '.join(['"' + x['name'] + '"' for x in columns]) |
|
33 |
|
34 table_fullname = '"' + schema + '"."'+ table + '"'; |
|
35 query = 'SELECT ' + columns_sel + ' FROM ' + table_fullname + ' ORDER BY 1;' |
|
36 |
|
37 with self.pgm.cursor('src') as curs: |
|
38 curs.execute(query) |
|
39 src_rows = curs.fetchall() |
|
40 |
|
41 with self.pgm.cursor('dst') as curs: |
|
42 curs.execute(query) |
|
43 dst_rows = curs.fetchall() |
|
44 |
|
45 pgdd = pgdatadiff.PgDataDiff(table_fullname, |
|
46 src_rows, dst_rows, [x['name'] for x in columns]) |
|
47 #pgdd.print_diff() |
|
48 pgdd.print_patch() |
|
49 |
36 |
50 |
37 |
51 tool = TableDiffTool() |
38 tool = TableDiffTool() |
52 tool.main() |
39 tool.main() |
|
40 |