root/livinglogic.python.xist/src/ll/orasql/scripts/oradelete.py @ 4447:5ff2593c0dd8

Revision 4447:5ff2593c0dd8, 5.3 KB (checked in by Walter Doerwald <walter@…>, 8 years ago)

Docstring for oradelete. Fix typos.

Line 
1#!/usr/bin/env python
2# -*- coding: utf-8 -*-
3
4## Copyright 2005-2011 by LivingLogic AG, Bayreuth/Germany.
5## Copyright 2005-2011 by Walter Dörwald
6##
7## All Rights Reserved
8##
9## See orasql/__init__.py for the license
10
11
12"""
13Purpose
14-------
15
16``oradelete`` prints the delete statements for all tables in an Oracle database
17schema in the correct order (i.e. records will be deleted so that no errors
18happen during script execution). ``oradelete`` can also be used to actually
19make all tables empty.
20
21
22Options
23-------
24
25``oradelete`` supports the following options:
26
27    ``connectstring``
28        An Oracle connectstring.
29
30    ``-v``, ``--verbose`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
31        Produces output (on stderr) while to datebase is read or written.
32
33    ``-c``, ``--color`` : ``yes``, ``no`` or ``auto``
34        Should the output (when the ``-v`` option is used) be colored. If ``auto``
35        is specified (the default) then the output is colored if stderr is a
36        terminal.
37
38    ``-s``, ``--sequences`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
39        Should sequences be reset to their initial values?
40
41    ``-x``, ``--execute`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
42        When the ``-x`` argument is given the SQL script isn't printed on stdout,
43        but is executed directly. Be careful with this: You *will* have empty
44        tables after ``oradelete -x``.
45
46    ``-i``, ``--ignore`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
47        If given, errors occuring while the database is read or written will be
48        ignored.
49
50    ``-e``, ``--encoding`` : encoding
51        The encoding of the output (if ``-x`` is not given; default is ``utf-8``).
52
53    ``-t``, ``--truncate`` : ``false``, ``no``, ``0``, ``true``, ``yes`` or ``1``
54        If given the script uses the ``TRUNCATE`` command instead of the ``DELETE``
55        command.
56"""
57
58
59import sys, os, argparse
60
61from ll import misc, astyle, orasql
62
63
64__docformat__ = "reStructuredText"
65
66
67s4warning = astyle.Style.fromenv("LL_ORASQL_REPRANSI_WARNING", "red:black")
68s4error = astyle.Style.fromenv("LL_ORASQL_REPRANSI_ERROR", "red:black")
69s4connectstring = astyle.Style.fromenv("LL_ORASQL_REPRANSI_CONNECTSTRING", "yellow:black")
70s4object = astyle.Style.fromenv("LL_ORASQL_REPRANSI_OBJECT", "green:black")
71
72
73def main(args=None):
74    p = argparse.ArgumentParser(description="Print (or execute) SQL for deleting all records from all tables in an Oracle database schema")
75    p.add_argument("connectstring", help="Oracle connect string")
76    p.add_argument("-v", "--verbose", dest="verbose", help="Give a progress report? (default %(default)s)", default=False, action=misc.FlagAction)
77    p.add_argument("-c", "--color", dest="color", help="Color output (default %(default)s)", default="auto", choices=("yes", "no", "auto"))
78    p.add_argument("-s", "--sequences", dest="sequences", help="Reset sequences? (default %(default)s)", default=False, action=misc.FlagAction)
79    p.add_argument("-x", "--execute", dest="execute", action=misc.FlagAction, help="immediately execute the commands instead of printing them? (default %(default)s)")
80    p.add_argument("-i", "--ignore", dest="ignore", help="Ignore errors? (default %(default)s)", default=False, action=misc.FlagAction)
81    p.add_argument("-e", "--encoding", dest="encoding", help="Encoding for output (default %(default)s)", default="utf-8")
82    p.add_argument("-t", "--truncate", dest="truncate", help="Truncate tables (instead of deleting)? (default %(default)s)", default=False, action=misc.FlagAction)
83
84    args = p.parse_args(args)
85
86    if args.color == "yes":
87        color = True
88    elif args.color == "no":
89        color = False
90    else:
91        color = None
92
93    stdout = astyle.Stream(sys.stdout, color)
94    stderr = astyle.Stream(sys.stderr, color)
95
96    connection = orasql.connect(args.connectstring)
97    cursor = connection.cursor()
98
99    cs = s4connectstring(connection.connectstring())
100
101    for (i, obj) in enumerate(connection.itertables(schema="user", mode="drop")):
102        # Progress report
103        if args.verbose:
104            msg = "truncating" if args.truncate else "deleting from"
105            msg = astyle.style_default("oradelete.py: ", cs, ": {} #{} ".format(msg, i+1), s4object(str(obj)))
106            stderr.writeln(msg)
107
108        # Print or execute SQL
109        if args.execute:
110            try:
111                fmt = u"truncate table {}" if args.truncate else u"delete from {}"
112                cursor.execute(fmt.format(obj.name))
113            except orasql.DatabaseError, exc:
114                if not args.ignore or "ORA-01013" in str(exc):
115                    raise
116                stderr.writeln("oradelete.py: ", s4error("{}: {}".format(exc.__class__, str(exc).strip())))
117        else:
118            if args.truncate:
119                sql = u"truncate table {};\n".format(obj.name)
120            else:
121                sql = u"delete from {};\n".format(obj.name)
122            stdout.write(sql.encode(args.encoding))
123    if not args.truncate:
124        connection.commit()
125
126    if args.sequences:
127        for (i, obj) in enumerate(connection.itersequences(schema="user")):
128            # Progress report
129            if args.verbose:
130                msg = astyle.style_default("oradelete.py: ", cs, ": recreating #{} ".format(i+1), s4object(str(obj)))
131                stderr.writeln(msg)
132
133            # Print or execute SQL
134            if args.execute:
135                try:
136                    sql = obj.createddl(term=False)
137                    cursor.execute(obj.dropddl(term=False))
138                    cursor.execute(sql)
139                except orasql.DatabaseError, exc:
140                    if not args.ignore or "ORA-01013" in str(exc):
141                        raise
142                    stderr.writeln("oradelete.py: ", s4error("{}: {}".format(exc.__class__, str(exc).strip())))
143            else:
144                sql = obj.dropddl(term=True) + obj.createddl(term=True)
145                stdout.write(sql.encode(args.encoding))
146
147if __name__ == "__main__":
148    sys.exit(main())
Note: See TracBrowser for help on using the browser.