[EMAIL PROTECTED] 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/
--
http://mail.python.org/mailman/listinfo/python-list
