Several times, I demonstrate getting rows from a cursor by means of the cursor's .fetchone() method. .fetchone() is used in loops like this:
curs.execute(<some query>, <bind variables>)Another, more concise, alternative was not mentioned in the article. The cursor object itself can be iterated over, like this:
aRowOfData = curs.fetchone()
while aRowOfData:
<commands>
aRowOfData = curs.fetchone()
curs.execute(<some query>, <bind variables>)The effect is the same, but it works with two fewer lines of code.
for aRowOfData in curs:
<commands>
Finally, the .fetchall() method, bringing the entire result set into a list at once, was only briefly mentioned in the article, but it would probably be preferable to .fetchone() for the small result sets we'll find in places like v$parameter. Only when a result set is very large (or your computer is very memory-limited) do you need to worry about .fetchall()'s impact on your system's available memory.
10 comments:
Catherine,
I perused your OTN article, definitely good stuff. More people need to be turned on to the love that is Python
Although, I am *still* finding myself clinging to my dog-eared copy of the Perl Cookbook with an almost disturbing level of attachment.
Catherine:
I enjoyed your article. Python seems like a very interesting language & I plan on learning more about it.
One other thing though:
In your article you use the example of reading & parsing an init.ora file using Python but I've found that the split('=') function will throw an error if the parameter "archive_log_dest_x" is being used as it has two equal (=) signs in it. Does Python have a work around for this ?
Thank you
John,
Thanks very much for your feedback! You're absolutely right. I decided not to go into this in the article itself, to keep things simple and stay within space constraints, but this is the perfect place to clarify.
Using split('=',1) means that the split will only occur on the first 1 occurance of '=', which is exactly what you want for a real-world init.ora file.
>>> print x.split.__doc__
S.split([sep [,maxsplit]]) -> list of strings
Return a list of the words in the string S, using sep as the
delimiter string. If maxsplit is given, at most maxsplit
splits are done. If sep is not specified or is None, any
whitespace string is a separator.
And if fetchone and fetchall don't quite fit the bill there's always fetchmany, which when wrapped in an iterator give the best bang for buck performance wise.
For an example take a look at the resultIter function in my csvDump.py module.
Thanks, Andy! Yes, fetchmany() can be the happy medium. It can be more complex to use, though, unless it's enclosed in an iterator, as in the Python Cookbook's recipe 19.13, or your csvDump.py .
Catherine, what a nice way of explaining Python to Oracle developers! Particularly I like how you motivated the significant white space. It seems that many people who were accustomed to other languages before stumble over that one and dislike it, though I see it as an advantage as well.
Two minor corrections:
In the passage titled "Nesting" you wrote "directory" where you meant "dictionary" (I sometimes found myself making the same mistake).
Also, instead of writing
for fileName in initParams.keys():
you can write shorter:
for fileName in initParams:
Catherine,
Read your OTN article and enjoyed it much. (and some of the links on your blog look worth following up on too).
I've been trying to do it myself (always dangerous!) but hit a snag: what if an oracle stored procedure has an out param that's a cursor? I've tried execute, callproc and callfunc withou t success - any ideas?
Looking forward to using Python,
Russell.
Hi Catherine,
Nice article on OTN. I was wondering if you know if there's a way to grab the results of an Oracle query into a tuple of dictionaries (one for each row with the key as the column name and the value as the value of the column)?
In mysql I do this as below:
----------------
import MySQLdb
from MySQLdb.cursors import DictCursor
cursortype = DictCursor
...
curs = conn.cursor(cursortype)
result = curs.execute("%s" % (sql, ))
return curs.fetchall()
-------------------
And in Postgres I do it like this:
-----------------
import pg
...
result = conn.query("%s" % (sql, ))
return result.dictresult()
------------------
I can't seem to see if there are any cursor types as with MySQLdb module, or a dictresult query return method as with the pg module.
Any help appreciated.
Thanks, Tom
Tom,
Wow. Did I pay you to ask that?
Basically, nothing like that is built into the DB-API2 specification, or into cx_Oracle, so everybody gets to write their own code to package up row results in a more convenient format. The best of these is, of course, mine. :) Well, sqlWrap.py is still very much unfinished, but that's all the better reason to use it, right?
I really do intend to produce some proper documentation, and a proper package for installation, within a few weeks...
Post a Comment