Mercurial > sqlpython

comparison sqlpyPlus.py @ 5:65ae6cec71c6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
expanded desc good so far
author devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
date Tue, 04 Dec 2007 17:19:18 -0500
parents 23c3a58d7804
children f9ec5c20beb4
comparison
equal deleted inserted replaced
4:23c3a58d7804 5:65ae6cec71c6
24 24
25 - catherinedevlin.blogspot.com May 31, 2006 25 - catherinedevlin.blogspot.com May 31, 2006
26 """ 26 """
27 # note in cmd.cmd about supporting emacs commands? 27 # note in cmd.cmd about supporting emacs commands?
28 28
29 pullQueries = {
30 'PROCEDURE':("""
31 text
32 FROM all_source
33 WHERE owner = :owner
34 AND name = :object_name
35 """,)
36 }
37 pullQueries['TRIGGER'] = pullQueries['PROCEDURE']
38 pullQueries['FUNCTION'] = pullQueries['PROCEDURE']
39
29 descQueries = { 40 descQueries = {
30 'TABLE': """ 41 'TABLE': ("""
31 atc.column_name, 42 atc.column_name,
32 CASE atc.nullable WHEN 'Y' THEN 'NULL' ELSE 'NOT NULL' END "Null?", 43 CASE atc.nullable WHEN 'Y' THEN 'NULL' ELSE 'NOT NULL' END "Null?",
33 atc.data_type || 44 atc.data_type ||
34 CASE atc.data_type WHEN 'DATE' THEN '' 45 CASE atc.data_type WHEN 'DATE' THEN ''
35 ELSE '(' || 46 ELSE '(' ||
41 CASE atc.data_type WHEN 'DATE' THEN '' ELSE ')' END 52 CASE atc.data_type WHEN 'DATE' THEN '' ELSE ')' END
42 data_type 53 data_type
43 FROM all_tab_columns atc 54 FROM all_tab_columns atc
44 WHERE atc.table_name = :object_name 55 WHERE atc.table_name = :object_name
45 AND atc.owner = :owner 56 AND atc.owner = :owner
46 ORDER BY atc.column_id;""", 57 ORDER BY atc.column_id;""",),
47 'PROCEDURE':""" 58 'PROCEDURE': ("""
48 argument_name, 59 argument_name,
49 data_type, 60 data_type,
50 in_out, 61 in_out,
51 default_value 62 default_value
52 FROM all_arguments 63 FROM all_arguments
53 WHERE object_name = :object_name 64 WHERE object_name = :object_name
54 AND owner = :owner 65 AND owner = :owner
55 AND package_name IS NULL 66 AND package_name IS NULL
56 AND argument_name IS NOT NULL 67 AND argument_name IS NOT NULL
57 ORDER BY sequence;""", 68 ORDER BY sequence;""",),
58 'PackageObjects':""" 69 'PackageObjects':("""
59 SELECT DISTINCT object_name 70 SELECT DISTINCT object_name
60 FROM all_arguments 71 FROM all_arguments
61 WHERE package_name = :package_name 72 WHERE package_name = :package_name
62 AND owner = :owner""", 73 AND owner = :owner""",),
63 'PackageObjArgs':""" 74 'PackageObjArgs':("""
64 object_name, 75 object_name,
65 argument_name, 76 argument_name,
66 data_type, 77 data_type,
67 in_out, 78 in_out,
68 default_value 79 default_value
69 FROM all_arguments 80 FROM all_arguments
70 WHERE package_name = :package_name 81 WHERE package_name = :package_name
71 AND object_name = :object_name 82 AND object_name = :object_name
72 AND owner = :owner 83 AND owner = :owner
73 AND argument_name IS NOT NULL 84 AND argument_name IS NOT NULL
74 ORDER BY sequence""", 85 ORDER BY sequence""",),
75 'TRIGGER':""" 86 'TRIGGER':("""
76 trigger_name, 87 description
77 trigger_type, 88 FROM all_triggers
78 triggering_event, 89 WHERE owner = :owner
90 AND trigger_name = :object_name
91 """,
92 """
79 table_owner, 93 table_owner,
80 base_object_type, 94 base_object_type,
81 table_name, 95 table_name,
82 column_name, 96 column_name,
83 when_clause, 97 when_clause,
84 status, 98 status,
85 description,
86 action_type, 99 action_type,
87 crossedition 100 crossedition
88 FROM all_triggers 101 FROM all_triggers
89 WHERE owner = :owner 102 WHERE owner = :owner
90 AND trigger_name = :object_name 103 AND trigger_name = :object_name
91 \\t 104 \\t
92 """ 105 """,
106 ),
107 'INDEX':("""
108 index_type,
109 table_owner,
110 table_name,
111 table_type,
112 uniqueness,
113 compression,
114 partitioned,
115 temporary,
116 generated,
117 secondary,
118 dropped,
119 visibility
120 FROM all_indexes
121 WHERE owner = :owner
122 AND index_name = :object_name
123 \\t
124 """,)
93 } 125 }
94 descQueries['VIEW'] = descQueries['TABLE'] 126 descQueries['VIEW'] = descQueries['TABLE']
95 descQueries['FUNCTION'] = descQueries['PROCEDURE'] 127 descQueries['FUNCTION'] = descQueries['PROCEDURE']
96 128
97 queries = { 129 queries = {
482 result.append(','.join('"%s"' % ('' if itm is None else itm) for itm in row)) 514 result.append(','.join('"%s"' % ('' if itm is None else itm) for itm in row))
483 result = '\n'.join(result) 515 result = '\n'.join(result)
484 elif outformat == '\\h': 516 elif outformat == '\\h':
485 result = self.output_as_html_table() 517 result = self.output_as_html_table()
486 elif outformat == '\\t': 518 elif outformat == '\\t':
487 519 rows = [self.colnames]
520 rows.extend(list(self.rows))
521 transpr = [[rows[y][x] for y in range(len(rows))]for x in range(len(rows[0]))] # matrix transpose
522 newdesc = [['ROW N.'+str(y),10] for y in range(len(rows))]
523 for x in range(len(self.curs.description)):
524 if str(self.curs.description[x][1]) == "<type 'cx_Oracle.BINARY'>": # handles RAW columns
525 rname = transpr[x][0]
526 transpr[x] = map(binascii.b2a_hex, transpr[x])
527 transpr[x][0] = rname
528 self.debg=transpr
529 newdesc[0][0] = 'COLUMN NAME'
530 result = '\n' + sqlpython.pmatrix(transpr,newdesc)
488 else: 531 else:
489 result = sqlpython.pmatrix(self.rows, self.curs.description, self.maxfetch) 532 result = sqlpython.pmatrix(self.rows, self.curs.description, self.maxfetch)
490 return result 533 return result
491 534
492 def do_select(self, arg, bindVarsIn=None): 535 def do_select(self, arg, bindVarsIn=None, override_terminator=None):
493 """Fetch rows from a table. 536 """Fetch rows from a table.
494 537
495 Limit the number of rows retrieved by appending 538 Limit the number of rows retrieved by appending
496 an integer after the terminator 539 an integer after the terminator
497 (example: SELECT * FROM mytable;10 ) 540 (example: SELECT * FROM mytable;10 )
500 ("help terminators" for details) 543 ("help terminators" for details)
501 """ 544 """
502 bindVarsIn = bindVarsIn or {} 545 bindVarsIn = bindVarsIn or {}
503 self.query = 'select ' + arg 546 self.query = 'select ' + arg
504 (self.query, terminator, rowlimit) = sqlpython.findTerminator(self.query) 547 (self.query, terminator, rowlimit) = sqlpython.findTerminator(self.query)
548 if override_terminator:
549 terminator = override_terminator
505 rowlimit = int(rowlimit or 0) 550 rowlimit = int(rowlimit or 0)
506 if terminator == '\\t': 551 try:
507 self.do_tselect(' '.join(self.query.split()[1:]) + ';', rowlimit) 552 self.varsUsed = findBinds(self.query, self.binds, bindVarsIn)
508 return 553 self.curs.execute(self.query, self.varsUsed)
509 else: 554 self.rows = self.curs.fetchmany(min(self.maxfetch, (rowlimit or self.maxfetch)))
510 try: 555 self.desc = self.curs.description
511 self.varsUsed = findBinds(self.query, self.binds, bindVarsIn) 556 self.rc = self.curs.rowcount
512 self.curs.execute(self.query, self.varsUsed) 557 if self.rc > 0:
513 self.rows = self.curs.fetchmany(min(self.maxfetch, (rowlimit or self.maxfetch))) 558 print '\n' + self.output(terminator, rowlimit)
514 self.desc = self.curs.description 559 if self.rc == 0:
515 self.rc = self.curs.rowcount 560 print '\nNo rows Selected.\n'
516 if self.rc > 0: 561 elif self.rc == 1:
517 print '\n' + self.output(terminator, rowlimit) 562 print '\n1 row selected.\n'
518 if self.rc == 0: 563 if self.autobind:
519 print '\nNo rows Selected.\n' 564 self.binds.update(dict(zip([d[0] for d in self.desc], self.rows[0])))
520 elif self.rc == 1: 565 elif self.rc < self.maxfetch:
521 print '\n1 row selected.\n' 566 print '\n%d rows selected.\n' % self.rc
522 if self.autobind: 567 else:
523 self.binds.update(dict(zip([d[0] for d in self.desc], self.rows[0]))) 568 print '\nSelected Max Num rows (%d)' % self.rc
524 elif self.rc < self.maxfetch: 569 except Exception, e:
525 print '\n%d rows selected.\n' % self.rc 570 print e
526 else: 571 import traceback
527 print '\nSelected Max Num rows (%d)' % self.rc 572 traceback.print_exc(file=sys.stdout)
528 except Exception, e:
529 print e
530 import traceback
531 traceback.print_exc(file=sys.stdout)
532 self.sqlBuffer.append(self.query) 573 self.sqlBuffer.append(self.query)
533 574
534 def showParam(self, param): 575 def showParam(self, param):
535 param = param.strip().lower() 576 param = param.strip().lower()
536 if param in self.settable: 577 if param in self.settable:
584 625
585 def do_describe(self, arg): 626 def do_describe(self, arg):
586 "emulates SQL*Plus's DESCRIBE" 627 "emulates SQL*Plus's DESCRIBE"
587 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper()) 628 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper())
588 print "%s %s.%s" % (object_type, owner, object_name) 629 print "%s %s.%s" % (object_type, owner, object_name)
589 if object_type in ('TABLE','VIEW','PROCEDURE','FUNCTION','TRIGGER'): 630 descQ = descQueries.get(object_type)
590 self.do_select(descQueries[object_type],{'object_name':object_name, 'owner':owner}) 631 if descQ:
632 for q in descQ:
633 self.do_select(q,bindVarsIn={'object_name':object_name, 'owner':owner})
591 elif object_type == 'PACKAGE': 634 elif object_type == 'PACKAGE':
592 self.curs.execute(descQueries['PackageObjects'], {'package_name':object_name, 'owner':owner}) 635 self.curs.execute(descQueries['PackageObjects'], {'package_name':object_name, 'owner':owner})
593 for (packageObj_name,) in self.curs: 636 for (packageObj_name,) in self.curs:
594 print packageObj_name 637 print packageObj_name
595 self.do_select(descQueries['PackageObjArgs'],{'package_name':object_name, 'owner':owner, 'object_name':packageObj_name}) 638 self.do_select(descQueries['PackageObjArgs'],bindVarsIn={'package_name':object_name, 'owner':owner, 'object_name':packageObj_name})
596 do_desc = do_describe 639 do_desc = do_describe
597 640
598 def do_comments(self, arg): 641 def do_comments(self, arg):
599 'Prints comments on a table and its columns.' 642 'Prints comments on a table and its columns.'
600 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper()) 643 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper())
601 if object_type: 644 if object_type:
602 self.curs.execute(queries['tabComments'],{'table_name':object_name, 'owner':owner}) 645 self.curs.execute(queries['tabComments'],{'table_name':object_name, 'owner':owner})
603 print "%s %s.%s: %s" % (object_type, owner, object_name, self.curs.fetchone()[0]) 646 print "%s %s.%s: %s" % (object_type, owner, object_name, self.curs.fetchone()[0])
604 self.do_select(queries['colComments'],{'owner':owner, 'object_name': object_name}) 647 self.do_select(queries['colComments'],bindVarsIn={'owner':owner, 'object_name': object_name})
605 648
606 def resolve(self, identifier): 649 def resolve(self, identifier):
607 """Checks (my objects).name, (my synonyms).name, (public synonyms).name 650 """Checks (my objects).name, (my synonyms).name, (public synonyms).name
608 to resolve a database object's name. """ 651 to resolve a database object's name. """
609 parts = identifier.split('.') 652 parts = identifier.split('.')