Steve Holden wrote:
> [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/
thanks , that's excellent..
currently what i do is sort of combined the sql statements, something
like
stmt = """declare @res int
exec @res sp_adduser '%s'
if @res = 0
insert table values...blah blah where col = '%s'
""" % ( login, colvalue)
then when i do
(a,b) = db.executed(stmt), it gives me ([(0,)], [(1,)]) when i print
(a,b)
i think one result is the one from sp_adduser stored proc and the other
is the insertion.
if i want to get a or b's value, i would do a[0][0] or b[0][0]
will using a cursor with fetch* give me a return result of a stored
proc...?
i did not use cursors for executing stored procs...i just call
db.execute(stmt) , from the eg above.
i think i am going to revamp some of my codes, after looking at your
reply. thanks
--
http://mail.python.org/mailman/listinfo/python-list
