tablediff.py
author Radek Brich <radek.brich@devl.cz>
Thu, 10 May 2012 08:42:21 +0200
changeset 34 98c7809af415
parent 14 a900bc629ecc
child 35 e7f79c4a27ce
permissions -rwxr-xr-x
Add PgDataCopy. Add TableCopyTool.Add SrcDstTablesTool class to toolbase, use in tablecopy, tablediff.

#!/usr/bin/env python3.2
#
# Print differencies between data in two tables of same schema.
#
# Requirements:
#  * First column of both tables must be numerical primary key.
#  * Destination table must contain all columns from source table.
#    Order is not important.
#

from pgtoolkit import toolbase, pgmanager, pgdatadiff
from pgtoolkit.highlight import *


class TableDiffTool(toolbase.SrcDstTablesTool):
    def __init__(self):
        toolbase.SrcDstTablesTool.__init__(self, name='tablediff', desc='Table diff.')
        
        self.parser.add_argument('--sql', action='store_true', help='Output is SQL script.')
        self.parser.add_argument('--rowcount', action='store_true', help='Compare number of rows.')
        
        self.init()

    def main(self):
        srcconn = self.pgm.get_conn('src')
        dstconn = self.pgm.get_conn('dst')
        
        dd = pgdatadiff.PgDataDiff(srcconn, dstconn)
        
        for srcschema, srctable, dstschema, dsttable in self.tables():
            print('Diff from [%s] %s.%s to [%s] %s.%s' % (
                self.args.src, srcschema, srctable,
                self.args.dst, dstschema, dsttable))
            
            if self.args.rowcount:
                with self.pgm.cursor('src') as curs:
                    curs.execute('''SELECT count(*) FROM "%s"."%s"''' % (srcschema, srctable))
                    srccount = curs.fetchone()[0]
                with self.pgm.cursor('dst') as curs:
                    curs.execute('''SELECT count(*) FROM "%s"."%s"''' % (dstschema, dsttable))
                    dstcount = curs.fetchone()[0]
                if srccount != dstcount:
                    print(highlight(1, BOLD | YELLOW), "Row count differs: src=%s dst=%s" % (srccount, dstcount), highlight(0), sep='')
                continue
            
            dd.settable1(srctable, srcschema)
            dd.settable2(dsttable, dstschema)
            
            if self.args.sql:
                dd.print_patch()
            else:
                dd.print_diff()


tool = TableDiffTool()
tool.main()