[Tutor] overview of how data is handled [getting dictionaries from sql cursor / generators] (fwd)

[Danny Yoo]

> One nice thing about getting a list is I can change the data in the list
> (can't in a tuple).  so how about something like: replace all extPrice
> with Qty * price (this is a very simple thing to do in VFP, Delphi or
> Java.) in the resultset. Then open a connection and save the changed
> data.


Hi John,

Ah, ok, this part is different from my assumptions of the problem.  I had
assumed we were still trying to generate some kind of report file, so that
we needed just read-only values.  It sounds like you want to make changes
to the database.  Are you thinking of Java's update* methods in
java.sql.ResultSet?

If so, then no, I don't think that Python's DB API 2.0 supports doing
select-and-update directly through a cursor, but I could be wrong.  You
may want to ask on the Database SIG group:

    http://python.org/sigs/db-sig/



> In the java code you are looping thru the resultset.  That's all I want
> to do - at first.  Then I'll need some way to loop thru the data based
> on the field name.  Again it looks like I have to create the dict for
> each record.  This just can't be right. Fetchone 1000's of times is also
> got to be wrong.

Hmmm... I have to disagree here, because I'm not seeing the difference.
How is looping through a Java ResultSet conceptually different from
calling cursor.fetchone() in Python?

In the Java code that was presented earlier, we could just as easily say
that in

/*** Java ***/
while (rs.next()) {
     ...
}
/******/

we're calling 'rs.next()' thousands of times too.  The 'test' condition in
a loop is not just executed once, but every time at the beginning of each
iteration.



> In the VFP world we have a "SCAN" that loops thru the resultset.  I can
> access the data by field name in VFP. How does one do same thing - loop
> thru the resultset (could be 1000's) and access the fields by their
> field name?

Hmmm!  I'm not familiar with VFP.  Googling... *grin*

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_foxhelp/html/lngscan__endscan.asp



Ok, I see how SCAN works.  But I still don't see the difference here
between the approach we're taking in Python and the approach that Foxpro
takes.  Let's take the example that they're giving us:


****** Foxpro code
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\testdata')
USE customer  && Opens Customer table
CLEAR
SCAN FOR UPPER(country) = 'SWEDEN'
   ? contact, company, city
ENDSCAN
******


Here's Python pseudocode for the above, if we pretend to have a MySQLdb
database called 'testdata', and a table called 'customer'.  (Also, let's
pretend we have that getDictsFromCursor() function from our last
exchange.):


###
conn = MySQLdb.connect(db='testdata')
cursor = conn.cursor()
cursor.execute("""SELECT * from customer
                  WHERE UPPER(country) = 'SWEDEN'""")
for row in getDictsFromCursor(cursor):
    print row['contact'], row['company'], row['city']
###


There are some issues here we should address.  First, the scanning
condition in the Foxpro code,

    SCAN FOR UPPER(country) = 'SWEDEN'

can --- and should! --- be defined in the SQL query.  So I've put a
"WHERE" clause in the SQL that defines that constraint.


The following snippet, in contrast:

###
conn = MySQLdb.connect(db='testdata')
cursor = conn.cursor()
cursor.execute("""SELECT * from customer""")
for row in getDictsFromCursor(cursor):
    if row['country'].upper() == 'SWEDEN':
        print row['contact'], row['company'], row['city']
###

will also work, but it is inefficient because it iterates over all the
records in customer.  Is this what you were worried about?


Foxpro's SCAN function also appears to bind new variables automagically so
that we can simply name them within the SCAN block.  The equivalent Python
code is a little clumsier than the Foxpro code, since I'm accessing the
values though that row dictionary.

I'm not convinced, though, that Foxpro's rebinding is a good idea.  It's
convenient, but the names of fields in our database might be easily be
keywords in our language.  What happens in Foxpro if one of the column
names is something like ENDSCAN?  Is that possible?

That's one motivating reason to package up each result row in a container,
like a list or a dictionary.  The extra layer of indirection allows us to
avoid name-collision issues.


I hope this helps!