Mercurial > sqlpython
annotate sqlpython/mysqlpy.py @ 255:39ecc4b65b11
smart prompt now works with db connect
author | catherine@Elli.myhome.westell.com |
---|---|
date | Thu, 12 Mar 2009 21:29:51 -0400 |
parents | ae1d89f09a88 |
children | 6d4d90fb2082 |
rev | line source |
---|---|
189 | 1 #!/usr/bin/python |
250 | 2 # MySqlPy V1.6.1 |
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 |
189 | 13 |
14 class mysqlpy(sqlpyPlus): | |
15 ''' | |
250 | 16 MySqlPy V1.6.1 - 'sqlplus in python' |
189 | 17 Author: Luca.Canali@cern.ch |
250 | 18 Rev: 1.6.1, 15-Mar-09 |
189 | 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 | |
22 | |
23 Usage: sqlpython [connect string] [single-word command] ["multi-word command"]... | |
24 | |
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' | |
59 order by 3 desc | |
60 ''' | |
61 self.query_top9i = '''SELECT | |
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' | |
66 and audsid !=sys_context('USERENV','SESSIONID') ; | |
67 ''' | |
68 self.query_ractop = '''SELECT | |
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 ''' | |
77 self.query_longops = '''SELECT | |
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 ''' | |
82 | |
83 def do_new(self, args): | |
84 'tells you about new objects' | |
85 self.onecmd('''SELECT owner, | |
86 object_name, | |
87 object_type | |
88 FROM all_objects | |
89 WHERE created > SYSDATE - 7;''') | |
90 def do_top9i(self,args): | |
91 '''Runs query_top9i defined above, to display active sessions in Oracle 9i''' | |
92 self.onecmd(self.query_top9i) | |
93 | |
94 def do_top(self,args): | |
95 '''Runs query_ractop defined above, to display active sessions in Oracle 10g (and RAC)''' | |
96 self.onecmd(self.query_ractop) | |
97 | |
98 def do_longops(self,args): | |
99 '''Runs query_longops defined above, to display long running operations (full scans, etc)''' | |
100 self.onecmd(self.query_longops) | |
101 | |
102 do_get = Cmd.do__load | |
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) |
189 | 108 |
252 | 109 def do_hello(self, arg): |
110 print 'Hello, World!' | |
111 | |
250 | 112 @options([make_option('-u', '--uppercase', action='store_true', |
113 help='use ALL CAPS')]) | |
252 | 114 def do_greet(self, arg, opts): |
115 'Provides a personalized greeting.' | |
116 result = 'Hello %s!\n' % arg | |
250 | 117 if opts.uppercase: |
118 result = result.upper() | |
119 self.stdout.write(result) | |
120 | |
189 | 121 def do_db(self,args,filepath='pass.txt'): |
122 '''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
|
123 try: |
39ecc4b65b11
smart prompt now works with db connect
catherine@Elli.myhome.westell.com
parents:
252
diff
changeset
|
124 f = open(filepath,'r') |
39ecc4b65b11
smart prompt now works with db connect
catherine@Elli.myhome.westell.com
parents:
252
diff
changeset
|
125 except IOError: |
39ecc4b65b11
smart prompt now works with db connect
catherine@Elli.myhome.westell.com
parents:
252
diff
changeset
|
126 print 'Need a file %s containing username/password' % filepath |
39ecc4b65b11
smart prompt now works with db connect
catherine@Elli.myhome.westell.com
parents:
252
diff
changeset
|
127 raise |
39ecc4b65b11
smart prompt now works with db connect
catherine@Elli.myhome.westell.com
parents:
252
diff
changeset
|
128 connectstr = f.readline().strip() |
39ecc4b65b11
smart prompt now works with db connect
catherine@Elli.myhome.westell.com
parents:
252
diff
changeset
|
129 if args: |
39ecc4b65b11
smart prompt now works with db connect
catherine@Elli.myhome.westell.com
parents:
252
diff
changeset
|
130 connectstr += '@'+args |
189 | 131 self.do_connect(connectstr) |
132 f.close() | |
133 | |
134 def do_py(self, arg): | |
135 '''Executes a python command''' | |
136 try: | |
137 exec(arg) | |
138 except Exception, e: | |
139 print e | |
140 | |
141 def do_tselect(self, arg): | |
206 | 142 '''executes a query and prints the result in trasposed form. Useful when querying tables with many columns''' |
143 self.do_select(self.parsed(arg, terminator='\\t')) | |
189 | 144 |
145 def do_sql(self,args): | |
146 '''prints sql statement give the sql_id (Oracle 10gR2)''' | |
147 self.query = "select inst_id, sql_fulltext from gv$sqlstats where sql_id='"+args+"'" | |
148 try: | |
149 self.curs.execute(self.query) | |
150 row = self.curs.fetchone() | |
151 print "\nSQL statement from cache" | |
152 print "------------------------\n" | |
153 while row: | |
154 print "\nINST_ID = "+str(row[0])+" - SQL TEXT:\n", row[1].read() | |
155 row = self.curs.next() | |
156 except Exception, e: | |
157 print e | |
158 | |
159 def do_explain(self,args): | |
160 '''prints the plan of a given statement from the sql cache. 1 parameter: sql_id, see also do_sql ''' | |
251
aa33f495a289
reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents:
250
diff
changeset
|
161 words = args.sqlit() |
aa33f495a289
reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents:
250
diff
changeset
|
162 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
|
163 self.curs.execute('explain %s' % args) |
226 | 164 print 'Explained. (see plan table)' |
165 return | |
189 | 166 self.query = "select * from table(dbms_xplan.display_cursor('"+args+"'))" |
167 try: | |
168 self.curs.execute(self.query) | |
169 rows = self.curs.fetchall() | |
170 desc = self.curs.description | |
171 self.rc = self.curs.rowcount | |
172 if self.rc > 0: | |
173 print '\n' + sqlpython.pmatrix(rows,desc,200) | |
174 except Exception, e: | |
175 print e | |
176 | |
177 def do_sessinfo(self,args): | |
206 | 178 '''Reports session info for the given sid, extended to RAC with gv$''' |
179 try: | |
180 if not args: | |
181 self.curs.execute('SELECT sid FROM v$mystat') | |
182 args = self.curs.fetchone()[0] | |
183 self.onecmd('SELECT * from gv$session where sid=%s\\t' % args) | |
184 except cx_Oracle.DatabaseError, e: | |
185 if 'table or view does not exist' in str(e): | |
186 print 'This account has not been granted SELECT privileges to v$mystat or gv$session.' | |
187 else: | |
188 raise | |
189 | 189 |
190 def do_sleect(self,args): | |
191 '''implements sleect = select, a common typo''' | |
192 self.do_select(args) | |
193 | |
194 do_slect = do_sleect | |
195 | |
196 def run(): | |
197 my=mysqlpy() | |
198 print my.__doc__ | |
199 try: | |
200 if sys.argv[1][0] != '@': | |
201 connectstring = sys.argv.pop(1) | |
202 try: # attach AS SYSDBA or AS SYSOPER if present | |
203 for connectmode in my.connection_modes.keys(): | |
204 if connectmode.search(' %s %s' % tuple(sys.argv[1:3])): | |
205 for i in (1,2): | |
206 connectstring += ' ' + sys.argv.pop(1) | |
207 break | |
208 except TypeError: | |
209 pass | |
210 my.do_connect(connectstring) | |
211 for arg in sys.argv[1:]: | |
212 | 212 if my.onecmd(arg) == my._STOP_AND_EXIT: |
189 | 213 return |
214 except IndexError: | |
215 pass | |
216 my.cmdloop() | |
217 | |
218 class TestCase(Cmd2TestCase): | |
219 CmdApp = mysqlpy | |
220 transcriptFileName = 'exampleSession.txt' | |
221 | |
222 if __name__ == '__main__': | |
223 parser = optparse.OptionParser() | |
224 parser.add_option('-t', '--test', dest='unittests', action='store_true', default=False, help='Run unit test suite') | |
225 (callopts, callargs) = parser.parse_args() | |
226 if callopts.unittests: | |
227 sys.argv = [sys.argv[0]] # the --test argument upsets unittest.main() | |
228 unittest.main() | |
229 else: | |
230 run() |