Mercurial > sqlpython

comparison sqlpyPlus.py @ 151:802d8df993da

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
midway through making plots saveable
author catherine@dellzilla
date Fri, 26 Sep 2008 16:11:29 -0400
parents b00a020b81c6
children c26bc528cb05
comparison
equal deleted inserted replaced
150:b00a020b81c6 151:802d8df993da
21 or with a python-like shorthand 21 or with a python-like shorthand
22 :b = 3 22 :b = 3
23 23
24 - catherinedevlin.blogspot.com May 31, 2006 24 - catherinedevlin.blogspot.com May 31, 2006
25 """ 25 """
26 import sys, os, re, sqlpython, cx_Oracle, pyparsing, re, completion 26 import sys, os, re, sqlpython, cx_Oracle, pyparsing, re, completion, datetime, pickle
27 from cmd2 import Cmd, make_option, options, Statekeeper 27 from cmd2 import Cmd, make_option, options, Statekeeper
28 from output_templates import * 28 from output_templates import *
29 try:
30 import pylab
31 except:
32 pass
29 33
30 descQueries = { 34 descQueries = {
31 'TABLE': (""" 35 'TABLE': ("""
32 atc.column_name, 36 atc.column_name,
33 CASE atc.nullable WHEN 'Y' THEN 'NULL' ELSE 'NOT NULL' END "Null?", 37 CASE atc.nullable WHEN 'Y' THEN 'NULL' ELSE 'NOT NULL' END "Null?",
334 except KeyError: 338 except KeyError:
335 if not givenBindVars.has_key(varname): 339 if not givenBindVars.has_key(varname):
336 print 'Bind variable %s not defined.' % (varname) 340 print 'Bind variable %s not defined.' % (varname)
337 return result 341 return result
338 342
343 try:
344 import pylab
345 class Plot(object):
346 plottable_types = (cx_Oracle.NUMBER, datetime.datetime)
347 def __init__(self):
348 self.legends = []
349 self.yserieslists = []
350 self.xticks = []
351 def build(self, sqlSession):
352 self.title = sqlSession.tblname
353 self.xlabel = sqlSession.curs.description[0][0]
354 self.datatypes = [d[1] for d in sqlSession.curs.description]
355 for (colNum, datatype) in enumerate(self.datatypes):
356 if colNum > 0 and datatype in self.plottable_types:
357 self.yserieslists.append([row[colNum] for row in sqlSession.rows])
358 self.legends.append(sqlSession.curs.description[colNum][0])
359 if self.datatypes[0] in self.plottable_types:
360 self.xvalues = [r[0] for r in sqlSession.rows]
361 else:
362 self.xvalues = range(sqlSession.curs.rowcount)
363 self.xticks = [r[0] for r in sqlSession.rows]
364 def save(self):
365 pass
366 def draw(self):
367 if not self.yserieslists:
368 result = 'At least one quantitative column needed to plot.'
369 return result
370 if self.xticks:
371 pylab.xticks(self.xvalues, self.xticks)
372 for (colName, yseries) in self.yserieslists.items():
373 pylab.plot(xvalues, yseries, '-o')
374 pylab.xlabel(self.xlabel)
375 pylab.title(self.title)
376 pylab.legend(self.legends)
377 pylab.show()
378 return 'If your lines zigzag, you may want to ORDER BY the x axis.'
379
380 except ImportError:
381 class Plot(object):
382 def build(self, sqlSession):
383 pass
384 def save(self):
385 pass
386 def draw(self):
387 return 'Must install python-matplotlib to plot query results.'
388
339 class sqlpyPlus(sqlpython.sqlpython): 389 class sqlpyPlus(sqlpython.sqlpython):
340 defaultExtension = 'sql' 390 defaultExtension = 'sql'
341 sqlpython.sqlpython.shortcuts.update({':': 'setbind', '\\': 'psql', '@': '_load'}) 391 sqlpython.sqlpython.shortcuts.update({':': 'setbind', '\\': 'psql', '@': '_load'})
342 multilineCommands = '''select insert update delete tselect 392 multilineCommands = '''select insert update delete tselect
343 create drop alter'''.split() 393 create drop alter'''.split()
404 for (itm, useq) in zip(row, usequotes)) 454 for (itm, useq) in zip(row, usequotes))
405 result = ['INSERT INTO %s (%s) VALUES (%s);' % 455 result = ['INSERT INTO %s (%s) VALUES (%s);' %
406 (self.tblname, ','.join(self.colnames), formatRow(row)) 456 (self.tblname, ','.join(self.colnames), formatRow(row))
407 for row in self.rows] 457 for row in self.rows]
408 return '\n'.join(result) 458 return '\n'.join(result)
409
410 tableNameFinder = re.compile(r'from\s+([\w$#_"]+)', re.IGNORECASE | re.MULTILINE | re.DOTALL) 459 tableNameFinder = re.compile(r'from\s+([\w$#_"]+)', re.IGNORECASE | re.MULTILINE | re.DOTALL)
411 def output(self, outformat, rowlimit): 460 def output(self, outformat, rowlimit):
412 self.tblname = self.tableNameFinder.search(self.curs.statement).group(1) 461 self.tblname = self.tableNameFinder.search(self.curs.statement).group(1)
413 self.colnames = [d[0] for d in self.curs.description] 462 self.colnames = [d[0] for d in self.curs.description]
414 if outformat == '\\i': 463 if outformat == '\\i':
439 rname = transpr[x][0] 488 rname = transpr[x][0]
440 transpr[x] = map(binascii.b2a_hex, transpr[x]) 489 transpr[x] = map(binascii.b2a_hex, transpr[x])
441 transpr[x][0] = rname 490 transpr[x][0] = rname
442 newdesc[0][0] = 'COLUMN NAME' 491 newdesc[0][0] = 'COLUMN NAME'
443 result = '\n' + sqlpython.pmatrix(transpr,newdesc) 492 result = '\n' + sqlpython.pmatrix(transpr,newdesc)
493 elif outformat == '\\p':
494 plot = Plot()
495 plot.build(self)
496 plot.save()
497 return plot.draw()
444 else: 498 else:
445 result = sqlpython.pmatrix(self.rows, self.curs.description, self.maxfetch) 499 result = sqlpython.pmatrix(self.rows, self.curs.description, self.maxfetch)
446 return result 500 return result
447 501
448 legalOracle = re.compile('[a-zA-Z_$#]') 502 legalOracle = re.compile('[a-zA-Z_$#]')
449 503
450 def select_scalar_list(self, sql, binds={}): 504 def select_scalar_list(self, sql, binds={}):
451 self.curs.execute(sql, binds) 505 self.curs.execute(sql, binds)
452 return [r[0] for r in self.curs.fetchall()] 506 return [r[0] for r in self.curs.fetchall()]
493 stmt = "SELECT object_name FROM all_objects WHERE object_name LIKE '%s%%'" 547 stmt = "SELECT object_name FROM all_objects WHERE object_name LIKE '%s%%'"
494 completions = self.select_scalar_list(stmt % (text)) 548 completions = self.select_scalar_list(stmt % (text))
495 return completions 549 return completions
496 550
497 rowlimitPattern = pyparsing.Word(pyparsing.nums)('rowlimit') 551 rowlimitPattern = pyparsing.Word(pyparsing.nums)('rowlimit')
498 terminatorPattern = (pyparsing.oneOf('; \\s \\S \\c \\C \\t \\x \\h \\g \\G \\i') 552 terminatorPattern = (pyparsing.oneOf('; \\s \\S \\c \\C \\t \\x \\h \\g \\G \\i \\p')
499 ^ pyparsing.Literal('\n/') ^ \ 553 ^ pyparsing.Literal('\n/') ^ \
500 (pyparsing.Literal('\nEOF') + pyparsing.stringEnd)) \ 554 (pyparsing.Literal('\nEOF') + pyparsing.stringEnd)) \
501 ('terminator') + \ 555 ('terminator') + \
502 pyparsing.Optional(rowlimitPattern) #+ \ 556 pyparsing.Optional(rowlimitPattern) #+ \
503 #pyparsing.FollowedBy(pyparsing.LineEnd()) 557 #pyparsing.FollowedBy(pyparsing.LineEnd())