CODEWITHSUNDEEP
pythondatabasesqlite
Noob
Noob

Reputation: 29

Using DESC within MAX ID Sqlite3 - Python

Can someone tell me why the code below is throwing errors. Its meant to look at the current id and if its less the max to descend to the next row in the database and print it.

def loadData(self):
    connection = sqlite3.connect('films.db')
    c = connection.cursor()
    maxid_before = c.execute("SELECT * FROM FILMS WHERE ID = (SELECT MAX(ID) FROM FILMS)")
    last_row = maxid_before.fetchone()[0]
    maxid_after = c.execute("SELECT * FROM FILMS WHERE ID < (last_row) ORDER BY ID DESC LIMIT 1;").fetchone()
    print(maxid_after)

UPDATE This is the function that calls loadData.What silly mistake have i done this time.You can ignore the first 5 lines or so.

def __init__(self):
    QtGui.QMainWindow.__init__(self)
    Ui_MainWindow.__init__(self)
    self.setupUi(self)
    self.edit_btn.clicked.connect(self.Retrieve)
    self.edit_save_btn.clicked.connect(self.insertData)
    last_id = 0
    if not last_id:
        connection = sqlite3.connect('films.db')
        c = connection.cursor()
        result = c.execute("SELECT * FROM FILMS WHERE ID = (SELECT MAX(ID) FROM FILMS)")
        last_id = result.fetchone()[0]
    self.edit_load_btn.clicked.connect(self.loadData)

And this is the loadData function

def loadData(self, last_id):
    connection = sqlite3.connect('films.db')
    c = connection.cursor()
    maxid_after = c.execute("SELECT * FROM FILMS WHERE ID < ? ORDER BY ID DESC LIMIT 1;", (last_id,)).fetchone()
    print(maxid_after)
    return maxid_after

Upvotes: 0

Views: 489

Answers (1)

kuro
kuro

Reputation: 3226

Try this - 

maxid_after = c.execute("SELECT * FROM FILMS WHERE ID < ? ORDER BY ID DESC LIMIT 1;", (last_row,)).fetchone()

In your code last_row is a variable not column name. So, you should put the value of last_row in the query.

If you put last_row in the query like you did, sqlite will treat last_row as a column name and try to execute the query like that. As it fails to find any column by that name, it throws the error.

If you want to run this function on a button click and return one by one row in descending order in every button click, then your function should be like this - 

def loadData(self):
    connection = sqlite3.connect('films.db')
    c = connection.cursor()
    result = c.execute("SELECT * FROM FILMS WHERE ID < ? ORDER BY ID DESC LIMIT 1;", (last_id,)).fetchone()
    if result:
        self.last_id = result[0]
    connection.close()

And the function that calls loadData() should be like - 

# This is the code that calls loadData()
# This is __init__

result = c.execute("SELECT * FROM FILMS WHERE ID = (SELECT MAX(ID) FROM FILMS)").fetchone()

if result:
    self.last_id = result[0]

...
# On button click
# Call loadData

Upvotes: 1

Related Questions