Rius2 Rius2 - 1 month ago 8
Python Question

How do i select the value of a certain column from the last row in sqlite3 and python?

At the moment i do not have set

INTEGER PRIMARY KEY
as i do not need it but i will if it is the easiest way.

So i set up my database and table:

conn = sqlite3.connect('lista.db')
c = conn.cursor()
c.execute('CREATE TABLE IF NOT EXISTS unu(echipa1 TEXT UNIQUE, scor1 TEXT, scor2 TEXT, echipa2 TEXT, '
'cota FLOAT, miza INT, stare INT)')


i insert some values

c.execute("INSERT OR IGNORE INTO unu( echipa1,scor1, scor2, echipa2, cota, miza, stare ) VALUES (?,?,?,?,?,?,?)",
(echipa1, scor1, scor2, echipa2, cota, miza, stare))


so how do i select the value for
stare
from the last row?

c.execute("SELECT stare FROM unu WHERE id = Max")


So i know i do not have an id, but does sqlite count the rows even if i do not assign a id to each row?
If not i will set a
INTEGER PRIMARY KEY


c.execute('CREATE TABLE IF NOT EXISTS unu(id INTEGER PRIMARY KEY, echipa1 TEXT UNIQUE, scor1 TEXT, scor2 TEXT, echipa2 TEXT, '
'cota FLOAT, miza INT, stare INT)')


Will then my select query work?

Answer Source

This depends on your definition of last row. If you want to select the row you just inserted, you can use cursor.lastrowid.

c.execute("SELECT stare FROM unu WHERE ROWID=(?)", (c.lastrowid,))

If you want the last row anyone inserted (someone may have inserted a row between the insert and select statements), then you need a PK with AUTOINCREMENT (just "id INTEGER PRIMARY KEY" will make it a copy of the ROWID):

c.execute("SELECT stare FROM unu ORDER BY id DESC LIMIT 1")