CODEWITHSUNDEEP
pythondatabasesqlitepyqt
Noob
Noob

Reputation: 29

How to iterate through database rows using max(ID) on button click event Sqlite - Python

The function below is connected to a button and when pressed sets the last row of the database(aka the last data entry) to the appropriate text boxes/lineEdits. What im trying to do is make it so each time the button is pressed it sets the next row(one before last) of the database to the text boxes/lineEdits. In short im trying to iterate through the database using forward and backward buttons. Is there somesort of next function?

def loadData(self):
    connection = sqlite3.connect('films.db')
    c = connection.cursor()
    cur = c.execute("SELECT * FROM FILMS WHERE ID = (SELECT MAX(ID) FROM FILMS)")
    last_row = cur.fetchone() #returns the last row of the database(aka the max id) as a tuple
                              #tuple consists of 4 columns; ID,TITLE,YEAR,RATING


    title =last_row[1] #slicing the tuple to set the title into the text box
    self.edit_main_title.setText(title)
    self.edit_title.setText(title)

    year = last_row[2]
    self.edit_year.setText(year)

    ratings = last_row[3]
    self.edit_rating.setText(str(ratings))

I dont know if this could help but -1 from max id gives the row before the last

cur = c.execute("SELECT * FROM FILMS WHERE ID = (SELECT MAX(ID)-1 FROM FILMS)")

Upvotes: 0

Views: 177

Answers (1)

CL.
CL.

Reputation: 180210

A different way of getting the largest ID is to use ORDER BY and LIMIT:

SELECT ...
FROM Films
ORDER BY ID DESC
LIMIT 1;

Now if you have remembered that ID value, you can restrict the query to rows with a smaller ID value, so you get the largest value of the remaining rows:

SELECT ...
FROM Films
WHERE ID < ?
ORDER BY ID DESC
LIMIT 1;

Upvotes: 1

Related Questions