Mercurial > sqlpython
annotate sqlpython/sqlpython.py @ 436:f441f2eb52e0
pickling gerald data
author | catherine@dellzilla |
---|---|
date | Thu, 28 Jan 2010 17:05:50 -0500 |
parents | cac7333f9ff5 |
children | 39a664a87c51 |
rev | line source |
---|---|
189 | 1 # |
368 | 2 # SqlPython V1.6.7 |
189 | 3 # Author: Luca.Canali@cern.ch, Apr 2006 |
380 | 4 # Rev 2-Sep-09 |
368 | 5 |
189 | 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 | |
415 | 11 import cmd2,getpass,binascii,re,os,platform |
426 | 12 import sqlalchemy, pyparsing, schemagroup, connections |
380 | 13 __version__ = '1.6.8' |
415 | 14 try: |
15 import cx_Oracle | |
16 except ImportError: | |
17 cx_Oracle = None | |
18 try: | |
19 import psycopg2 | |
20 except ImportError: | |
21 psycopg2 = None | |
189 | 22 |
339
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
23 class Parser(object): |
409
5b88ce5f31ff
ugh, trying to separate -- comments from --flags
catherine@DellZilla
parents:
407
diff
changeset
|
24 comment_def = "--" + pyparsing.NotAny('-' + pyparsing.CaselessKeyword('begin')) + pyparsing.ZeroOrMore(pyparsing.CharsNotIn("\n")) |
339
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
25 def __init__(self, scanner, retainSeparator=True): |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
26 self.scanner = scanner |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
27 self.scanner.ignore(pyparsing.sglQuotedString) |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
28 self.scanner.ignore(pyparsing.dblQuotedString) |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
29 self.scanner.ignore(self.comment_def) |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
30 self.scanner.ignore(pyparsing.cStyleComment) |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
31 self.retainSeparator = retainSeparator |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
32 def separate(self, txt): |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
33 itms = [] |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
34 for (sqlcommand, start, end) in self.scanner.scanString(txt): |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
35 if sqlcommand: |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
36 if type(sqlcommand[0]) == pyparsing.ParseResults: |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
37 if self.retainSeparator: |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
38 itms.append("".join(sqlcommand[0])) |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
39 else: |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
40 itms.append(sqlcommand[0][0]) |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
41 else: |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
42 if sqlcommand[0]: |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
43 itms.append(sqlcommand[0]) |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
44 return itms |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
45 |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
46 |
189 | 47 class sqlpython(cmd2.Cmd): |
48 '''A python module to reproduce Oracle's command line with focus on customization and extention''' | |
49 | |
50 def __init__(self): | |
51 cmd2.Cmd.__init__(self) | |
428 | 52 self.no_instance() |
189 | 53 self.maxfetch = 1000 |
54 self.terminator = ';' | |
55 self.timeout = 30 | |
56 self.commit_on_exit = True | |
428 | 57 self.instances = {} |
260 | 58 |
428 | 59 def no_instance(self): |
261 | 60 self.prompt = 'SQL.No_Connection> ' |
260 | 61 self.curs = None |
429 | 62 self.current_instance = None |
428 | 63 self.instance_number = None |
189 | 64 |
428 | 65 def make_instance_current(self, instance_number): |
66 db_instance = self.instances[instance_number] | |
429 | 67 self.prompt = db_instance.prompt |
68 self.rdbms = db_instance.rdbms | |
428 | 69 self.instance_number = instance_number |
429 | 70 self.curs = db_instance.connection.cursor() |
428 | 71 self.current_instance = db_instance |
317
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
72 |
428 | 73 def list_instances(self): |
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
74 self.stdout.write('Existing connections:\n') |
428 | 75 self.stdout.write('\n'.join('%s (%s)' % (v.prompt, v.rdbms) |
76 for (k,v) in sorted(self.instances.items())) + '\n') | |
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
77 |
260 | 78 def disconnect(self, arg): |
79 try: | |
428 | 80 instance_number = int(arg) |
81 instance = self.instances[instance_number] | |
261 | 82 except (ValueError, KeyError): |
428 | 83 self.list_instances() |
260 | 84 return |
85 if self.commit_on_exit: | |
428 | 86 instance.connection.commit() |
87 self.instances.pop(instance_number) | |
88 if instance_number == self.instance_number: | |
89 self.no_instance() | |
261 | 90 |
91 def closeall(self): | |
428 | 92 for instance_number in self.instances.keys(): |
93 self.disconnect(instance_number) | |
261 | 94 self.curs = None |
428 | 95 self.no_instance() |
261 | 96 |
407 | 97 legal_sql_word = pyparsing.Word(pyparsing.alphanums + '_$#') |
98 legal_hostname = pyparsing.Word(pyparsing.alphanums + '_-.')('host') + pyparsing.Optional( | |
99 ':' + pyparsing.Word(pyparsing.nums)('port')) | |
100 oracle_connect_parser = legal_sql_word('username') + ( | |
101 pyparsing.Optional('/' + pyparsing.CharsNotIn('@')("password")) + | |
102 pyparsing.Optional('@' + pyparsing.Optional(legal_hostname + '/') + | |
103 legal_sql_word('db_name')) + | |
104 pyparsing.Optional(pyparsing.CaselessKeyword('as') + | |
105 (pyparsing.CaselessKeyword('sysoper') ^ | |
106 pyparsing.CaselessKeyword('sysdba'))('mode'))) | |
423
146d64d2ad5e
beginning to explore use of Gerald Users
catherine@dellzilla
parents:
420
diff
changeset
|
107 postgresql_connect_parser = pyparsing.Optional(legal_sql_word('db_name') + |
146d64d2ad5e
beginning to explore use of Gerald Users
catherine@dellzilla
parents:
420
diff
changeset
|
108 pyparsing.Optional(legal_sql_word('username'))) |
407 | 109 |
429 | 110 def successfully_connect_to_number(self, arg): |
111 try: | |
112 instance_number = int(arg) | |
113 except ValueError: | |
114 return False | |
115 try: | |
116 self.make_instance_current(instance_number) | |
117 except IndexError: | |
118 self.list_instances() | |
119 return False | |
120 if (self.rdbms == 'oracle') and self.serveroutput: | |
121 self.curs.callproc('dbms_output.enable', []) | |
122 return True | |
123 | |
425 | 124 @cmd2.options([cmd2.make_option('-a', '--add', action='store_true', |
125 help='add connection (keep current connection)'), | |
126 cmd2.make_option('-c', '--close', action='store_true', | |
127 help='close connection {N} (or current)'), | |
128 cmd2.make_option('-C', '--closeall', action='store_true', | |
129 help='close all connections'), | |
130 cmd2.make_option('--postgres', action='store_true', help='Connect to postgreSQL: `sqlpython --postgres [DBNAME [USERNAME]]`'), | |
131 cmd2.make_option('--oracle', action='store_true', help='Connect to an Oracle database'), | |
132 cmd2.make_option('--mysql', action='store_true', help='Connect to a MySQL database'), | |
426 | 133 cmd2.make_option('-H', '--hostname', type='string', |
134 help='Machine where database is hosted (postgresql only)'), | |
425 | 135 cmd2.make_option('-p', '--port', type='int', |
136 help='Port to connect to (postgresql only)'), | |
426 | 137 cmd2.make_option('--password', type='string', |
138 help='Password (mysql only)'), | |
425 | 139 cmd2.make_option('-d', '--database', type='string', |
140 help='Database name to connect to'), | |
141 cmd2.make_option('-U', '--username', type='string', | |
142 help='Database user name to connect as') | |
143 ]) | |
426 | 144 def do_connect(self, arg, opts): |
425 | 145 |
146 '''Opens the DB connection''' | |
147 if opts.closeall: | |
148 self.closeall() | |
149 return | |
150 if opts.close: | |
151 if not arg: | |
428 | 152 arg = self.instance_number |
425 | 153 self.disconnect(arg) |
154 return | |
155 if (not arg) and (not opts.postgres): | |
428 | 156 self.list_instances() |
425 | 157 return |
158 if self.successfully_connect_to_number(arg): | |
159 return | |
160 | |
428 | 161 db_instance = connections.DatabaseInstance(arg, opts, default_rdbms = self.default_rdbms) |
162 if opts.add or (self.instance_number is None): | |
425 | 163 try: |
428 | 164 self.instance_number = max(self.instances.keys()) + 1 |
425 | 165 except ValueError: |
428 | 166 self.instance_number = 0 |
167 db_instance.set_instance_number(self.instance_number) | |
429 | 168 self.instances[self.instance_number] = db_instance |
428 | 169 self.make_instance_current(self.instance_number) |
425 | 170 if (self.rdbms == 'oracle') and self.serveroutput: |
431
cac7333f9ff5
beginning rework of threaded metadata discovery
catherine@dellzilla
parents:
430
diff
changeset
|
171 self.current_instance.connection.cursor().callproc('dbms_output.enable',[]) |
cac7333f9ff5
beginning rework of threaded metadata discovery
catherine@dellzilla
parents:
430
diff
changeset
|
172 #self.curs.callproc('dbms_output.enable', []) |
407 | 173 |
436 | 174 def do_pickle(self, arg): |
175 self.current_instance.pickle() | |
176 | |
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
177 def postparsing_precmd(self, statement): |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
178 stop = 0 |
428 | 179 self.saved_instance_number = None |
180 if statement.parsed.instance_number: | |
181 saved_instance_number = self.instance_number | |
258 | 182 try: |
428 | 183 if self.successfully_connect_to_number(statement.parsed.instance_number): |
335
00b183a103b3
bare prefix switches connection
Catherine Devlin <catherine.devlin@gmail.com>
parents:
324
diff
changeset
|
184 if statement.parsed.command: |
428 | 185 self.saved_instance_number = saved_instance_number |
261 | 186 except KeyError: |
428 | 187 self.list_instances() |
188 raise KeyError, 'No connection #%s' % statement.parsed.instance_number | |
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
189 return stop, statement |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
190 def postparsing_postcmd(self, stop): |
428 | 191 if self.saved_instance_number is not None: |
192 self.successfully_connect_to_number(self.saved_instance_number) | |
259
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
193 return stop |
c0847a4c7f49
one-shot connection changes
catherine@Elli.myhome.westell.com
parents:
258
diff
changeset
|
194 |
256 | 195 do_host = cmd2.Cmd.do_shell |
189 | 196 |
197 def emptyline(self): | |
198 pass | |
264
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
199 |
265
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
200 def _show_errors(self, all_users=False, limit=None, mintime=None, targets=[]): |
264
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
201 if all_users: |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
202 user = '' |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
203 else: |
265
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
204 user = "AND ao.owner = user\n" |
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
205 if targets: |
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
206 target = 'AND (%s)\n' % ' OR '.join("ae.type || '/' || ae.name LIKE '%s'" % |
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
207 t.upper().replace('*','%') for t in targets) |
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
208 else: |
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
209 target = '' |
264
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
210 self.curs.execute(''' |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
211 SELECT ae.owner, ae.name, ae.type, ae.position, ae.line, ae.attribute, |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
212 ae.text error_text, |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
213 src.text object_text, |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
214 ao.last_ddl_time |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
215 FROM all_errors ae |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
216 JOIN all_objects ao ON ( ae.owner = ao.owner |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
217 AND ae.name = ao.object_name |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
218 AND ae.type = ao.object_type) |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
219 JOIN all_source src ON ( ae.owner = src.owner |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
220 AND ae.name = src.name |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
221 AND ae.type = src.type |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
222 AND ae.line = src.line) |
265
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
223 WHERE 1=1 |
041c656dc8e5
show err working nicely now
catherine@Elli.myhome.westell.com
parents:
264
diff
changeset
|
224 %s%sORDER BY ao.last_ddl_time DESC''' % (user, target)) |
264
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
225 if limit is None: |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
226 errors = self.curs.fetchall() |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
227 else: |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
228 errors = self.curs.fetchmany(numRows = limit) |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
229 for err in errors: |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
230 if (mintime is not None) and (err[8] < mintime): |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
231 break |
333 | 232 self.poutput('%s at line %d of %s %s.%s:' % (err[5], err[4], err[2], err[0], err[1])) |
233 self.poutput(err[7]) | |
234 self.poutput((' ' * (err[3]-1)) + '^') | |
235 self.poutput(err[6]) | |
236 self.poutput('\n') | |
264
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
237 |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
238 def current_database_time(self): |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
239 self.curs.execute('select sysdate from dual') |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
240 return self.curs.fetchone()[0] |
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
241 |
189 | 242 def do_terminators(self, arg): |
243 """; standard Oracle format | |
244 \\c CSV (with headings) | |
245 \\C CSV (no headings) | |
246 \\g list | |
247 \\G aligned list | |
248 \\h HTML table | |
249 \\i INSERT statements | |
266 | 250 \\j JSON |
308 | 251 \\r ReStructured Text |
189 | 252 \\s CSV (with headings) |
253 \\S CSV (no headings) | |
254 \\t transposed | |
255 \\x XML | |
256 \\l line plot, with markers | |
257 \\L scatter plot (no lines) | |
258 \\b bar graph | |
259 \\p pie chart""" | |
333 | 260 self.poutput(self.do_terminators.__doc__) |
189 | 261 |
262 terminatorSearchString = '|'.join('\\' + d.split()[0] for d in do_terminators.__doc__.splitlines()) | |
263 | |
339
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
264 bindScanner = {'oracle': Parser(pyparsing.Literal(':') + pyparsing.Word( pyparsing.alphanums + "_$#" )), |
407 | 265 'postgres': Parser(pyparsing.Literal('%(') + legal_sql_word + ')s')} |
340
001d01eeac90
bind vars for postgres
Catherine Devlin <catherine.devlin@gmail.com>
parents:
339
diff
changeset
|
266 def findBinds(self, target, givenBindVars = {}): |
339
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
267 result = givenBindVars |
416
e7769bc81960
several changes to settables - incomplete
catherine@bothari
parents:
415
diff
changeset
|
268 #TODO: A consistent bind style? As a setting, perhaps? |
339
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
269 if self.rdbms in self.bindScanner: |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
270 for finding, startat, endat in self.bindScanner[self.rdbms].scanner.scanString(target): |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
271 varname = finding[1] |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
272 try: |
340
001d01eeac90
bind vars for postgres
Catherine Devlin <catherine.devlin@gmail.com>
parents:
339
diff
changeset
|
273 result[varname] = self.binds[varname] |
339
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
274 except KeyError: |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
275 if not givenBindVars.has_key(varname): |
420 | 276 raise KeyError, 'Bind variable "%s" not defined.' % (varname) |
339
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
277 return result |
545f63b6ef42
supports bind variables in postgresql
Catherine Devlin <catherine.devlin@gmail.com>
parents:
338
diff
changeset
|
278 |
189 | 279 def default(self, arg): |
340
001d01eeac90
bind vars for postgres
Catherine Devlin <catherine.devlin@gmail.com>
parents:
339
diff
changeset
|
280 self.varsUsed = self.findBinds(arg, givenBindVars={}) |
247 | 281 ending_args = arg.lower().split()[-2:] |
282 if 'end' in ending_args: | |
246 | 283 command = '%s %s;' |
284 else: | |
264
a8deaa38f11e
show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents:
263
diff
changeset
|
285 command = '%s %s' |
317
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
286 if self.rdbms == 'oracle': |
315 | 287 current_time = self.current_database_time() |
417 | 288 commandstring = command % (arg.parsed.command, arg.parsed.args) |
289 self.curs.execute(commandstring, self.varsUsed) | |
276
0b7031c2229e
autobind unit test failing
catherine@Elli.myhome.westell.com
parents:
266
diff
changeset
|
290 executionmessage = '\nExecuted%s\n' % ((self.curs.rowcount > 0) and ' (%d rows)' % self.curs.rowcount or '') |
317
f200a222a936
beginning to set up metadata.py
Catherine Devlin <catherine.devlin@gmail.com>
parents:
315
diff
changeset
|
291 if self.rdbms == 'oracle': |
315 | 292 self._show_errors(all_users=True, limit=1, mintime=current_time) |
333 | 293 self.pfeedback(executionmessage) |
198
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
294 |
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
295 def do_commit(self, arg=''): |
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
296 self.default(self.parsed('commit %s;' % (arg))) |
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
297 def do_rollback(self, arg=''): |
b2d8bf5f89db
merged with changes from work
catherine@Elli.myhome.westell.com
parents:
197
diff
changeset
|
298 self.default(self.parsed('rollback %s;' % (arg))) |
189 | 299 def do_quit(self, arg): |
261 | 300 if self.commit_on_exit: |
301 self.closeall() | |
189 | 302 return cmd2.Cmd.do_quit(self, None) |
303 do_exit = do_quit | |
304 do_q = do_quit | |
337 | 305 colorcodes = {'bold':{True:'\x1b[1m',False:'\x1b[22m'}, |
306 'red':{True:'\x1b[36m',False:'\x1b[39m'}, | |
307 'cyan':{True:'\x1b[31m',False:'\x1b[39m'}, | |
308 'underline':{True:'\x1b[4m',False:'\x1b[24m'}} | |
375
9d0a3ab7f573
set colors off for Windows
devlinjs@A0266D4FVTK81.wrightpatterson.afmc.ds.af.mil
parents:
374
diff
changeset
|
309 colors = (platform.system() != 'Windows') |
337 | 310 def colorize(self, val, color): |
311 if self.colors and (self.stdout == self.initial_stdout): | |
312 if color not in self.colorcodes: | |
313 if (color % 2): | |
314 color = 'red' | |
315 else: | |
316 color = 'cyan' | |
317 return self.colorcodes[color][True] + val + self.colorcodes[color][False] | |
318 return val | |
400 | 319 def pmatrix(self,rows,maxlen=30,heading=True,restructuredtext=False): |
337 | 320 '''prints a matrix, used by sqlpython to print queries' result sets''' |
400 | 321 names = self.colnames |
322 maxen = [len(n) for n in self.colnames] | |
337 | 323 toprint = [] |
400 | 324 rcols = range(len(self.colnames)) |
337 | 325 rrows = range(len(rows)) |
326 for i in rrows: # loops for all rows | |
327 rowsi = map(str, rows[i]) # current row to process | |
328 split = [] # service var is row split is needed | |
329 mustsplit = 0 # flag | |
330 for j in rcols: | |
400 | 331 if str(self.coltypes[j]) == "<type 'cx_Oracle.BINARY'>": # handles RAW columns |
337 | 332 rowsi[j] = binascii.b2a_hex(rowsi[j]) |
333 maxen[j] = max(maxen[j], len(rowsi[j])) # computes max field length | |
334 if maxen[j] <= maxlen: | |
335 split.append('') | |
336 else: # split the line is 2 because field is too long | |
337 mustsplit = 1 | |
338 maxen[j] = maxlen | |
339 split.append(rowsi[j][maxlen-1:2*maxlen-1]) | |
340 rowsi[j] = rowsi[j][0:maxlen-1] # this implem. truncates after maxlen*2 | |
341 toprint.append(rowsi) # 'toprint' is a printable copy of rows | |
342 if mustsplit != 0: | |
343 toprint.append(split) | |
344 sepcols = [] | |
345 for i in rcols: | |
346 maxcol = maxen[i] | |
347 name = names[i] | |
348 sepcols.append("-" * maxcol) # formats column names (header) | |
349 names[i] = name + (" " * (maxcol-len(name))) # formats separ line with -- | |
350 rrows2 = range(len(toprint)) | |
351 for j in rrows2: | |
352 val = toprint[j][i] | |
400 | 353 if str(self.coltypes[i]) == "<type 'cx_Oracle.NUMBER'>": # right align numbers - but must generalize! |
337 | 354 toprint[j][i] = (" " * (maxcol-len(val))) + val |
355 else: | |
356 toprint[j][i] = val + (" " * (maxcol-len(val))) | |
357 toprint[j][i] = self.colorize(toprint[j][i], i) | |
358 for j in rrows2: | |
359 toprint[j] = ' '.join(toprint[j]) | |
360 names = [self.colorize(name, n) for (n, name) in enumerate(names)] | |
361 names = ' '.join(names) | |
362 names = self.colorize(names, 'bold') | |
363 sepcols = ' '.join(sepcols) | |
364 if heading or restructuredtext: | |
365 toprint.insert(0, sepcols) | |
366 toprint.insert(0, names) | |
367 if restructuredtext: | |
368 toprint.insert(0, sepcols) | |
369 toprint.append(sepcols) | |
370 return '\n'.join(toprint) | |
189 | 371 |
337 | 372 |
373 | |
374 | |
189 | 375 |