Mercurial > sqlpython
annotate sqlpython/mysqlpy.py @ 348:c652478be4fd
migrated \dt to gerald
| author | catherine@cordelia |
|---|---|
| date | Fri, 24 Apr 2009 15:09:29 -0400 |
| parents | 8fbf49d3abe8 |
| children |
| rev | line source |
|---|---|
| 189 | 1 #!/usr/bin/python |
|
324
9cbea1d8872e
version 1.6.4, works with cmd2 0.5.2 for more flexible testing
Catherine Devlin <catherine.devlin@gmail.com>
parents:
323
diff
changeset
|
2 # MySqlPy V1.6.4 |
| 189 | 3 # Author: Luca.Canali@cern.ch |
| 4 # | |
| 5 # | |
| 6 # Companion of SqlPython, a python module that reproduces Oracle's command line within python | |
| 7 # 'sqlplus inside python' | |
| 8 # See also: http://twiki.cern.ch/twiki/bin/view/PSSGroup/SqlPython | |
| 9 # http://catherine.devlin.googlepages.com/ | |
| 10 | |
| 11 from sqlpyPlus import * | |
| 206 | 12 import sys, tempfile, optparse, unittest |
|
323
b97f1b8cdecd
added usage notes for transcript testing
Catherine Devlin <catherine.devlin@gmail.com>
parents:
320
diff
changeset
|
13 import sqlalchemy |
| 189 | 14 |
| 15 class mysqlpy(sqlpyPlus): | |
| 16 ''' | |
|
324
9cbea1d8872e
version 1.6.4, works with cmd2 0.5.2 for more flexible testing
Catherine Devlin <catherine.devlin@gmail.com>
parents:
323
diff
changeset
|
17 MySqlPy V1.6.4 - 'sqlplus in python' |
| 189 | 18 Author: Luca.Canali@cern.ch |
|
324
9cbea1d8872e
version 1.6.4, works with cmd2 0.5.2 for more flexible testing
Catherine Devlin <catherine.devlin@gmail.com>
parents:
323
diff
changeset
|
19 Rev: 1.6.4, 03-Apr-09 |
| 189 | 20 |
| 21 Companion of SqlPython, a python module that reproduces Oracle's command line within python | |
| 22 and sqlpyPlus. Major contributions by Catherine Devlin, http://catherinedevlin.blogspot.com | |
| 23 | |
| 24 Usage: sqlpython [connect string] [single-word command] ["multi-word command"]... | |
| 25 | |
| 26 Quick start command list: | |
| 27 | |
| 28 - top -> executes a query to list all active sessions in (Oracle 10g and RAC) | |
| 29 (use: instance activity monitoring, a DBA tool) | |
| 30 - tselect -> prints the result set in trasposed form, useful to print result sets with | |
| 31 many columns such as dba_ or v$ views (ex: dba_tables or v$instance) | |
| 32 - py -> execute a python command (C.D.) | |
| 33 - db -> quick connect using credentials in pass.txt file | |
| 34 (Ex: write username and pass in pass.txt and then "db db_alias" to connect) | |
| 35 - sql -> prints the sql text from the cache. parameter: sql_id of the statement | |
| 36 (Ex: sql fzqa1qj65nagki) | |
| 37 - explain -> prints the execution plan from the cache. parameter: sql_id of the statement | |
| 38 - sessinfo-> prints session information. 1 parameter sid (Ex: sql 101 print info for sid 101) | |
| 39 - longops -> prints from gv$session_longops (running full scans, etc) | |
| 40 - load -> prints the OS load on all cluster nodes (10g RAC) | |
| 41 - sleect,slect -> alias for select (I mistyped select this way too many times...) | |
| 42 - top9i -> 9i (and single instance) version of top | |
| 43 - describe, @, !, spool, show, set, list, get, write -> sql*plus-like, from sqlpyPlus (C.D.) | |
| 44 - shortcuts: \c (connect), \d (describe), etc, from sqlpyPlus (C.D.) | |
| 45 - :myvarname = xx, set autobind 1, print -> bind variables management extension, to sqlplus (C.D.) | |
| 46 | |
| 47 Example: | |
| 48 SQL> connect username@dbalias or username/pass@dbalias | |
| 49 SQL> select sysdate from dual; | |
| 50 SQL> exit | |
| 51 ''' | |
| 52 | |
| 53 def __init__(self): | |
| 54 sqlpyPlus.__init__(self) | |
| 55 self.maxtselctrows = 10 | |
| 56 self.query_load10g = ''' | |
| 57 ins.instance_name,ins.host_name,round(os.value,2) load | |
| 58 from gv$osstat os, gv$instance ins | |
| 59 where os.inst_id=ins.inst_id and os.stat_name='LOAD' | |
| 60 order by 3 desc | |
| 61 ''' | |
| 62 self.query_top9i = '''SELECT | |
| 63 sid,username,osuser||'@'||terminal "Server User@terminal",program,taddr, status, | |
| 64 module, sql_hash_value hash, fixed_table_sequence seq, last_call_et elaps | |
| 65 from v$session | |
| 66 where username is not null and program not like 'emagent%' and status='ACTIVE' | |
| 67 and audsid !=sys_context('USERENV','SESSIONID') ; | |
| 68 ''' | |
| 69 self.query_ractop = '''SELECT | |
| 70 inst_id||'_'||sid inst_sid,username,osuser||'@'||terminal "User@Term",program, decode(taddr,null,null,'NN') tr, | |
| 71 sql_id, '.'||mod(fixed_table_sequence,1000) seq, state||': '||event event, | |
| 72 case state when 'WAITING' then seconds_in_wait else wait_time end w_tim, last_call_et elaps | |
| 73 from gv$session | |
| 74 where status='ACTIVE' and username is not null | |
| 75 and not (event like '% waiting for messages in the queue' and state='WAITING') | |
| 76 and audsid !=sys_context('USERENV','SESSIONID'); | |
| 77 ''' | |
| 78 self.query_longops = '''SELECT | |
| 79 inst_id,sid,username,time_remaining remaining, elapsed_seconds elapsed, sql_hash_value hash, opname,message | |
| 80 from gv$session_longops | |
| 81 where time_remaining>0; | |
| 82 ''' | |
| 83 | |
| 84 def do_new(self, args): | |
| 85 'tells you about new objects' | |
| 86 self.onecmd('''SELECT owner, | |
| 87 object_name, | |
| 88 object_type | |
| 89 FROM all_objects | |
| 90 WHERE created > SYSDATE - 7;''') | |
| 91 def do_top9i(self,args): | |
| 92 '''Runs query_top9i defined above, to display active sessions in Oracle 9i''' | |
| 93 self.onecmd(self.query_top9i) | |
| 94 | |
| 95 def do_top(self,args): | |
| 96 '''Runs query_ractop defined above, to display active sessions in Oracle 10g (and RAC)''' | |
| 97 self.onecmd(self.query_ractop) | |
| 98 | |
| 99 def do_longops(self,args): | |
| 100 '''Runs query_longops defined above, to display long running operations (full scans, etc)''' | |
| 101 self.onecmd(self.query_longops) | |
| 284 | 102 |
| 189 | 103 def do_load(self,args): |
| 104 '''Runs query_load10g defined above, to display OS load on cluster nodes (10gRAC) | |
| 105 Do not confuse with `GET myfile.sql` and `@myfile.sql`, | |
| 106 which get and run SQL scripts from disk.''' | |
| 208 | 107 self.onecmd(self.query_load10g) |
|
257
6d4d90fb2082
dbms_output.put_line working
catherine@Elli.myhome.westell.com
parents:
255
diff
changeset
|
108 |
| 189 | 109 def do_db(self,args,filepath='pass.txt'): |
| 110 '''Exec do_connect to db_alias in args (credentials form the file pass.txt) ''' | |
|
255
39ecc4b65b11
smart prompt now works with db connect
catherine@Elli.myhome.westell.com
parents:
252
diff
changeset
|
111 try: |
|
39ecc4b65b11
smart prompt now works with db connect
catherine@Elli.myhome.westell.com
parents:
252
diff
changeset
|
112 f = open(filepath,'r') |
|
39ecc4b65b11
smart prompt now works with db connect
catherine@Elli.myhome.westell.com
parents:
252
diff
changeset
|
113 except IOError: |
| 333 | 114 self.perror('Need a file %s containing username/password' % filepath) |
|
255
39ecc4b65b11
smart prompt now works with db connect
catherine@Elli.myhome.westell.com
parents:
252
diff
changeset
|
115 raise |
|
39ecc4b65b11
smart prompt now works with db connect
catherine@Elli.myhome.westell.com
parents:
252
diff
changeset
|
116 connectstr = f.readline().strip() |
|
39ecc4b65b11
smart prompt now works with db connect
catherine@Elli.myhome.westell.com
parents:
252
diff
changeset
|
117 if args: |
|
39ecc4b65b11
smart prompt now works with db connect
catherine@Elli.myhome.westell.com
parents:
252
diff
changeset
|
118 connectstr += '@'+args |
| 189 | 119 self.do_connect(connectstr) |
| 120 f.close() | |
| 121 | |
| 122 def do_tselect(self, arg): | |
| 281 | 123 ''' |
| 124 Executes a query and prints the result in trasposed form; | |
| 125 equivalent to terminating query with `\\t` instead of `;`. | |
| 126 Useful when querying tables with many columns and few rows.''' | |
| 206 | 127 self.do_select(self.parsed(arg, terminator='\\t')) |
| 189 | 128 |
| 129 def do_sql(self,args): | |
| 130 '''prints sql statement give the sql_id (Oracle 10gR2)''' | |
| 131 self.query = "select inst_id, sql_fulltext from gv$sqlstats where sql_id='"+args+"'" | |
| 132 try: | |
| 133 self.curs.execute(self.query) | |
| 134 row = self.curs.fetchone() | |
| 333 | 135 self.poutput("\nSQL statement from cache") |
| 136 self.poutput("------------------------\n") | |
| 189 | 137 while row: |
| 333 | 138 self.poutput("\nINST_ID = "+str(row[0])+" - SQL TEXT:\n" + row[1].read()) |
| 189 | 139 row = self.curs.next() |
| 140 except Exception, e: | |
| 333 | 141 self.perror(e) |
| 189 | 142 |
| 143 def do_explain(self,args): | |
| 144 '''prints the plan of a given statement from the sql cache. 1 parameter: sql_id, see also do_sql ''' | |
|
320
af4106fca5d9
fixed misspelling of split(), thanks Igor
Catherine Devlin <catherine.devlin@gmail.com>
parents:
313
diff
changeset
|
145 words = args.split() |
|
251
aa33f495a289
reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents:
250
diff
changeset
|
146 if len(words) > 2 and words[0].lower() == 'plan' and words[1].lower() == 'for': |
|
aa33f495a289
reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents:
250
diff
changeset
|
147 self.curs.execute('explain %s' % args) |
| 333 | 148 self.pfeedback('Explained. (see plan table)') |
| 226 | 149 return |
| 189 | 150 self.query = "select * from table(dbms_xplan.display_cursor('"+args+"'))" |
| 151 try: | |
| 152 self.curs.execute(self.query) | |
| 153 rows = self.curs.fetchall() | |
| 154 desc = self.curs.description | |
| 155 self.rc = self.curs.rowcount | |
| 156 if self.rc > 0: | |
| 337 | 157 self.poutput('\n' + self.pmatrix(rows,desc,200)) |
| 189 | 158 except Exception, e: |
| 333 | 159 self.perror(e) |
| 189 | 160 |
| 161 def do_sessinfo(self,args): | |
| 206 | 162 '''Reports session info for the given sid, extended to RAC with gv$''' |
| 163 try: | |
| 164 if not args: | |
| 165 self.curs.execute('SELECT sid FROM v$mystat') | |
| 166 args = self.curs.fetchone()[0] | |
| 167 self.onecmd('SELECT * from gv$session where sid=%s\\t' % args) | |
| 168 except cx_Oracle.DatabaseError, e: | |
| 169 if 'table or view does not exist' in str(e): | |
| 333 | 170 self.perror('This account has not been granted SELECT privileges to v$mystat or gv$session.') |
| 206 | 171 else: |
| 172 raise | |
| 189 | 173 |
| 174 def do_sleect(self,args): | |
| 175 '''implements sleect = select, a common typo''' | |
| 176 self.do_select(args) | |
| 177 | |
| 178 do_slect = do_sleect | |
| 179 | |
| 180 def run(): | |
| 181 my=mysqlpy() | |
| 182 print my.__doc__ | |
| 183 try: | |
| 184 if sys.argv[1][0] != '@': | |
| 185 connectstring = sys.argv.pop(1) | |
| 348 | 186 if len(sys.argv) >= 3 and sys.argv[1].lower() == 'as': # attach AS SYSDBA or AS SYSOPER if present |
| 187 for i in (1,2): | |
| 188 connectstring += ' ' + sys.argv.pop(1) | |
| 189 | 189 my.do_connect(connectstring) |
| 190 for arg in sys.argv[1:]: | |
| 292 | 191 if my.onecmd(arg + '\n') == my._STOP_AND_EXIT: |
| 189 | 192 return |
| 193 except IndexError: | |
| 194 pass | |
| 195 my.cmdloop() | |
|
323
b97f1b8cdecd
added usage notes for transcript testing
Catherine Devlin <catherine.devlin@gmail.com>
parents:
320
diff
changeset
|
196 |
| 189 | 197 class TestCase(Cmd2TestCase): |
| 198 CmdApp = mysqlpy | |
| 199 | |
| 200 if __name__ == '__main__': | |
| 201 parser = optparse.OptionParser() | |
| 202 parser.add_option('-t', '--test', dest='unittests', action='store_true', default=False, help='Run unit test suite') | |
| 203 (callopts, callargs) = parser.parse_args() | |
| 204 if callopts.unittests: | |
|
323
b97f1b8cdecd
added usage notes for transcript testing
Catherine Devlin <catherine.devlin@gmail.com>
parents:
320
diff
changeset
|
205 mysqlpy.testfiles = callargs |
| 189 | 206 sys.argv = [sys.argv[0]] # the --test argument upsets unittest.main() |
| 207 unittest.main() | |
| 208 else: | |
| 209 run() |