getting results into one variable

[Steve Holden]

s99999999s2003 at yahoo.com wrote:

That's a nice email address :-)
> hi
> the database "execute" function returns a list of logical results. Each
> logical result is a list of row tuples, as explained in the documents.
> 
In a DB-API-compliant module, execution of the query adn retrieval of 
the result(s) are actually sepearated: execute() executesthe query (and 
on some, but not all, platforms returns the number of rows in the 
result). Then you use either fetchone(), fetchmany() or fetchall() to 
retrive the results from the cursor.

> everytime i use it to execute various statements, it returns me, for
> example
> ([(0,)], [(0,)], [(0,)]) and sometimes , ([(0,)], [(0,)]) or ([(0,)])
> 
What you seem to be saying here is that you are getting a tuple of 
lists, each of which contains a (single-element) tuple. What mopdule are 
you using to do this, or is it the result of a gedanken-experiment?

> in my call, i give
> eg (a,b,c) = db.execute(stmt) so that it returns me ([(0,)], [(0,)],
> [(0,)])
> 
> in python, can we do something like
> 
> a = db.execute(stmt) and then expand variable 'a'
> 
> instead of doing
> (a,b) =  db.execute(stmt) for return of 2
> (a,b,c) = for return of 3
> (a,b,c,d) for return of 4
> 
> thanks
> 
Yes. Here's a pracical example using the database that generates 
www.holdenweb.com:

  >>> import mx.ODBC.Windows as db
  >>> conn = db.connect("comsite")
  >>> curs = conn.cursor()
  >>> curs.execute("SELECT secID, secTitle, secPath FROM SECTION")
  >>>

[Note that this returns None for this particular combination of database 
module and backend].

  >>> rows = curs.fetchall()
  >>> rows
[(1, 'Explore Holden Web', 'hd_explore'), (2, 'Student Links', 
'hd_students'), (3, 'Other Stuff', 'hd_otherstuff'), (4, 'Recent Python 
News', 'hd_pythonnews'),(5, 'Python Links', 'hd_pythonlinks'), (6, 
'Python Reading', 'hd_pythonreading'), (7, 'Python Modules', 
'hd_pythonreviews')]
  >>>

You see here that fetchall() returns a list of tuples - each tuple being 
a rows from the query result. It's normal to iterate over this list, and 
one way to do this is:

  >>> for row in rows:
  ...   print row
  ...
(1, 'Explore Holden Web', 'hd_explore')
(2, 'Student Links', 'hd_students')
(3, 'Other Stuff', 'hd_otherstuff')
(4, 'Recent Python News', 'hd_pythonnews')
(5, 'Python Links', 'hd_pythonlinks')
(6, 'Python Reading', 'hd_pythonreading')
(7, 'Python Modules', 'hd_pythonreviews')
  >>>

Of course you can unpack each row if you want to refer to the columns 
individually:

  >>> for row in rows:
  ...   id, title, path = row
  ...   print title, id
  ...
Explore Holden Web 1
Student Links 2
Other Stuff 3
Recent Python News 4
Python Links 5
Python Reading 6
Python Modules 7
  >>>
You can save yourself some time by doing the unpacking right in the for 
loop:

  >>> for id, title, path in rows:
  ...   print id, title
  ...
1 Explore Holden Web
2 Student Links
3 Other Stuff
4 Recent Python News
5 Python Links
6 Python Reading
7 Python Modules
  >>>

Finally, if you only want to use the result once you don't even need to 
save it:

  >>> curs.execute("SELECT secID, secTitle, secPath FROM SECTION")
  >>> for id, ttl, pth in curs.fetchall():
  ...   print pth, ":", ttl
  ...
hd_explore : Explore Holden Web
hd_students : Student Links
hd_otherstuff : Other Stuff
hd_pythonnews : Recent Python News
hd_pythonlinks : Python Links
hd_pythonreading : Python Reading
hd_pythonreviews : Python Modules
  >>>

You can use fetchone() to return each row as a tuple if that suits you 
better, but it may be less efficient because it can lead to inefficient 
communication between the database server and the client, particularly 
if the result set is large.

  >>> curs.execute("SELECT secID, secTitle, secPath FROM SECTION")
  >>> curs.fetchone()
(1, 'Explore Holden Web', 'hd_explore')
  >>>

So of course you can unpack the tuple as well:

 >>> id, ttl, pth = curs.fetchone()
 >>> print "Title:", ttl, "path:", pth, "id:", id
Title: Student Links path: hd_students id: 2
 >>>

If the result sets are too large to comfortably hold in memory you can 
fetch them N at a time with fetchmany(N), repeating until there's 
nothing left to read. And so on, but I hope this gives you the idea.

regards
  Steve
-- 
Steve Holden       +44 150 684 7255  +1 800 494 3119
Holden Web LLC                     www.holdenweb.com
PyCon TX 2006                  www.python.org/pycon/