batchupdate.py
author Radek Brich <radek.brich@devl.cz>
Wed, 26 Sep 2012 23:29:54 +0200
changeset 46 037410ef2b6b
parent 33 bd0beda49bcb
child 83 515fadd3d286
permissions -rwxr-xr-x
Fix query logging.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
32
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     1
#!/usr/bin/env python3.2
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     2
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     3
import time
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     4
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     5
from pgtoolkit import toolbase
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     6
from pgtoolkit.highlight import highlight
33
bd0beda49bcb PgManager: log connection name with queries. BatchUpdateTool: handle some possible exceptions and try reconnect to database.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
     7
from pgtoolkit.pgmanager import OperationalError
32
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     8
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     9
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    10
class BatchUpdateTool(toolbase.SimpleTool):
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    11
    def __init__(self):
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    12
        toolbase.SimpleTool.__init__(self, name='batchupdate', desc='Run an UPDATE query until it does not update any rows.')
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    13
        self.parser.add_argument('--query', dest='query', type=str, help='Query to run. It should be constructed using subquery with LIMIT.')
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    14
        self.parser.add_argument('--sleep', dest='sleep', default=0, type=int, help='Sleep SLEEP seconds between subsequent queries.')
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    15
        self.init()
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    16
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    17
    def main(self):
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    18
        # connect DB
33
bd0beda49bcb PgManager: log connection name with queries. BatchUpdateTool: handle some possible exceptions and try reconnect to database.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
    19
        rowcount = 1
bd0beda49bcb PgManager: log connection name with queries. BatchUpdateTool: handle some possible exceptions and try reconnect to database.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
    20
        while rowcount > 0:
bd0beda49bcb PgManager: log connection name with queries. BatchUpdateTool: handle some possible exceptions and try reconnect to database.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
    21
            print('query:', self.args.query)
bd0beda49bcb PgManager: log connection name with queries. BatchUpdateTool: handle some possible exceptions and try reconnect to database.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
    22
            with self.pgm.cursor('target') as curs:
bd0beda49bcb PgManager: log connection name with queries. BatchUpdateTool: handle some possible exceptions and try reconnect to database.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
    23
                try:
bd0beda49bcb PgManager: log connection name with queries. BatchUpdateTool: handle some possible exceptions and try reconnect to database.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
    24
                    curs.execute(self.args.query, [])
bd0beda49bcb PgManager: log connection name with queries. BatchUpdateTool: handle some possible exceptions and try reconnect to database.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
    25
                    rowcount = curs.rowcount
bd0beda49bcb PgManager: log connection name with queries. BatchUpdateTool: handle some possible exceptions and try reconnect to database.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
    26
                    print('updated', rowcount)
bd0beda49bcb PgManager: log connection name with queries. BatchUpdateTool: handle some possible exceptions and try reconnect to database.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
    27
                    curs.connection.commit()
bd0beda49bcb PgManager: log connection name with queries. BatchUpdateTool: handle some possible exceptions and try reconnect to database.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
    28
                except (OperationalError, SystemError) as e:
bd0beda49bcb PgManager: log connection name with queries. BatchUpdateTool: handle some possible exceptions and try reconnect to database.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
    29
                    print('Error:', str(e))
bd0beda49bcb PgManager: log connection name with queries. BatchUpdateTool: handle some possible exceptions and try reconnect to database.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
    30
            print('sleep %s seconds' % self.args.sleep)
bd0beda49bcb PgManager: log connection name with queries. BatchUpdateTool: handle some possible exceptions and try reconnect to database.
Radek Brich <radek.brich@devl.cz>
parents: 32
diff changeset
    31
            time.sleep(self.args.sleep)
32
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    32
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    33
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    34
tool = BatchUpdateTool()
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    35
tool.main()
d59c473c9ad7 Add batchupdate tool. PgBrowser: add constraints to list_column_usage().
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    36