Mercurial > sqlpython

comparison sqlpyPlus.py @ 46:ad41f6a577f7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
think terminators are working now
author catherine.devlin@gmail.com
date Tue, 01 Apr 2008 16:56:30 -0400
parents 71576bf684c8
children 46b7b31dc395
comparison
equal deleted inserted replaced
45:71576bf684c8 46:ad41f6a577f7
380 380
381 def do_shortcuts(self,arg): 381 def do_shortcuts(self,arg):
382 """Lists available first-character shortcuts 382 """Lists available first-character shortcuts
383 (i.e. '!dir' is equivalent to 'shell dir')""" 383 (i.e. '!dir' is equivalent to 'shell dir')"""
384 for (scchar, scto) in self.shortcuts.items(): 384 for (scchar, scto) in self.shortcuts.items():
385 print '%s: %s' % (scchar, scto) 385 self.stdout.write('%s: %s\n') % (scchar, scto)
386 386
387 def colnames(self): 387 def colnames(self):
388 return [d[0] for d in curs.description] 388 return [d[0] for d in curs.description]
389 389
390 def sql_format_itm(self, itm, needsquotes): 390 def sql_format_itm(self, itm, needsquotes):
473 result = '\n' + sqlpython.pmatrix(transpr,newdesc) 473 result = '\n' + sqlpython.pmatrix(transpr,newdesc)
474 else: 474 else:
475 result = sqlpython.pmatrix(self.rows, self.curs.description, self.maxfetch) 475 result = sqlpython.pmatrix(self.rows, self.curs.description, self.maxfetch)
476 return result 476 return result
477 477
478 statementEndPattern = re.compile(r'(.*)(;|\\[gGhtxicCsS])\s*(\d*)$') 478 statementEndPattern = re.compile(r'(.*)(;|\\[gGhtxicCsS])\s*(\d*)$', re.DOTALL | re.MULTILINE)
479 # what about quote-enclosed? 479 # what about quote-enclosed?
480 480
481 def findTerminator(self, statement): 481 def findTerminator(self, statement):
482 m = self.statementEndPattern.search(statement) 482 m = self.statementEndPattern.search(statement)
483 if m: 483 if m:
503 if override_terminator: 503 if override_terminator:
504 terminator = override_terminator 504 terminator = override_terminator
505 rowlimit = int(rowlimit or 0) 505 rowlimit = int(rowlimit or 0)
506 try: 506 try:
507 self.varsUsed = findBinds(self.query, self.binds, bindVarsIn) 507 self.varsUsed = findBinds(self.query, self.binds, bindVarsIn)
508 #import pdb; pdb.set_trace()
508 self.curs.execute(self.query, self.varsUsed) 509 self.curs.execute(self.query, self.varsUsed)
509 self.rows = self.curs.fetchmany(min(self.maxfetch, (rowlimit or self.maxfetch))) 510 self.rows = self.curs.fetchmany(min(self.maxfetch, (rowlimit or self.maxfetch)))
510 self.desc = self.curs.description 511 self.desc = self.curs.description
511 self.rc = self.curs.rowcount 512 self.rc = self.curs.rowcount
512 if self.rc > 0: 513 if self.rc > 0:
513 print '\n' + self.output(terminator, rowlimit) 514 self.stdout.write('\n' + self.output(terminator, rowlimit) + '\n')
514 if self.rc == 0: 515 if self.rc == 0:
515 print '\nNo rows Selected.\n' 516 self.stdout.write('\nNo rows Selected.\n\n')
516 elif self.rc == 1: 517 elif self.rc == 1:
517 print '\n1 row selected.\n' 518 self.stdout.write('\n1 row selected.\n\n')
518 if self.autobind: 519 if self.autobind:
519 self.binds.update(dict(zip([''.join(l for l in d[0] if l.isalnum()) for d in self.desc], self.rows[0]))) 520 self.binds.update(dict(zip([''.join(l for l in d[0] if l.isalnum()) for d in self.desc], self.rows[0])))
520 if len(self.desc) == 1: 521 if len(self.desc) == 1:
521 self.binds['_'] = self.rows[0][0] 522 self.binds['_'] = self.rows[0][0]
522 elif self.rc < self.maxfetch: 523 elif self.rc < self.maxfetch:
523 print '\n%d rows selected.\n' % self.rc 524 self.stdout.write('\n%d rows selected.\n\n' % self.rc)
524 else: 525 else:
525 print '\nSelected Max Num rows (%d)' % self.rc 526 self.stdout.write('\nSelected Max Num rows (%d)\n' % self.rc)
526 except Exception, e: 527 except Exception, e:
527 print e 528 print e
528 import traceback 529 import traceback
529 traceback.print_exc(file=sys.stdout) 530 traceback.print_exc(file=sys.stdout)
530 self.sqlBuffer.append(self.query) 531 self.sqlBuffer.append(self.query)
535 536
536 --full, -f: get dependent objects as well""" 537 --full, -f: get dependent objects as well"""
537 538
538 options, arg = self.pullflags.parse(arg) 539 options, arg = self.pullflags.parse(arg)
539 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper()) 540 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper())
540 print "%s %s.%s" % (object_type, owner, object_name) 541 self.stdout.write("%s %s.%s\n" % (object_type, owner, object_name))
541 print self.curs.callfunc('DBMS_METADATA.GET_DDL', cx_Oracle.CLOB, 542 self.stdout.write(self.curs.callfunc('DBMS_METADATA.GET_DDL', cx_Oracle.CLOB,
542 [object_type, object_name, owner]) 543 [object_type, object_name, owner])+'\n')
543 if options.has_key('full'): 544 if options.has_key('full'):
544 for dependent_type in ('OBJECT_GRANT', 'CONSTRAINT', 'TRIGGER'): 545 for dependent_type in ('OBJECT_GRANT', 'CONSTRAINT', 'TRIGGER'):
545 try: 546 try:
546 print self.curs.callfunc('DBMS_METADATA.GET_DEPENDENT_DDL', cx_Oracle.CLOB, 547 self.stdout.write(self.curs.callfunc('DBMS_METADATA.GET_DEPENDENT_DDL', cx_Oracle.CLOB,
547 [dependent_type, object_name, owner]) 548 [dependent_type, object_name, owner])+'\n')
548 except cx_Oracle.DatabaseError: 549 except cx_Oracle.DatabaseError:
549 pass 550 pass
550 551
551 findflags = flagReader.FlagSet([flagReader.Flag('insensitive')]) 552 findflags = flagReader.FlagSet([flagReader.Flag('insensitive')])
552 def do_find(self, arg): 553 def do_find(self, arg):
563 self.do_select("* from all_source where %s like '%%%s%%'" % (searchfor, arg)) 564 self.do_select("* from all_source where %s like '%%%s%%'" % (searchfor, arg))
564 565
565 def do_describe(self, arg): 566 def do_describe(self, arg):
566 "emulates SQL*Plus's DESCRIBE" 567 "emulates SQL*Plus's DESCRIBE"
567 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper()) 568 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper())
568 print "%s %s.%s" % (object_type, owner, object_name) 569 self.stdout.write("%s %s.%s\n" % (object_type, owner, object_name))
569 descQ = descQueries.get(object_type) 570 descQ = descQueries.get(object_type)
570 if descQ: 571 if descQ:
571 for q in descQ: 572 for q in descQ:
572 self.do_select(q,bindVarsIn={'object_name':object_name, 'owner':owner}) 573 self.do_select(q,bindVarsIn={'object_name':object_name, 'owner':owner})
573 elif object_type == 'PACKAGE': 574 elif object_type == 'PACKAGE':
581 def do_comments(self, arg): 582 def do_comments(self, arg):
582 'Prints comments on a table and its columns.' 583 'Prints comments on a table and its columns.'
583 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper()) 584 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper())
584 if object_type: 585 if object_type:
585 self.curs.execute(queries['tabComments'],{'table_name':object_name, 'owner':owner}) 586 self.curs.execute(queries['tabComments'],{'table_name':object_name, 'owner':owner})
586 print "%s %s.%s: %s" % (object_type, owner, object_name, self.curs.fetchone()[0]) 587 self.stdout.write("%s %s.%s: %s\n" % (object_type, owner, object_name, self.curs.fetchone()[0]))
587 self.do_select(queries['colComments'],bindVarsIn={'owner':owner, 'object_name': object_name}) 588 self.do_select(queries['colComments'],bindVarsIn={'owner':owner, 'object_name': object_name})
588 589
589 def resolve(self, identifier): 590 def resolve(self, identifier):
590 """Checks (my objects).name, (my synonyms).name, (public synonyms).name 591 """Checks (my objects).name, (my synonyms).name, (public synonyms).name
591 to resolve a database object's name. """ 592 to resolve a database object's name. """
604 print 'Could not resolve object %s.' % identifier 605 print 'Could not resolve object %s.' % identifier
605 object_type, owner, object_name = '', '', '' 606 object_type, owner, object_name = '', '', ''
606 return object_type, owner, object_name 607 return object_type, owner, object_name
607 608
608 def do_resolve(self, arg): 609 def do_resolve(self, arg):
609 print self.resolve(arg) 610 self.stdout.write(self.resolve(arg)+'\n')
610 611
611 def spoolstop(self): 612 def spoolstop(self):
612 if self.spoolFile: 613 if self.spoolFile:
613 sys.stdout = self.stdoutBeforeSpool 614 sys.stdout = self.stdoutBeforeSpool
614 print 'Finished spooling to ', self.spoolFile.name 615 print 'Finished spooling to ', self.spoolFile.name
615 self.spoolFile.close() 616 self.spoolFile.close()
707 'print VARNAME: Show current value of bind variable VARNAME.' 708 'print VARNAME: Show current value of bind variable VARNAME.'
708 if arg: 709 if arg:
709 if arg[0] == ':': 710 if arg[0] == ':':
710 arg = arg[1:] 711 arg = arg[1:]
711 try: 712 try:
712 print self.binds[arg] 713 self.stdout.write(self.binds[arg]+'\n')
713 except KeyError: 714 except KeyError:
714 print 'No bind variable ', arg 715 self.stdout.write('No bind variable %s\n' % arg)
715 else: 716 else:
716 self.do_setbind('') 717 self.do_setbind('')
717 def do_setbind(self, arg): 718 def do_setbind(self, arg):
718 args = arg.split(None, 2) 719 args = arg.split(None, 2)
719 if len(args) == 0: 720 if len(args) == 0: