| author | Radek Brich <radek.brich@devl.cz> |
| Wed, 28 Mar 2012 17:25:18 +0200 | |
| changeset 33 | bd0beda49bcb |
| parent 32 | d59c473c9ad7 |
| child 83 | 515fadd3d286 |
| permissions | -rwxr-xr-x |
|
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 |