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