How to move my cursor to the last record in MySQL using Python?

Question

I'm using MySQLdb library to connect to MySQL within my Python script.

(Ref: http://mysql-python.sourceforge.net/MySQLdb.html ).

I would like to learn if there is a way to jump to the last record in a resultset using Python?
In other words, I would like to move my cursor to the last record in the resultset.

It seems not many people understand this question but this is totally valid concern for people who know CURSOR in SQL. Unfortunately most of cursor implementations in Python are the client side cursor (aka fake cursor). It doesn't issue DECLARE CURSOR, hence no support of MOVE. The only server side implementation I'm aware of is the named cursor of psycopg2 for PostgreSQL. — Kenji Noguchi
– Kenji Noguchi, Commented Feb 7, 2015 at 21:48

nosklo · Accepted Answer · 2010-01-04 13:20:20Z

1

Well, your question is weird. Why would you want to do that?

You could keep reading records from the cursor until it is the last. But you'd have to try reading past it to know it is the last one, you can't know before reading it.

Another idea is to make a reversed query. Use ORDER BY to reverse the result order and the last will come first.

Or if you know the number of records, you could use OFFSET to return only the last one. You could issue a COUNT query first to know the number, and then use this number on OFFSET.

answered Jan 4, 2010 at 13:20

nosklo

224k58 gold badges300 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Antoine P. Over a year ago

And don't forget to use LIMIT so that you only fetch the record you are interested in and not the whole table.

Haluk Over a year ago

I don't think the first two solutions would be efficient on large databases. The third solution (OFFSET) is interesting, that's probably what I'm looking for since Python's MySQL API does not provide functions such as afterLast(), beforeFirst() which are pretty common in some other programming languages.

Chris Robak · Accepted Answer · 2012-03-29 05:09:09Z

0

If you feel the query is not too hard on the db, and there is enough memory on the machine, you could store the result set in a list, and just grab the last object from the list:

import MySQLdb
conn = MySQLdb.connect(host='',user='',passwd='')
curs = conn.cursor()
curs.execute('show databases') # or whatever query
last_result = [x[0] for x in curs.fetchall()][-1]

answered Mar 29, 2012 at 5:09

Chris Robak

1187 bronze badges

Comments

serafin · Accepted Answer · 2017-03-24 15:46:31Z

0

curs.execute("SELECT ghfhgf")

return an int with the number of records.

To move from one records to another x steps

curs.scroll(x)

So

curs.scroll(curs.execute("SELECT hgffg"))

Can work for you.

answered Mar 24, 2017 at 15:46

serafin

1

Collectives™ on Stack Overflow

How to move my cursor to the last record in MySQL using Python?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related