Mercurial > sqlpython
annotate sqlpython.py @ 147:a2731c87b837
lists to genshi
| author | catherine@Elli.myhome.westell.com |
|---|---|
| date | Thu, 25 Sep 2008 16:20:08 -0400 |
| parents | d0e071e19c26 |
| children | 3b1e25cc0e38 |
| rev | line source |
|---|---|
| 0 | 1 # |
| 126 | 2 # SqlPython V1.4.7 |
| 0 | 3 # Author: Luca.Canali@cern.ch, Apr 2006 |
| 103 | 4 # Rev 29-May-08 |
| 0 | 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 | |
| 102 | 11 import cmd2,getpass,binascii,cx_Oracle,re,os |
| 80 | 12 import pexpecter, sqlpyPlus |
| 0 | 13 |
| 14 # complication! separate sessions -> | |
| 15 # separate transactions !!!!! | |
| 16 # also: timeouts, other session failures | |
|
40
1fb9f7dee7d8
tearing out cmd2
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
16
diff
changeset
|
17 |
|
16
2776755a3a7e
beginning separation of cmd2
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
9
diff
changeset
|
18 class sqlpython(cmd2.Cmd): |
| 0 | 19 '''A python module to reproduce Oracle's command line with focus on customization and extention''' |
| 20 | |
| 21 def __init__(self): | |
|
16
2776755a3a7e
beginning separation of cmd2
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
9
diff
changeset
|
22 cmd2.Cmd.__init__(self) |
| 0 | 23 self.prompt = 'SQL.No_Connection> ' |
| 24 self.maxfetch = 1000 | |
| 25 self.failoverSessions = [] | |
| 26 self.terminator = ';' | |
| 27 self.timeout = 30 | |
|
123
898ed97bec38
fixed bug in setting parameters
catherine@Elli.myhome.westell.com
parents:
120
diff
changeset
|
28 self.commit_on_exit = True |
| 102 | 29 |
| 30 connection_modes = {re.compile(' AS SYSDBA', re.IGNORECASE): cx_Oracle.SYSDBA, | |
| 31 re.compile(' AS SYSOPER', re.IGNORECASE): cx_Oracle.SYSOPER} | |
| 0 | 32 def do_connect(self, arg): |
| 33 '''Opens the DB connection''' | |
| 102 | 34 modeval = 0 |
| 35 for modere, modevalue in self.connection_modes.items(): | |
| 36 if modere.search(arg): | |
| 37 arg = modere.sub('', arg) | |
| 38 modeval = modevalue | |
| 0 | 39 try: |
| 102 | 40 orauser, oraserv = arg.split('@') |
| 41 except ValueError: | |
| 42 try: | |
| 43 oraserv = os.environ['ORACLE_SID'] | |
| 44 except KeyError: | |
| 45 print 'instance not specified and environment variable ORACLE_SID not set' | |
| 46 return | |
| 47 orauser = arg | |
| 48 sid = oraserv | |
| 49 try: | |
| 50 host, sid = oraserv.split('/') | |
| 51 try: | |
| 52 host, port = host.split(':') | |
| 53 port = int(port) | |
| 54 except ValueError: | |
| 55 port = 1521 | |
| 56 oraserv = cx_Oracle.makedsn(host, port, sid) | |
| 57 except ValueError: | |
| 58 pass | |
| 59 try: | |
| 60 orauser, orapass = orauser.split('/') | |
| 61 except ValueError: | |
| 62 orapass = getpass.getpass('Password: ') | |
| 63 if orauser.upper() == 'SYS' and not modeval: | |
| 64 print 'Privilege not specified for SYS, assuming SYSOPER' | |
| 65 modeval = cx_Oracle.SYSOPER | |
| 66 try: | |
| 67 self.orcl = cx_Oracle.connect(orauser,orapass,oraserv,modeval) | |
| 0 | 68 self.curs = self.orcl.cursor() |
| 102 | 69 self.prompt = '%s@%s> ' % (orauser, sid) |
| 0 | 70 self.failoverSessions = [f for f in [fbs(arg) for fbs in pexpecter.available] if f.available] |
| 71 except Exception, e: | |
| 72 print e | |
| 102 | 73 |
| 0 | 74 |
| 75 def emptyline(self): | |
| 76 pass | |
| 77 | |
| 78 def fail(self, arg, do_everywhere=False): | |
| 79 if self.failover: | |
| 80 success, result = False, '' | |
| 81 for fbs in self.failoverSessions: | |
| 82 success, result = fbs.attempt(arg) | |
| 83 if success: | |
| 84 print result | |
| 85 if not do_everywhere: | |
| 86 return True | |
| 87 print result | |
| 88 return False | |
| 89 | |
| 90 def designated_session(self, arg, sesstype): | |
| 91 for fbs in self.failoverSessions: | |
| 92 if fbs.valid and fbs.__class__ == sesstype: | |
| 93 success, result = fbs.attempt(arg) | |
| 94 print result | |
| 95 return | |
| 96 print 'Valid %s not found' % (sesstype.__name__) | |
| 97 | |
| 98 def do_terminators(self, arg): | |
| 99 """; standard Oracle format | |
| 100 \\c CSV (with headings) | |
| 101 \\C CSV (no headings) | |
| 102 \\g list | |
| 103 \\G aligned list | |
| 104 \\h HTML table | |
| 105 \\i INSERT statements | |
| 106 \\s CSV (with headings) | |
| 107 \\S CSV (no headings) | |
| 108 \\t transposed | |
| 109 \\x XML""" | |
| 110 print self.do_terminators.__doc__ | |
| 111 | |
| 112 terminatorSearchString = '|'.join('\\' + d.split()[0] for d in do_terminators.__doc__.splitlines()) | |
| 113 | |
| 114 def do_yasql(self, arg): | |
| 115 '''Sends a command to a YASQL session (http://sourceforge.net/projects/yasql/)''' | |
| 116 self.designated_session(arg, pexpecter.YASQLSession) | |
| 117 do_y = do_yasql | |
| 118 def do_sqlplus(self, arg): | |
| 119 '''Sends a command to a SQL*Plus session''' | |
| 120 self.designated_session(arg, pexpecter.SqlPlusSession) | |
| 121 do_sqlp = do_sqlplus | |
| 122 def do_senora(self, arg): | |
| 123 '''Sends a command to a Senora session (http://senora.sourceforge.net/)''' | |
| 124 self.designated_session(arg, pexpecter.SenoraSession) | |
| 125 do_sen = do_senora | |
|
9
f70cc3be6377
reporting rowcount
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
3
diff
changeset
|
126 |
| 0 | 127 def default(self, arg, do_everywhere = False): |
| 117 | 128 statement = self.parsed(arg) |
| 129 self.query = statement.unterminated | |
| 0 | 130 try: |
| 80 | 131 self.varsUsed = sqlpyPlus.findBinds(self.query, self.binds, givenBindVars={}) |
| 132 self.curs.execute(self.query, self.varsUsed) | |
|
9
f70cc3be6377
reporting rowcount
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
3
diff
changeset
|
133 print '\nExecuted%s\n' % ((self.curs.rowcount > 0) and ' (%d rows)' % self.curs.rowcount or '') |
| 0 | 134 if do_everywhere: |
| 135 self.fail(arg, do_everywhere = True ) | |
| 136 except Exception, e: | |
| 137 result = self.fail(arg) | |
| 138 if not result: | |
| 139 print str(e) | |
| 140 | |
| 141 def do_commit(self, arg): | |
|
67
d0bf9e40ba8d
eliminate double-semicolon on commit
catherine@DellZilla.myhome.westell.com
parents:
55
diff
changeset
|
142 self.default('commit %s;' % (arg), do_everywhere=True) |
| 0 | 143 def do_rollback(self, arg): |
|
67
d0bf9e40ba8d
eliminate double-semicolon on commit
catherine@DellZilla.myhome.westell.com
parents:
55
diff
changeset
|
144 self.default('rollback %s;' % (arg), do_everywhere=True) |
|
123
898ed97bec38
fixed bug in setting parameters
catherine@Elli.myhome.westell.com
parents:
120
diff
changeset
|
145 def do_quit(self, arg): |
| 124 | 146 if self.commit_on_exit and hasattr(self, 'curs'): |
|
123
898ed97bec38
fixed bug in setting parameters
catherine@Elli.myhome.westell.com
parents:
120
diff
changeset
|
147 self.default('commit;') |
|
898ed97bec38
fixed bug in setting parameters
catherine@Elli.myhome.westell.com
parents:
120
diff
changeset
|
148 cmd2.Cmd.do_quit() |
|
898ed97bec38
fixed bug in setting parameters
catherine@Elli.myhome.westell.com
parents:
120
diff
changeset
|
149 do_exit = do_quit |
|
898ed97bec38
fixed bug in setting parameters
catherine@Elli.myhome.westell.com
parents:
120
diff
changeset
|
150 do_q = do_quit |
| 0 | 151 |
| 152 def pmatrix(rows,desc,maxlen=30): | |
| 153 '''prints a matrix, used by sqlpython to print queries' result sets''' | |
| 154 names = [] | |
| 155 maxen = [] | |
| 156 toprint = [] | |
| 157 for d in desc: | |
| 158 n = d[0] | |
| 159 names.append(n) # list col names | |
| 160 maxen.append(len(n)) # col length | |
| 161 rcols = range(len(desc)) | |
| 162 rrows = range(len(rows)) | |
| 163 for i in rrows: # loops for all rows | |
| 164 rowsi = map(str, rows[i]) # current row to process | |
| 165 split = [] # service var is row split is needed | |
| 166 mustsplit = 0 # flag | |
| 167 for j in rcols: | |
| 168 if str(desc[j][1]) == "<type 'cx_Oracle.BINARY'>": # handles RAW columns | |
| 80 | 169 rowsi[j] = binascii.b2a_hex(rowsi[j]) |
| 0 | 170 maxen[j] = max(maxen[j], len(rowsi[j])) # computes max field length |
| 171 if maxen[j] <= maxlen: | |
| 172 split.append('') | |
| 173 else: # split the line is 2 because field is too long | |
| 174 mustsplit = 1 | |
| 175 maxen[j] = maxlen | |
| 176 split.append(rowsi[j][maxlen-1:2*maxlen-1]) | |
| 177 rowsi[j] = rowsi[j][0:maxlen-1] # this implem. truncates after maxlen*2 | |
| 178 toprint.append(rowsi) # 'toprint' is a printable copy of rows | |
| 179 if mustsplit != 0: | |
| 180 toprint.append(split) | |
| 181 sepcols = [] | |
| 182 for i in rcols: | |
| 183 maxcol = maxen[i] | |
| 184 name = names[i] | |
| 185 sepcols.append("-" * maxcol) # formats column names (header) | |
| 186 names[i] = name + (" " * (maxcol-len(name))) # formats separ line with -- | |
| 187 rrows2 = range(len(toprint)) | |
| 188 for j in rrows2: | |
| 189 val = toprint[j][i] | |
| 190 if str(desc[i][1]) == "<type 'cx_Oracle.NUMBER'>": # right align numbers | |
| 191 toprint[j][i] = (" " * (maxcol-len(val))) + val | |
| 192 else: | |
| 193 toprint[j][i] = val + (" " * (maxcol-len(val))) | |
| 194 for j in rrows2: | |
| 195 toprint[j] = ' '.join(toprint[j]) | |
| 196 names = ' '.join(names) | |
| 197 sepcols = ' '.join(sepcols) | |
| 198 toprint.insert(0, sepcols) | |
| 199 toprint.insert(0, names) | |
| 200 return '\n'.join(toprint) | |
| 201 |