|
85
|
1 SQLPython is an open-source command-line environment for interacting with an
|
|
91
|
2 Oracle database. It is designed as an alternative to Oracle's SQL\*Plus.
|
|
85
|
3
|
|
|
4 Installing
|
|
|
5 ----------
|
|
|
6
|
|
|
7 Debian/Ubuntu::
|
|
|
8
|
|
|
9 $ sudo apt-get install python python-dev python-setuptools
|
|
|
10 $ sudo easy_install cx_Oracle
|
|
|
11 $ sudo easy_install -UZ sqlpython
|
|
|
12
|
|
|
13 Windows:
|
|
|
14 Download and run executable installers from::
|
|
|
15
|
|
|
16 http://www.python.org (Python language)
|
|
|
17 http://cx-oracle.sourceforge.net/ (cx_Oracle)
|
|
|
18 http://pypi.python.org/pypi/sqlpython (sqlpython)
|
|
|
19
|
|
|
20 Other:
|
|
|
21 Python is typically already installed. You'll need its
|
|
|
22 development package (python-dev); then easy_install
|
|
|
23 cx_Oracle and sqlpython as per Debian.
|
|
|
24
|
|
|
25 Using
|
|
|
26 -----
|
|
|
27
|
|
91
|
28 Use sqlpython more or less as you would use SQL\*Plus.
|
|
85
|
29
|
|
|
30 Read the help. Experiment with UNIX-style and postgresql-style
|
|
|
31 commands.
|
|
|
32
|
|
103
|
33 Special output (inspired by YASQL)
|
|
|
34 ----------------------------------
|
|
|
35
|
|
|
36 An integer following a command terminator limits output to that number of rows, like SQL's LIMIT keyword::
|
|
|
37
|
|
|
38 hr@xe> SELECT * FROM jobs;2
|
|
|
39
|
|
|
40 If `;` is replaced by one of these special characters, the output will be formatted as such::
|
|
|
41
|
|
|
42 ---------- ----------------------
|
|
|
43 terminator format
|
|
|
44 ---------- ----------------------
|
|
|
45 ; standard Oracle format
|
|
|
46 \c CSV (with headings)
|
|
|
47 \C CSV (no headings)
|
|
|
48 \g list
|
|
|
49 \G aligned list
|
|
|
50 \h HTML table
|
|
|
51 \i INSERT statements
|
|
|
52 \s CSV (with headings)
|
|
|
53 \S CSV (no headings)
|
|
|
54 \t transposed
|
|
|
55 \x XML
|
|
|
56 ---------- ----------------------
|
|
|
57
|
|
|
58 Special terminators can also be combined with row limits::
|
|
|
59
|
|
|
60 hr@xe> SELECT * FROM jobs\h5
|
|
|
61
|
|
|
62 Redirecting output
|
|
|
63 ------------------
|
|
|
64
|
|
|
65 `>` and `>>` write or append the output of a command. If a
|
|
|
66 filename is given, that will be the destination of the output.
|
|
|
67
|
|
|
68 If no filename is given, the output will go into the paste buffer and
|
|
|
69 can immediately pasted to any program. This requires `xclip` (*nix) or
|
|
|
70 `pywin32` (Windows) to be installed on the operating system.
|
|
|
71
|
|
|
72 Connecting
|
|
|
73 ----------
|
|
|
74
|
|
|
75 sqlpython supports every version of connecting that SQL*Plus does, including EZCONNECT::
|
|
|
76
|
|
|
77 $ > sqlpython
|
|
|
78 $ > sqlpython hr/hr@xe
|
|
|
79 $ > sqlpython hr (uses ORACLE_SID, prompts for password)
|
|
|
80 $ > sqlpython hr/hr@hostmachine.somewhere.com/xe
|
|
|
81 $ > sqlpython hr/hr@hostmachine.somewhere.com:1521/xe
|
|
|
82 $ > sqlpython sys@xe as sysdba
|
|
|
83
|
|
|
84 You may also supply commands that will be run immediately after connection::
|
|
|
85
|
|
|
86 $ > sqlpython hr/hr@xe @myscript.sql @another_script.sql quit
|
|
|
87
|
|
|
88 Multi-word commands must be enclosed in double-quotes::
|
|
|
89
|
|
|
90 $ > sqlpython hr/hr@xe "cat jobs" "select * from employees;"
|
|
|
91
|
|
|
92 Combining special output terminators with redirectors and command-line arguments
|
|
|
93 can produce powerful one-line programs. For instance, this generates an HTML
|
|
|
94 report and exits::
|
|
|
95
|
|
|
96 $ > sqlpython hr/hr@xe "select * from jobs\h > jobs.html" quit
|
|
|
97
|
|
85
|
98 Modifying
|
|
|
99 ---------
|
|
|
100
|
|
90
|
101 Modify mysqlpy.py; add `do_mycommand(self, arg)`
|
|
|
102 methods to the mysqlpy class to add your own commands.
|
|
|
103
|
|
|
104 Use `self.stdout.write(txt)` in place of `print txt`
|
|
|
105 to make sure your output can be redirected into text
|
|
|
106 files or the paste buffer with `>` and `>>`.
|
|
85
|
107
|
|
|
108 Contributing
|
|
|
109 ------------
|
|
|
110
|
|
|
111 Development trunk is available from::
|
|
|
112
|
|
|
113 http://www.assembla.com/wiki/show/sqlpython
|
|
|
114
|
|
|
115 Bugs and suggestions can be filed at::
|
|
|
116
|
|
|
117 http://www.assembla.com/spaces/sqlpython/tickets
|
|
|
118
|
