Walking through a mysql db

[Heiko Wundram]

Am Samstag, 4. Juni 2005 17:23 schrieb Jeff Elkins:
> Within this same app, I've got a search function working, but I need the
> rownumber when a record is fetched.
>
>   sql = """select * from address where %s = %%s""" % arg1.lower()
>             cursor.execute(sql, (arg2,))
>              item = cursor.fetchone ()
>              index = cursor.rownumber
>
> At this point, the record is on the screen, but cursor.rownumber doesn't
> reflect the actual rownumber...it always returns 1. How can I obtain the
> actual row number for the displayed record?

You're relying on the fact that rows never move... And IMHO this premise is 
false (at least it's not what a relational database has to guarantee for a 
table).

What you might do:

sql = """select prim_key from address where %s = %%s""" % arg1.lower()
cursor.execute(sql,(arg2,))
prim_key = cursor.fetchone()[0]

And then pass prim_key to the actual display function:

def fetch_item(prim_key=None,row_number=None):
	if prim_key:
		sql = """select * from address where prim_key = %s"""
		args = (prim_key,)
		# We fetch by primary key, only one row of one in table as primary
		# key is unique.
		row_number, rows = 1, 1
	elif row_number:
		sql = """select * from address limit %i,1""" % (row_number,)
		args = ()
		# We fetch a specified row_number, we already know number of rows in
		# query.
		rows = None
	else:
		sql = """select * from address"""
		args = ()
		# We fetch everything to get row count. Returned value is row 1,
		# after query rows contains number of rows fetched.
		row_number = 1
		rows = 0
	cursor.execute(sql,args)
	if rows == 0:
		# I don't know what MySQLdb uses, probably something like this...
		rows = cursor.rowcount
	return cursor.fetchone(), row_number, rows

Now, to use the function in your script, just test for certain parameters, 
such as a passed primary key (in response to a search operation), or a passed 
total row count and current row_number. The function takes care of the rest 
of processing.

HTH!

-- 
--- Heiko.
  see you at: http://www.stud.mh-hannover.de/~hwundram/wordpress/
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 196 bytes
Desc: not available
URL: <http://mail.python.org/pipermail/python-list/attachments/20050604/6617afbd/attachment.sig>