Mercurial > sqlpython
annotate mysqlpy.py @ 140:58d15cb69f72
fixed Luca's old queries with new SELECT parsing
| author | catherine@Elli.myhome.westell.com |
|---|---|
| date | Wed, 03 Sep 2008 17:27:28 -0400 |
| parents | 4aa28dffe658 |
| children | 24b5daa7ebe1 |
| rev | line source |
|---|---|
| 0 | 1 #!/usr/bin/python |
| 103 | 2 # MySqlPy V1.4.6 |
| 0 | 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 * | |
| 92 | 12 import binascii, sys, tempfile |
| 0 | 13 |
| 14 class mysqlpy(sqlpyPlus): | |
| 15 ''' | |
| 103 | 16 MySqlPy V1.4.6 - 'sqlplus in python' |
| 0 | 17 Author: Luca.Canali@cern.ch |
| 103 | 18 Rev: 1.4.8, 29-May-08 |
| 0 | 19 |
| 20 Companion of SqlPython, a python module that reproduces Oracle's command line within python | |
| 21 and sqlpyPlus. Major contributions by Catherine Devlin, http://catherinedevlin.blogspot.com | |
| 94 | 22 |
| 23 Usage: sqlpython [connect string] [single-word command] ["multi-word command"]... | |
| 24 | |
| 0 | 25 Quick start command list: |
| 26 | |
| 27 - top -> executes a query to list all active sessions in (Oracle 10g and RAC) | |
| 28 (use: instance activity monitoring, a DBA tool) | |
| 29 - tselect -> prints the result set in trasposed form, useful to print result sets with | |
| 30 many columns such as dba_ or v$ views (ex: dba_tables or v$instance) | |
| 31 - py -> execute a python command (C.D.) | |
| 32 - db -> quick connect using credentials in pass.txt file | |
| 33 (Ex: write username and pass in pass.txt and then "db db_alias" to connect) | |
| 34 - sql -> prints the sql text from the cache. parameter: sql_id of the statement | |
| 35 (Ex: sql fzqa1qj65nagki) | |
| 36 - explain -> prints the execution plan from the cache. parameter: sql_id of the statement | |
| 37 - sessinfo-> prints session information. 1 parameter sid (Ex: sql 101 print info for sid 101) | |
| 38 - longops -> prints from gv$session_longops (running full scans, etc) | |
| 39 - load -> prints the OS load on all cluster nodes (10g RAC) | |
| 40 - sleect,slect -> alias for select (I mistyped select this way too many times...) | |
| 41 - top9i -> 9i (and single instance) version of top | |
| 42 - describe, @, !, spool, show, set, list, get, write -> sql*plus-like, from sqlpyPlus (C.D.) | |
| 43 - shortcuts: \c (connect), \d (describe), etc, from sqlpyPlus (C.D.) | |
| 44 - :myvarname = xx, set autobind 1, print -> bind variables management extension, to sqlplus (C.D.) | |
| 45 | |
| 46 Example: | |
| 47 SQL> connect username@dbalias or username/pass@dbalias | |
| 48 SQL> select sysdate from dual; | |
| 49 SQL> exit | |
| 50 ''' | |
| 51 | |
| 52 def __init__(self): | |
| 53 sqlpyPlus.__init__(self) | |
| 54 self.maxtselctrows = 10 | |
| 55 self.query_load10g = ''' | |
| 56 ins.instance_name,ins.host_name,round(os.value,2) load | |
| 57 from gv$osstat os, gv$instance ins | |
| 58 where os.inst_id=ins.inst_id and os.stat_name='LOAD' | |
|
140
58d15cb69f72
fixed Luca's old queries with new SELECT parsing
catherine@Elli.myhome.westell.com
parents:
103
diff
changeset
|
59 order by 3 desc |
| 0 | 60 ''' |
|
140
58d15cb69f72
fixed Luca's old queries with new SELECT parsing
catherine@Elli.myhome.westell.com
parents:
103
diff
changeset
|
61 self.query_top9i = '''SELECT |
| 0 | 62 sid,username,osuser||'@'||terminal "Server User@terminal",program,taddr, status, |
| 63 module, sql_hash_value hash, fixed_table_sequence seq, last_call_et elaps | |
| 64 from v$session | |
| 65 where username is not null and program not like 'emagent%' and status='ACTIVE' | |
|
140
58d15cb69f72
fixed Luca's old queries with new SELECT parsing
catherine@Elli.myhome.westell.com
parents:
103
diff
changeset
|
66 and audsid !=sys_context('USERENV','SESSIONID') ; |
| 0 | 67 ''' |
|
140
58d15cb69f72
fixed Luca's old queries with new SELECT parsing
catherine@Elli.myhome.westell.com
parents:
103
diff
changeset
|
68 self.query_ractop = '''SELECT |
| 0 | 69 inst_id||'_'||sid inst_sid,username,osuser||'@'||terminal "User@Term",program, decode(taddr,null,null,'NN') tr, |
| 70 sql_id, '.'||mod(fixed_table_sequence,1000) seq, state||': '||event event, | |
| 71 case state when 'WAITING' then seconds_in_wait else wait_time end w_tim, last_call_et elaps | |
| 72 from gv$session | |
| 73 where status='ACTIVE' and username is not null | |
| 74 and not (event like '% waiting for messages in the queue' and state='WAITING') | |
| 75 and audsid !=sys_context('USERENV','SESSIONID'); | |
| 76 ''' | |
|
140
58d15cb69f72
fixed Luca's old queries with new SELECT parsing
catherine@Elli.myhome.westell.com
parents:
103
diff
changeset
|
77 self.query_longops = '''SELECT |
| 0 | 78 inst_id,sid,username,time_remaining remaining, elapsed_seconds elapsed, sql_hash_value hash, opname,message |
| 79 from gv$session_longops | |
| 80 where time_remaining>0; | |
| 81 ''' | |
| 58 | 82 |
| 92 | 83 def do_new(self, args): |
| 84 'tells you about new objects' | |
|
140
58d15cb69f72
fixed Luca's old queries with new SELECT parsing
catherine@Elli.myhome.westell.com
parents:
103
diff
changeset
|
85 self.onecmd('''SELECT owner, |
| 92 | 86 object_name, |
| 87 object_type | |
| 88 FROM all_objects | |
|
140
58d15cb69f72
fixed Luca's old queries with new SELECT parsing
catherine@Elli.myhome.westell.com
parents:
103
diff
changeset
|
89 WHERE created > SYSDATE - 7;''') |
| 0 | 90 def do_top9i(self,args): |
| 91 '''Runs query_top9i defined above, to display active sessions in Oracle 9i''' | |
|
140
58d15cb69f72
fixed Luca's old queries with new SELECT parsing
catherine@Elli.myhome.westell.com
parents:
103
diff
changeset
|
92 self.onecmd(self.query_top9i) |
| 0 | 93 |
| 94 def do_top(self,args): | |
| 95 '''Runs query_ractop defined above, to display active sessions in Oracle 10g (and RAC)''' | |
|
140
58d15cb69f72
fixed Luca's old queries with new SELECT parsing
catherine@Elli.myhome.westell.com
parents:
103
diff
changeset
|
96 self.onecmd(self.query_ractop) |
| 0 | 97 |
| 98 def do_longops(self,args): | |
| 99 '''Runs query_longops defined above, to display long running operations (full scans, etc)''' | |
|
140
58d15cb69f72
fixed Luca's old queries with new SELECT parsing
catherine@Elli.myhome.westell.com
parents:
103
diff
changeset
|
100 self.onecmd(self.query_longops) |
| 0 | 101 |
|
140
58d15cb69f72
fixed Luca's old queries with new SELECT parsing
catherine@Elli.myhome.westell.com
parents:
103
diff
changeset
|
102 do_get = Cmd.do__load |
| 0 | 103 def do_load(self,args): |
|
140
58d15cb69f72
fixed Luca's old queries with new SELECT parsing
catherine@Elli.myhome.westell.com
parents:
103
diff
changeset
|
104 '''Runs query_load10g defined above, to display OS load on cluster nodes (10gRAC) |
|
58d15cb69f72
fixed Luca's old queries with new SELECT parsing
catherine@Elli.myhome.westell.com
parents:
103
diff
changeset
|
105 Do not confuse with `GET myfile.sql` and `@myfile.sql`, |
|
58d15cb69f72
fixed Luca's old queries with new SELECT parsing
catherine@Elli.myhome.westell.com
parents:
103
diff
changeset
|
106 which get and run SQL scripts from disk.''' |
| 0 | 107 self.do_select(self.query_load10g) |
| 108 | |
| 64 | 109 def do_himom(self,args): |
| 110 '''greets your mom''' | |
| 111 print 'hi mom' | |
| 112 | |
| 0 | 113 def do_db(self,args,filepath='pass.txt'): |
| 114 '''Exec do_connect to db_alias in args (credentials form the file pass.txt) ''' | |
| 115 f = open(filepath,'r') | |
| 116 connectstr = f.readline().strip() +'@'+args | |
| 117 self.do_connect(connectstr) | |
| 118 f.close() | |
| 119 | |
| 120 def do_py(self, arg): | |
| 121 '''Executes a python command''' | |
| 122 try: | |
| 123 exec(arg) | |
| 124 except Exception, e: | |
| 125 print e | |
| 126 | |
|
5
65ae6cec71c6
expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
4
diff
changeset
|
127 def do_tselect(self, arg): |
| 0 | 128 '''executes a query and prints the result in trasposed form. Useful when querying tables with many columns''' |
|
5
65ae6cec71c6
expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
4
diff
changeset
|
129 |
|
65ae6cec71c6
expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents:
4
diff
changeset
|
130 self.do_select(arg, override_terminator='\\t') |
| 0 | 131 |
| 132 def do_sql(self,args): | |
| 133 '''prints sql statement give the sql_id (Oracle 10gR2)''' | |
| 134 self.query = "select inst_id, sql_fulltext from gv$sqlstats where sql_id='"+args+"'" | |
| 135 try: | |
| 136 self.curs.execute(self.query) | |
| 137 row = self.curs.fetchone() | |
| 138 print "\nSQL statement from cache" | |
| 139 print "------------------------\n" | |
| 140 while row: | |
| 141 print "\nINST_ID = "+str(row[0])+" - SQL TEXT:\n", row[1].read() | |
| 142 row = self.curs.next() | |
| 143 except Exception, e: | |
| 144 print e | |
| 145 | |
| 146 def do_explain(self,args): | |
| 147 '''prints the plan of a given statement from the sql cache. 1 parameter: sql_id, see also do_sql ''' | |
| 148 self.query = "select * from table(dbms_xplan.display_cursor('"+args+"'))" | |
| 149 try: | |
| 150 self.curs.execute(self.query) | |
| 151 rows = self.curs.fetchall() | |
| 152 desc = self.curs.description | |
| 153 self.rc = self.curs.rowcount | |
| 154 if self.rc > 0: | |
| 155 print '\n' + sqlpython.pmatrix(rows,desc,200) | |
| 156 except Exception, e: | |
| 157 print e | |
| 158 | |
| 159 def do_sessinfo(self,args): | |
| 160 '''Reports session info for the give sid, extended to RAC with gv$''' | |
| 161 self.do_tselect('* from gv$session where sid='+args+';') | |
| 162 | |
| 163 def do_sleect(self,args): | |
| 164 '''implements sleect = select, a common typo''' | |
| 165 self.do_select(args) | |
| 166 | |
| 167 do_slect = do_sleect | |
| 168 | |
| 169 def run(): | |
| 170 my=mysqlpy() | |
| 171 print my.__doc__ | |
| 60 | 172 try: |
| 92 | 173 if sys.argv[1][0] != '@': |
| 103 | 174 connectstring = sys.argv.pop(1) |
| 175 try: # attach AS SYSDBA or AS SYSOPER if present | |
| 176 for connectmode in my.connection_modes.keys(): | |
| 177 if connectmode.search(' %s %s' % tuple(sys.argv[1:3])): | |
| 178 for i in (1,2): | |
| 179 connectstring += ' ' + sys.argv.pop(1) | |
| 180 break | |
| 181 except TypeError: | |
| 182 pass | |
| 183 my.do_connect(connectstring) | |
| 94 | 184 for arg in sys.argv[1:]: |
| 103 | 185 if my.onecmd(arg, assumeComplete=True) == my._STOP_AND_EXIT: |
| 94 | 186 return |
| 60 | 187 except IndexError: |
| 188 pass | |
| 0 | 189 my.cmdloop() |
| 190 | |
| 191 if __name__ == '__main__': | |
| 93 | 192 run() |