Mercurial > sqlpython
annotate sqlpython.py @ 2:59903dcaf327
working on history
| author | devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil |
|---|---|
| date | Mon, 03 Dec 2007 14:29:16 -0500 |
| parents | 8fa146b9a2d7 |
| children | cd23cd62de3c |
| rev | line source |
|---|---|
| 0 | 1 # |
| 2 # SqlPython V1.3 | |
| 3 # Author: Luca.Canali@cern.ch, Apr 2006 | |
| 4 # Rev 18-Oct-07 | |
| 5 # | |
| 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 | |
| 11 import cmd,getpass,binascii,cx_Oracle,re | |
| 12 import pexpecter | |
| 13 | |
| 14 # complication! separate sessions -> | |
| 15 # separate transactions !!!!! | |
| 16 # also: timeouts, other session failures | |
| 17 | |
| 18 class sqlpython(cmd.Cmd): | |
| 19 '''A python module to reproduce Oracle's command line with focus on customization and extention''' | |
| 20 | |
| 21 def __init__(self): | |
| 22 cmd.Cmd.__init__(self) | |
| 23 self.prompt = 'SQL.No_Connection> ' | |
| 24 self.maxfetch = 1000 | |
| 25 self.failoverSessions = [] | |
| 26 self.terminator = ';' | |
| 27 self.timeout = 30 | |
| 28 | |
| 29 def do_connect(self, arg): | |
| 30 '''Opens the DB connection''' | |
| 31 try: | |
| 32 if arg.find('/') == -1: | |
| 33 orapass = getpass.getpass('Password: ') | |
| 34 orauser = arg.split('@')[0] | |
| 35 oraserv = arg.split('@')[1] | |
| 36 self.orcl = cx_Oracle.connect(orauser,orapass,oraserv) | |
| 37 arg = '%s/%s@%s' % (orauser, orapass, oraserv) | |
| 38 else: | |
| 39 self.orcl = cx_Oracle.connect(arg) | |
| 40 self.curs = self.orcl.cursor() | |
| 41 self.prompt = 'SQL.'+self.orcl.tnsentry+'> ' | |
| 42 self.failoverSessions = [f for f in [fbs(arg) for fbs in pexpecter.available] if f.available] | |
| 43 | |
| 44 except Exception, e: | |
| 45 print e | |
| 46 | |
| 47 def emptyline(self): | |
| 48 pass | |
| 49 | |
| 50 def do_quit(self, arg): | |
| 51 return 1 | |
| 52 | |
| 53 def fail(self, arg, do_everywhere=False): | |
| 54 if self.failover: | |
| 55 success, result = False, '' | |
| 56 for fbs in self.failoverSessions: | |
| 57 success, result = fbs.attempt(arg) | |
| 58 if success: | |
| 59 print result | |
| 60 if not do_everywhere: | |
| 61 return True | |
| 62 print result | |
| 63 return False | |
| 64 | |
| 65 def designated_session(self, arg, sesstype): | |
| 66 for fbs in self.failoverSessions: | |
| 67 if fbs.valid and fbs.__class__ == sesstype: | |
| 68 success, result = fbs.attempt(arg) | |
| 69 print result | |
| 70 return | |
| 71 print 'Valid %s not found' % (sesstype.__name__) | |
| 72 | |
| 73 def do_terminators(self, arg): | |
| 74 """; standard Oracle format | |
| 75 \\c CSV (with headings) | |
| 76 \\C CSV (no headings) | |
| 77 \\g list | |
| 78 \\G aligned list | |
| 79 \\h HTML table | |
| 80 \\i INSERT statements | |
| 81 \\s CSV (with headings) | |
| 82 \\S CSV (no headings) | |
| 83 \\t transposed | |
| 84 \\x XML""" | |
| 85 print self.do_terminators.__doc__ | |
| 86 | |
| 87 terminatorSearchString = '|'.join('\\' + d.split()[0] for d in do_terminators.__doc__.splitlines()) | |
| 88 | |
| 89 def do_yasql(self, arg): | |
| 90 '''Sends a command to a YASQL session (http://sourceforge.net/projects/yasql/)''' | |
| 91 self.designated_session(arg, pexpecter.YASQLSession) | |
| 92 do_y = do_yasql | |
| 93 def do_sqlplus(self, arg): | |
| 94 '''Sends a command to a SQL*Plus session''' | |
| 95 self.designated_session(arg, pexpecter.SqlPlusSession) | |
| 96 do_sqlp = do_sqlplus | |
| 97 def do_senora(self, arg): | |
| 98 '''Sends a command to a Senora session (http://senora.sourceforge.net/)''' | |
| 99 self.designated_session(arg, pexpecter.SenoraSession) | |
| 100 do_sen = do_senora | |
| 101 | |
| 102 def default(self, arg, do_everywhere = False): | |
|
2
59903dcaf327
working on history
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
1
diff
changeset
|
103 self.query = finishStatement(arg) |
| 0 | 104 try: |
| 105 self.curs.execute(self.query) | |
| 106 print '\nExecuted\n' | |
| 107 if do_everywhere: | |
| 108 self.fail(arg, do_everywhere = True ) | |
| 109 except Exception, e: | |
| 110 result = self.fail(arg) | |
| 111 if not result: | |
| 112 print str(e) | |
| 113 | |
| 114 def do_commit(self, arg): | |
| 115 self.default('commit %s' % (arg), do_everywhere=True) | |
| 116 def do_rollback(self, arg): | |
| 117 self.default('rollback %s' % (arg), do_everywhere=True) | |
| 118 | |
| 119 # shortcuts | |
| 120 do_q = do_quit | |
| 121 do_exit = do_quit | |
| 122 | |
|
2
59903dcaf327
working on history
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
1
diff
changeset
|
123 stmtEndSearchString = r'(.*)(%s)\s*(\d+)?\s*$' % sqlpython.terminatorSearchString |
|
1
8fa146b9a2d7
reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
0
diff
changeset
|
124 stmtEndFinder = re.compile(stmtEndSearchString, re.MULTILINE | re.DOTALL) |
|
8fa146b9a2d7
reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
0
diff
changeset
|
125 prompt2 = ' > ' |
|
8fa146b9a2d7
reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
0
diff
changeset
|
126 |
|
8fa146b9a2d7
reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
0
diff
changeset
|
127 def finishStatement(firstline): |
|
8fa146b9a2d7
reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
0
diff
changeset
|
128 lines = [firstline] |
|
8fa146b9a2d7
reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
0
diff
changeset
|
129 while 1: |
|
8fa146b9a2d7
reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
0
diff
changeset
|
130 m = stmtEndFinder.search(lines[-1]) |
|
8fa146b9a2d7
reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
0
diff
changeset
|
131 if m: |
|
8fa146b9a2d7
reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
0
diff
changeset
|
132 return '\n'.join(lines) |
|
8fa146b9a2d7
reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
0
diff
changeset
|
133 lines.append(raw_input(prompt2)) |
|
8fa146b9a2d7
reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
0
diff
changeset
|
134 |
|
8fa146b9a2d7
reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
0
diff
changeset
|
135 def findTerminator(statement): |
|
8fa146b9a2d7
reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
0
diff
changeset
|
136 m = stmtEndFinder.search(statement) |
|
8fa146b9a2d7
reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
0
diff
changeset
|
137 return m.groups() |
| 0 | 138 |
| 139 def pmatrix(rows,desc,maxlen=30): | |
| 140 '''prints a matrix, used by sqlpython to print queries' result sets''' | |
| 141 names = [] | |
| 142 maxen = [] | |
| 143 toprint = [] | |
| 144 for d in desc: | |
| 145 n = d[0] | |
| 146 names.append(n) # list col names | |
| 147 maxen.append(len(n)) # col length | |
| 148 rcols = range(len(desc)) | |
| 149 rrows = range(len(rows)) | |
| 150 for i in rrows: # loops for all rows | |
| 151 rowsi = map(str, rows[i]) # current row to process | |
| 152 split = [] # service var is row split is needed | |
| 153 mustsplit = 0 # flag | |
| 154 for j in rcols: | |
| 155 if str(desc[j][1]) == "<type 'cx_Oracle.BINARY'>": # handles RAW columns | |
| 156 rowsi[j] = binascii.b2a_hex(rowsi[j]) | |
| 157 maxen[j] = max(maxen[j], len(rowsi[j])) # computes max field length | |
| 158 if maxen[j] <= maxlen: | |
| 159 split.append('') | |
| 160 else: # split the line is 2 because field is too long | |
| 161 mustsplit = 1 | |
| 162 maxen[j] = maxlen | |
| 163 split.append(rowsi[j][maxlen-1:2*maxlen-1]) | |
| 164 rowsi[j] = rowsi[j][0:maxlen-1] # this implem. truncates after maxlen*2 | |
| 165 toprint.append(rowsi) # 'toprint' is a printable copy of rows | |
| 166 if mustsplit != 0: | |
| 167 toprint.append(split) | |
| 168 sepcols = [] | |
| 169 for i in rcols: | |
| 170 maxcol = maxen[i] | |
| 171 name = names[i] | |
| 172 sepcols.append("-" * maxcol) # formats column names (header) | |
| 173 names[i] = name + (" " * (maxcol-len(name))) # formats separ line with -- | |
| 174 rrows2 = range(len(toprint)) | |
| 175 for j in rrows2: | |
| 176 val = toprint[j][i] | |
| 177 if str(desc[i][1]) == "<type 'cx_Oracle.NUMBER'>": # right align numbers | |
| 178 toprint[j][i] = (" " * (maxcol-len(val))) + val | |
| 179 else: | |
| 180 toprint[j][i] = val + (" " * (maxcol-len(val))) | |
| 181 for j in rrows2: | |
| 182 toprint[j] = ' '.join(toprint[j]) | |
| 183 names = ' '.join(names) | |
| 184 sepcols = ' '.join(sepcols) | |
| 185 toprint.insert(0, sepcols) | |
| 186 toprint.insert(0, names) | |
| 187 return '\n'.join(toprint) | |
| 188 |