Mercurial > sqlpython
annotate sqlpython/sqlpython.py @ 518:69997c324eb9 1.7.2
1.7.2
| author | Catherine Devlin <catherine.devlin@gmail.com> |
|---|---|
| date | Tue, 09 Nov 2010 05:41:08 -0500 |
| parents | b7782506b5f3 |
| children | 506156a9915e |
| rev | line source |
|---|---|
| 189 | 1 # |
| 368 | 2 # SqlPython V1.6.7 |
| 189 | 3 # Author: Luca.Canali@cern.ch, Apr 2006 |
| 380 | 4 # Rev 2-Sep-09 |
| 368 | 5 |
| 189 | 6 # A python module to reproduce Oracle's command line 'sqlplus-like' within python |
| 7 # Intended to allow easy customizations and extentions | |
| 8 # Best used with the companion modules sqlpyPlus and mysqlpy | |
| 9 # See also http://twiki.cern.ch/twiki/bin/view/PSSGroup/SqlPython | |
| 10 | |
| 415 | 11 import cmd2,getpass,binascii,re,os,platform |
|
445
b34ea206c7d1
debug bind var dependence on arg.parsed.expanded
catherine@Drou
parents:
444
diff
changeset
|
12 import pyparsing, connections |
| 518 | 13 __version__ = '1.7.2' |
| 415 | 14 try: |
| 15 import cx_Oracle | |
| 16 except ImportError: | |
| 17 cx_Oracle = None | |
| 18 try: | |
| 19 import psycopg2 | |
| 20 except ImportError: | |
| 21 psycopg2 = None | |
| 189 | 22 |
|
339
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
23 class Parser(object): |
|
409
5b88ce5f31ff
ugh, trying to separate -- comments from --flags
catherine@DellZilla
parents:
407
diff
changeset
|
24 comment_def = "--" + pyparsing.NotAny('-' + pyparsing.CaselessKeyword('begin')) + pyparsing.ZeroOrMore(pyparsing.CharsNotIn("\n")) |
|
339
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
25 def __init__(self, scanner, retainSeparator=True): |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
26 self.scanner = scanner |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
27 self.scanner.ignore(pyparsing.sglQuotedString) |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
28 self.scanner.ignore(pyparsing.dblQuotedString) |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
29 self.scanner.ignore(self.comment_def) |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
30 self.scanner.ignore(pyparsing.cStyleComment) |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
31 self.retainSeparator = retainSeparator |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
32 def separate(self, txt): |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
33 itms = [] |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
34 for (sqlcommand, start, end) in self.scanner.scanString(txt): |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
35 if sqlcommand: |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
36 if type(sqlcommand[0]) == pyparsing.ParseResults: |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
37 if self.retainSeparator: |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
38 itms.append("".join(sqlcommand[0])) |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
39 else: |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
40 itms.append(sqlcommand[0][0]) |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
41 else: |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
42 if sqlcommand[0]: |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
43 itms.append(sqlcommand[0]) |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
44 return itms |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
45 |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
46 |
| 189 | 47 class sqlpython(cmd2.Cmd): |
| 48 '''A python module to reproduce Oracle's command line with focus on customization and extention''' | |
| 49 | |
| 50 def __init__(self): | |
| 51 cmd2.Cmd.__init__(self) | |
| 428 | 52 self.no_instance() |
| 189 | 53 self.maxfetch = 1000 |
| 54 self.terminator = ';' | |
| 55 self.timeout = 30 | |
| 56 self.commit_on_exit = True | |
| 428 | 57 self.instances = {} |
| 260 | 58 |
| 428 | 59 def no_instance(self): |
| 261 | 60 self.prompt = 'SQL.No_Connection> ' |
| 260 | 61 self.curs = None |
| 429 | 62 self.current_instance = None |
| 428 | 63 self.instance_number = None |
| 189 | 64 |
| 428 | 65 def make_instance_current(self, instance_number): |
| 66 db_instance = self.instances[instance_number] | |
| 429 | 67 self.prompt = db_instance.prompt |
| 68 self.rdbms = db_instance.rdbms | |
| 428 | 69 self.instance_number = instance_number |
| 429 | 70 self.curs = db_instance.connection.cursor() |
| 428 | 71 self.current_instance = db_instance |
|
317
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
72 |
| 428 | 73 def list_instances(self): |
|
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
74 self.stdout.write('Existing connections:\n') |
| 428 | 75 self.stdout.write('\n'.join('%s (%s)' % (v.prompt, v.rdbms) |
| 76 for (k,v) in sorted(self.instances.items())) + '\n') | |
|
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
77 |
| 260 | 78 def disconnect(self, arg): |
| 79 try: | |
| 428 | 80 instance_number = int(arg) |
| 81 instance = self.instances[instance_number] | |
| 261 | 82 except (ValueError, KeyError): |
| 428 | 83 self.list_instances() |
| 260 | 84 return |
| 85 if self.commit_on_exit: | |
|
484
b7782506b5f3
don't get stuck if error during exit-commit
Catherine Devlin <catherine.devlin@gmail.com>
parents:
480
diff
changeset
|
86 try: |
|
b7782506b5f3
don't get stuck if error during exit-commit
Catherine Devlin <catherine.devlin@gmail.com>
parents:
480
diff
changeset
|
87 instance.connection.commit() |
|
b7782506b5f3
don't get stuck if error during exit-commit
Catherine Devlin <catherine.devlin@gmail.com>
parents:
480
diff
changeset
|
88 except Exception, e: |
|
b7782506b5f3
don't get stuck if error during exit-commit
Catherine Devlin <catherine.devlin@gmail.com>
parents:
480
diff
changeset
|
89 self.perror('Error while committing:') |
|
b7782506b5f3
don't get stuck if error during exit-commit
Catherine Devlin <catherine.devlin@gmail.com>
parents:
480
diff
changeset
|
90 self.perror(str(e)) |
| 428 | 91 self.instances.pop(instance_number) |
| 92 if instance_number == self.instance_number: | |
| 93 self.no_instance() | |
| 261 | 94 |
| 95 def closeall(self): | |
| 428 | 96 for instance_number in self.instances.keys(): |
| 97 self.disconnect(instance_number) | |
| 261 | 98 self.curs = None |
| 428 | 99 self.no_instance() |
| 261 | 100 |
| 407 | 101 legal_sql_word = pyparsing.Word(pyparsing.alphanums + '_$#') |
| 102 | |
| 429 | 103 def successfully_connect_to_number(self, arg): |
| 104 try: | |
| 105 instance_number = int(arg) | |
| 106 except ValueError: | |
| 107 return False | |
| 108 try: | |
| 109 self.make_instance_current(instance_number) | |
| 110 except IndexError: | |
| 111 self.list_instances() | |
| 112 return False | |
| 113 if (self.rdbms == 'oracle') and self.serveroutput: | |
| 114 self.curs.callproc('dbms_output.enable', []) | |
| 115 return True | |
|
451
fd86d22fe00d
mostly compatible with cmd2 0.6 - minor transcript test problem
catherine@Drou
parents:
445
diff
changeset
|
116 |
|
fd86d22fe00d
mostly compatible with cmd2 0.6 - minor transcript test problem
catherine@Drou
parents:
445
diff
changeset
|
117 @cmd2.options([cmd2.make_option('-a', '--add', action='store_true', |
| 425 | 118 help='add connection (keep current connection)'), |
|
451
fd86d22fe00d
mostly compatible with cmd2 0.6 - minor transcript test problem
catherine@Drou
parents:
445
diff
changeset
|
119 cmd2.make_option('-c', '--close', action='store_true', |
| 425 | 120 help='close connection {N} (or current)'), |
|
451
fd86d22fe00d
mostly compatible with cmd2 0.6 - minor transcript test problem
catherine@Drou
parents:
445
diff
changeset
|
121 cmd2.make_option('-C', '--closeall', action='store_true', |
| 425 | 122 help='close all connections'), |
| 518 | 123 cmd2.make_option('--postgresql', action='store_true', help='Connect to postgreSQL: `connect --postgresql [DBNAME [USERNAME]]`'), |
| 462 | 124 cmd2.make_option('--postgres', action='store_true', help='Connect to postgreSQL: `connect --postgres [DBNAME [USERNAME]]`'), |
| 425 | 125 cmd2.make_option('--oracle', action='store_true', help='Connect to an Oracle database'), |
|
451
fd86d22fe00d
mostly compatible with cmd2 0.6 - minor transcript test problem
catherine@Drou
parents:
445
diff
changeset
|
126 cmd2.make_option('--mysql', action='store_true', help='Connect to a MySQL database'), |
|
fd86d22fe00d
mostly compatible with cmd2 0.6 - minor transcript test problem
catherine@Drou
parents:
445
diff
changeset
|
127 cmd2.make_option('-H', '--hostname', type='string', |
|
fd86d22fe00d
mostly compatible with cmd2 0.6 - minor transcript test problem
catherine@Drou
parents:
445
diff
changeset
|
128 help='Machine where database is hosted'), |
|
fd86d22fe00d
mostly compatible with cmd2 0.6 - minor transcript test problem
catherine@Drou
parents:
445
diff
changeset
|
129 cmd2.make_option('-p', '--port', type='int', |
|
fd86d22fe00d
mostly compatible with cmd2 0.6 - minor transcript test problem
catherine@Drou
parents:
445
diff
changeset
|
130 help='Port to connect to'), |
|
fd86d22fe00d
mostly compatible with cmd2 0.6 - minor transcript test problem
catherine@Drou
parents:
445
diff
changeset
|
131 cmd2.make_option('--password', type='string', |
|
fd86d22fe00d
mostly compatible with cmd2 0.6 - minor transcript test problem
catherine@Drou
parents:
445
diff
changeset
|
132 help='Password'), |
|
fd86d22fe00d
mostly compatible with cmd2 0.6 - minor transcript test problem
catherine@Drou
parents:
445
diff
changeset
|
133 cmd2.make_option('-d', '--database', type='string', |
| 425 | 134 help='Database name to connect to'), |
|
451
fd86d22fe00d
mostly compatible with cmd2 0.6 - minor transcript test problem
catherine@Drou
parents:
445
diff
changeset
|
135 cmd2.make_option('-U', '--username', type='string', |
|
fd86d22fe00d
mostly compatible with cmd2 0.6 - minor transcript test problem
catherine@Drou
parents:
445
diff
changeset
|
136 help='Database user name to connect as')]) |
| 426 | 137 def do_connect(self, arg, opts): |
| 425 | 138 |
| 469 | 139 '''Opens the DB connection. Some sample valid connection strings: |
| 140 | |
| 141 connect oracle://user:password@SID | |
| 142 connect postgres://user:password@hostname/dbname | |
| 143 connect user/password@SID (Oracle is the default RDBMS target) | |
| 144 connect --postgres --hostname=hostname dbname username | |
| 145 connect --mysql dbname username''' | |
| 518 | 146 opts.postgres = opts.postgres or opts.postgresql |
| 425 | 147 if opts.closeall: |
| 148 self.closeall() | |
| 149 return | |
| 150 if opts.close: | |
| 151 if not arg: | |
| 428 | 152 arg = self.instance_number |
| 425 | 153 self.disconnect(arg) |
| 154 return | |
|
438
39a664a87c51
excluded --options from --comments with .NotAny
catherine@bothari
parents:
436
diff
changeset
|
155 if (not arg) and (not opts.postgres) and (not opts.mysql): |
| 428 | 156 self.list_instances() |
| 425 | 157 return |
| 158 if self.successfully_connect_to_number(arg): | |
| 159 return | |
| 462 | 160 |
| 161 try: | |
| 162 db_instance = connections.DatabaseInstance(arg, opts, default_rdbms = self.default_rdbms) | |
|
480
e60d9192bfaa
preserve exception on connections
Catherine Devlin <catherine.devlin@gmail.com>
parents:
472
diff
changeset
|
163 except Exception, e: |
| 462 | 164 self.perror('Connection failure.\n' + self.do_connect.__doc__) |
|
480
e60d9192bfaa
preserve exception on connections
Catherine Devlin <catherine.devlin@gmail.com>
parents:
472
diff
changeset
|
165 self.perror(str(e)) |
| 462 | 166 return |
| 428 | 167 if opts.add or (self.instance_number is None): |
| 425 | 168 try: |
| 428 | 169 self.instance_number = max(self.instances.keys()) + 1 |
| 425 | 170 except ValueError: |
| 428 | 171 self.instance_number = 0 |
| 172 db_instance.set_instance_number(self.instance_number) | |
| 429 | 173 self.instances[self.instance_number] = db_instance |
| 428 | 174 self.make_instance_current(self.instance_number) |
| 425 | 175 if (self.rdbms == 'oracle') and self.serveroutput: |
|
431
cac7333f9ff5
beginning rework of threaded metadata discovery
catherine@dellzilla
parents:
430
diff
changeset
|
176 self.current_instance.connection.cursor().callproc('dbms_output.enable',[]) |
| 407 | 177 |
| 436 | 178 def do_pickle(self, arg): |
| 179 self.current_instance.pickle() | |
| 180 | |
| 463 | 181 _availability_regex = re.compile(r'\(\s*Availab(.*)\)', re.IGNORECASE ) |
|
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
182 def postparsing_precmd(self, statement): |
|
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
183 stop = 0 |
| 428 | 184 self.saved_instance_number = None |
| 185 if statement.parsed.instance_number: | |
| 186 saved_instance_number = self.instance_number | |
| 258 | 187 try: |
| 428 | 188 if self.successfully_connect_to_number(statement.parsed.instance_number): |
|
335
00b183a103b3
bare prefix switches connection
Catherine Devlin <catherine.devlin@gmail.com>
parents:
324
diff
changeset
|
189 if statement.parsed.command: |
| 428 | 190 self.saved_instance_number = saved_instance_number |
| 261 | 191 except KeyError: |
| 428 | 192 self.list_instances() |
| 193 raise KeyError, 'No connection #%s' % statement.parsed.instance_number | |
| 463 | 194 try: |
| 195 method = getattr(self, 'do_' + statement.parsed.command) | |
| 196 availability = self._availability_regex.search(method.__doc__ or '') | |
| 197 if availability and (self.current_instance.rdbms not in availability.group(1).lower()): | |
| 198 raise NotImplementedError, '``%s`` unavailable for %s' % ( | |
| 199 statement.parsed.command, self.current_instance.rdbms) | |
| 200 except AttributeError: | |
| 201 pass | |
|
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
202 return stop, statement |
|
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
203 def postparsing_postcmd(self, stop): |
| 472 | 204 try: |
| 205 if self.saved_instance_number is not None: | |
| 206 self.successfully_connect_to_number(self.saved_instance_number) | |
| 207 except AttributeError: | |
| 208 pass # no saved_instance_number has been defined | |
|
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
209 return stop |
|
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
210 |
| 256 | 211 do_host = cmd2.Cmd.do_shell |
| 189 | 212 |
| 213 def emptyline(self): | |
| 214 pass | |
|
264
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
215 |
|
265
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
216 def _show_errors(self, all_users=False, limit=None, mintime=None, targets=[]): |
|
264
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
217 if all_users: |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
218 user = '' |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
219 else: |
|
265
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
220 user = "AND ao.owner = user\n" |
|
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
221 if targets: |
|
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
222 target = 'AND (%s)\n' % ' OR '.join("ae.type || '/' || ae.name LIKE '%s'" % |
|
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
223 t.upper().replace('*','%') for t in targets) |
|
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
224 else: |
|
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
225 target = '' |
|
264
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
226 self.curs.execute(''' |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
227 SELECT ae.owner, ae.name, ae.type, ae.position, ae.line, ae.attribute, |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
228 ae.text error_text, |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
229 src.text object_text, |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
230 ao.last_ddl_time |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
231 FROM all_errors ae |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
232 JOIN all_objects ao ON ( ae.owner = ao.owner |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
233 AND ae.name = ao.object_name |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
234 AND ae.type = ao.object_type) |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
235 JOIN all_source src ON ( ae.owner = src.owner |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
236 AND ae.name = src.name |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
237 AND ae.type = src.type |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
238 AND ae.line = src.line) |
|
265
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
239 WHERE 1=1 |
|
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
240 %s%sORDER BY ao.last_ddl_time DESC''' % (user, target)) |
|
264
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
241 if limit is None: |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
242 errors = self.curs.fetchall() |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
243 else: |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
244 errors = self.curs.fetchmany(numRows = limit) |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
245 for err in errors: |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
246 if (mintime is not None) and (err[8] < mintime): |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
247 break |
| 333 | 248 self.poutput('%s at line %d of %s %s.%s:' % (err[5], err[4], err[2], err[0], err[1])) |
| 249 self.poutput(err[7]) | |
| 250 self.poutput((' ' * (err[3]-1)) + '^') | |
| 251 self.poutput(err[6]) | |
| 252 self.poutput('\n') | |
|
264
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
253 |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
254 def current_database_time(self): |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
255 self.curs.execute('select sysdate from dual') |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
256 return self.curs.fetchone()[0] |
|
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
257 |
| 189 | 258 def do_terminators(self, arg): |
| 259 """; standard Oracle format | |
| 260 \\c CSV (with headings) | |
| 261 \\C CSV (no headings) | |
| 262 \\g list | |
| 263 \\G aligned list | |
| 264 \\h HTML table | |
| 265 \\i INSERT statements | |
| 266 | 266 \\j JSON |
| 308 | 267 \\r ReStructured Text |
| 189 | 268 \\s CSV (with headings) |
| 269 \\S CSV (no headings) | |
| 270 \\t transposed | |
| 271 \\x XML | |
| 272 \\l line plot, with markers | |
| 273 \\L scatter plot (no lines) | |
| 274 \\b bar graph | |
| 275 \\p pie chart""" | |
| 333 | 276 self.poutput(self.do_terminators.__doc__) |
| 189 | 277 |
| 278 terminatorSearchString = '|'.join('\\' + d.split()[0] for d in do_terminators.__doc__.splitlines()) | |
| 279 | |
|
339
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
280 bindScanner = {'oracle': Parser(pyparsing.Literal(':') + pyparsing.Word( pyparsing.alphanums + "_$#" )), |
| 407 | 281 'postgres': Parser(pyparsing.Literal('%(') + legal_sql_word + ')s')} |
|
340
001d01eeac90
bind vars for postgres
Catherine Devlin <catherine.devlin@gmail.com>
parents:
339
diff
changeset
|
282 def findBinds(self, target, givenBindVars = {}): |
|
339
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
283 result = givenBindVars |
|
416
e7769bc81960
several changes to settables - incomplete
catherine@bothari
parents:
415
diff
changeset
|
284 #TODO: A consistent bind style? As a setting, perhaps? |
|
339
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
285 if self.rdbms in self.bindScanner: |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
286 for finding, startat, endat in self.bindScanner[self.rdbms].scanner.scanString(target): |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
287 varname = finding[1] |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
288 try: |
|
340
001d01eeac90
bind vars for postgres
Catherine Devlin <catherine.devlin@gmail.com>
parents:
339
diff
changeset
|
289 result[varname] = self.binds[varname] |
|
339
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
290 except KeyError: |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
291 if not givenBindVars.has_key(varname): |
| 420 | 292 raise KeyError, 'Bind variable "%s" not defined.' % (varname) |
|
339
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
293 return result |
|
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
294 |
| 189 | 295 def default(self, arg): |
|
340
001d01eeac90
bind vars for postgres
Catherine Devlin <catherine.devlin@gmail.com>
parents:
339
diff
changeset
|
296 self.varsUsed = self.findBinds(arg, givenBindVars={}) |
| 247 | 297 ending_args = arg.lower().split()[-2:] |
| 298 if 'end' in ending_args: | |
| 246 | 299 command = '%s %s;' |
| 300 else: | |
|
264
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
301 command = '%s %s' |
|
317
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
302 if self.rdbms == 'oracle': |
| 315 | 303 current_time = self.current_database_time() |
| 417 | 304 commandstring = command % (arg.parsed.command, arg.parsed.args) |
| 305 self.curs.execute(commandstring, self.varsUsed) | |
|
276
0b7031c2229e
autobind unit test failing
catherine@Elli.myhome.westell.com
parents:
266
diff
changeset
|
306 executionmessage = '\nExecuted%s\n' % ((self.curs.rowcount > 0) and ' (%d rows)' % self.curs.rowcount or '') |
|
317
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
307 if self.rdbms == 'oracle': |
| 315 | 308 self._show_errors(all_users=True, limit=1, mintime=current_time) |
| 333 | 309 self.pfeedback(executionmessage) |
|
198
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
310 |
|
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
311 def do_commit(self, arg=''): |
|
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
312 self.default(self.parsed('commit %s;' % (arg))) |
|
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
313 def do_rollback(self, arg=''): |
|
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
314 self.default(self.parsed('rollback %s;' % (arg))) |
| 189 | 315 def do_quit(self, arg): |
| 261 | 316 if self.commit_on_exit: |
| 317 self.closeall() | |
| 189 | 318 return cmd2.Cmd.do_quit(self, None) |
| 319 do_exit = do_quit | |
| 320 do_q = do_quit | |
| 337 | 321 def colorize(self, val, color): |
| 443 | 322 if color not in self.colorcodes: |
| 323 if (color % 2): | |
| 324 color = 'red' | |
| 325 else: | |
| 326 color = 'cyan' | |
| 327 return cmd2.Cmd.colorize(self, val, color) | |
| 400 | 328 def pmatrix(self,rows,maxlen=30,heading=True,restructuredtext=False): |
| 337 | 329 '''prints a matrix, used by sqlpython to print queries' result sets''' |
| 400 | 330 names = self.colnames |
| 331 maxen = [len(n) for n in self.colnames] | |
| 337 | 332 toprint = [] |
| 400 | 333 rcols = range(len(self.colnames)) |
| 337 | 334 rrows = range(len(rows)) |
| 335 for i in rrows: # loops for all rows | |
| 336 rowsi = map(str, rows[i]) # current row to process | |
| 337 split = [] # service var is row split is needed | |
| 338 mustsplit = 0 # flag | |
| 339 for j in rcols: | |
| 400 | 340 if str(self.coltypes[j]) == "<type 'cx_Oracle.BINARY'>": # handles RAW columns |
| 337 | 341 rowsi[j] = binascii.b2a_hex(rowsi[j]) |
| 342 maxen[j] = max(maxen[j], len(rowsi[j])) # computes max field length | |
| 343 if maxen[j] <= maxlen: | |
| 344 split.append('') | |
| 345 else: # split the line is 2 because field is too long | |
| 346 mustsplit = 1 | |
| 347 maxen[j] = maxlen | |
| 348 split.append(rowsi[j][maxlen-1:2*maxlen-1]) | |
| 349 rowsi[j] = rowsi[j][0:maxlen-1] # this implem. truncates after maxlen*2 | |
| 350 toprint.append(rowsi) # 'toprint' is a printable copy of rows | |
| 351 if mustsplit != 0: | |
| 352 toprint.append(split) | |
| 353 sepcols = [] | |
| 354 for i in rcols: | |
| 355 maxcol = maxen[i] | |
| 356 name = names[i] | |
| 357 sepcols.append("-" * maxcol) # formats column names (header) | |
| 358 names[i] = name + (" " * (maxcol-len(name))) # formats separ line with -- | |
| 359 rrows2 = range(len(toprint)) | |
| 360 for j in rrows2: | |
| 361 val = toprint[j][i] | |
| 400 | 362 if str(self.coltypes[i]) == "<type 'cx_Oracle.NUMBER'>": # right align numbers - but must generalize! |
| 337 | 363 toprint[j][i] = (" " * (maxcol-len(val))) + val |
| 364 else: | |
| 365 toprint[j][i] = val + (" " * (maxcol-len(val))) | |
| 366 toprint[j][i] = self.colorize(toprint[j][i], i) | |
| 367 for j in rrows2: | |
| 368 toprint[j] = ' '.join(toprint[j]) | |
| 369 names = [self.colorize(name, n) for (n, name) in enumerate(names)] | |
| 370 names = ' '.join(names) | |
| 371 names = self.colorize(names, 'bold') | |
| 372 sepcols = ' '.join(sepcols) | |
| 373 if heading or restructuredtext: | |
| 374 toprint.insert(0, sepcols) | |
| 375 toprint.insert(0, names) | |
| 376 if restructuredtext: | |
| 377 toprint.insert(0, sepcols) | |
| 378 toprint.append(sepcols) | |
| 379 return '\n'.join(toprint) | |
| 189 | 380 |
| 337 | 381 |
| 382 | |
| 383 | |
| 189 | 384 |