Python – Can’t get the last row id from SQLITE3 database

pythonpython-2.7sqlite

I have a script that asks for input, and that input is then inserted into a table.
The next time the script is run, I'd like for it to tell the user what id the last input has.

The table looks like: id INTEGER PRIMARY KEY AUTOINCREMENT, userid TEXT, domain TEXT, password TEXT, webserver TEXT, sqlserver TEXT

I was told I could use SELECT seq from SQLITE_SEQUENCE WHERE name='table_name' but it yields the following text: instead of the id from the last row.

Please note that I'm an extremely new Python / SQLite3 coder!

For your reference, the code sofar looks like this:

#!/usr/bin/python
import os, sys, sqlite3
######## CHECK SYSTEM COMPATIBILITY ########
if os.name =='posix':
    os.system("clear")#CLEAR SCREEN#
    pass
else:
    sys.exit("Operating System is not supported")
######## END CHECK SYSTEM COMPATIBILITY ########
######## CHECK IF SCRIPT IS RUN AS ROOT ########
#if os.geteuid() != 0:
#       sys.exit("Script must be run as root")
#else:
#       pass
####### END CHECK IF SCRIPT IS RUN AS ROOT ########
####### CREATE DATABASE AND CHECK IF TABLE EXISTS ##########
conn = sqlite3.connect("dat.db")
c = conn.cursor()
c.execute ('''CREATE TABLE IF NOT EXISTS kunder
            (id INTEGER PRIMARY KEY AUTOINCREMENT, userid TEXT, domain TEXT, password      TEXT, webserver TEXT, sqlserver TEXT)''')
conn.commit()
print c.execute ("SELECT seq from SQLITE_SEQUENCE WHERE name='kunder'")
conn.close()
######## DONE CREATE DATABASE AND CHECK IF TABLE EXISTS #########
###### ASK FOR INPUT ##########
########### HERE NEEDS TO BE A CHECK TO DETERMINE THE LATEST USERID - ALSO NEEDS TO BE    FOR WEBSERVER AND PASSWORD #################
userid = raw_input("Enter userid: ")
########### HERE NEEDS TO BE A CHECK TO SEE IF USERID EXISTS!!!!!#####################
domain = raw_input("Enter domain: ")
password = raw_input("Enter password: ")
########### NEEDS TO BE A WAY TO AUTOGENERATE A PASSWORD!!! ####################
webserver = raw_input("Enter webserver: ")
sqlserver = raw_input("Enter sqlserver: ")
###### FINISHED ASK FOR INPUT #######
######## DATABASE ###########
conn = sqlite3.connect("dat.db")
c = conn.cursor()
c.execute ("INSERT INTO kunder (userid, domain, password, webserver, sqlserver) VALUES    (?,?,?,?,?)", (userid, domain, password, webserver, sqlserver))
conn.commit()
conn.close()
####### DONE WITH DATABASE ##########

Best Answer

The SQL statement SELECT max(id) FROM table_name should give you the maximum id. If you're auto-incrementing then this would be the same as the last inserted.

Edit: To get the actual value in python means reading it from the cursor:

cursor = sqlite3.execute('SELECT max(id) FROM table_name')
max_id = cursor.fetchone()[0]

fetchone() returns the first row from the select statement as a tuple (unless a row_factory is used), so fetchone()[0] will, in this case, return the first (and only) column in the first (and only) row, i.e. the max(id).

See http://docs.python.org/2/library/sqlite3.html for more info.

Related Topic