TL;DR: When I started to combine Oracle23ai and python, I wrote some Terrible pieces of code. One stands out as outright Dangerous (but Useful): How to Execute Any Select-Query...
Summary: Dynamic SQL, and Ad-Hoc queries.
I re-used the program victor.py (see blog last year) to run adhoc-queries: just give it a string of valid sql, and loop-print the results. Who needs SQL*Plus eh?
To copy this trick, you dont even need to download the file (links at bottom); you can almost type it faster yourself.
Up-front Warning: Do Not use this program (this trick) on Production-data. You will see why - and could be worthy of a whole separate blog...
Background: Do Some Queries...
My first steps into Python were focussed on "fetching data", e.g. SELECT, preferably in some SmartDB-way. That data got handed to
PyTorch (link) for further processing. Soon after came the "insert of results". All very Basic, nothing fancy.
A need for debugging-inspection of (large-ish) vectors resulted in the program victor.py to fetch+inspect individual vectors (see earlier blog).
And as I experimented with the (interactive) program for vector-inspection, I "discovered" I could use the same program for adhoc query+display of regular records. That provided a nice playground to experiment. It also turned out quite useful for situations where python-access to the data was the easiest, or even the _only_ way to connect to certain schemas (dont ask, talk to our CISO...).
Return results of a query, simple...
You probably discovered for yourself, this here is the quickest way to verify a (select)query in python:
cursor = ora_conn.cursor()
for row in cursor.execute ( "select * from emp" ):
print ( row)
You can even run queries "interactively" from the python-prompt, after you have opened the connection and defined the cursor:
>>>
>>> for row in cur.execute ( " select * from emp" ):
... print ( row )
...
(7369, 'SMITH', 'CLERK', 7902, datetime.datetime(1980, 12, 17, 0, 0), 800.0, None, 20)
(7499, 'ALLEN', 'SALESMAN', 7698, datetime.datetime(1981, 2, 20, 0, 0), 1600.0, 300.0, 30)
(7521, 'WARD', 'SALESMAN', 7698, datetime.datetime(1981, 2, 22, 0, 0), 1250.0, 500.0, 30)
(7566, 'JONES', 'MANAGER', 7839, datetime.datetime(1981, 4, 2, 0, 0), 2975.0, None, 20)
(7654, 'MARTIN', 'SALESMAN', 7698, datetime.datetime(1981, 9, 28, 0, 0), 1250.0, 1400.0, 30)
(7698, 'BLAKE', 'MANAGER', 7839, datetime.datetime(1981, 5, 1, 0, 0), 2850.0, None, 30)
(7782, 'CLARK', 'MANAGER', 7839, datetime.datetime(1981, 6, 9, 0, 0), 2450.0, None, 10)
(7788, 'SCOTT', 'ANALYST', 7566, datetime.datetime(1982, 12, 9, 0, 0), 3000.0, None, 20)
(7839, 'KING', 'PRESIDENT', None, datetime.datetime(1981, 11, 17, 0, 0), 5000.0, None, 10)
(7844, 'TURNER', 'SALESMAN', 7698, datetime.datetime(1981, 9, 8, 0, 0), 1500.0, 0.0, 30)
(7876, 'ADAMS', 'CLERK', 7788, datetime.datetime(1983, 1, 12, 0, 0), 1100.0, None, 20)
(7900, 'JAMES', 'CLERK', 7698, datetime.datetime(1981, 12, 3, 0, 0), 950.0, None, 30)
(7902, 'FORD', 'ANALYST', 7566, datetime.datetime(1981, 12, 3, 0, 0), 3000.0, None, 20)
(7934, 'MILLER', 'CLERK', 7782, datetime.datetime(1982, 1, 23, 0, 0), 1300.0, None, 40)
>>>
That was a Nice Discovery: once I had a connection-object open, I could just test queries from the prompt! But it can be a bit awkward if you are typo-prone like me...
So, why I wrapped it into a program...
Once I found myself testing queries that way, I came up with the little program do_sql1.py. It will prompt for a query, and try to execute whatever SQL was entered. And display the result line by line (or an error...). The result looked like this:
As you can see, the program opens a connection (using credentials from dotenv, see earlier blog), and it prompts the user for input of a query.
So let's give it a query...:
select * from dept order by dname
I typed the stmtnt, and hit enter:
After typing a query and hit enter, on one single line, bcse sys.input(), I get ... results! And from the timing in the print-stmnts, I even get an idea of where the time is spent, something I could use later in troubleshooting.
[intermezzo] Instrumentation: filename and timestamp.
From the output above, you can see that I print the (source)filename and a timestamp in microseconds in front of every line. To do so I am using a self-coded module called prefix.py (link at bottom) which might also merit a mention in a blog sometime. Printing those timestamps sometimes reveals some of the inner workings (separate story, maybe later...).
More Usage by using Stdin and argv[1]
I quickly discovered that I could use stdin to feed it pre-edited queries. As long as the inputfile was a one-liner. And I also started detecting sys.argv[1] and use that as query to run. All still very primitive, but I found it usable for all kinds of debug-purposes.
For example, typing:
echo "SELECT ename, sal from EMP order by ename" | python3 do_sql1.py
yields the results, to stdout, like this:
Looks familiar ?
And we begin to notice is the time-differences between the output rows. The nr of microseconds for the first row is 945422 - 937535 = 7887 microsec. Most of the subsequent rows get fetched in less than 100microsec (e.g. >10x faster), which is probably an indicator for work going on behind the scene, e.g. a round trip ?
Wrapping up: Risky, but Much more Useful than expected.
Normally, I would be the first to say: What a useless- and Trivial program.
Warning and Caveat, Do I really have to say this Out Loud, again...:"
This little program is very similar to doing "SQL-Injection" - Be Careful!
But I found myself using this do_sql1.py a lot while tweaking and troubleshooting python-oracle programs.
For one, I could run this program from any python-container to test a db-connection.
It verifies: Does the connection go where you think it should?
It does testing: And I could test what a certain query would return in That Particular Container. This is how I find a container is accidentally connected to the wrong database, or wrong schema (yep... quite often!).
And when I finally started reading the documentation for
oracledb (link), this program proved easily-adaptable to explore various settings and options. In the end, it proved much more useful than expected.
As always, I dont think many of you will copy it as-is. But I suspect some of you have similar tools, or will build similar tools. And often the best re-use of code is from the code you typed (or adapted) by yourself.
Have Fun !
-- -- -- -- -- -- End of this blogpost, for now -- -- -- -- -- --
Appendix: links to sourcecode
prefix.py : the module used to format + print output
-- -- -- -- -- End of this blogpost, for real -- -- -- -- -- --
No comments:
Post a Comment