Sqlite3 DELETE

[gordon77]

I am learning SQLite3 and struggling to understand what happens with DELETE a row and the row id.

If I make a database and add several rows I can retrieve them using their id, but if I now DELETE a row it appears to only remove the row contents but leave the id, so it can be retrieved, using fetchone, but now contains None which I can't then find find a way to either insert or modify data in that row or renumber the id's to match the now valid rows.

Am I doing something wrong ?

The attached code will allow saving, reading, modifying and deleting rows. Run it, enter a name and press SAVE , repeat a few times, then use VIEW NEXT to recall the entries, now DELETE the entry for ID 2 and see it is now blank and can't be modified to put data back in it.

Is there a way to INSERT/SAVE at a specific user_id ?

Thanks

Code: Select all

#!/usr/bin/python3
import tkinter as tk
import os
import sqlite3

# define variables
dbname = "mydb7"
age1 = ""
height1 = ""
mobile1 = ""
global db
global cursor
global max_id
user_id = 0

if os.path.exists(dbname) == False:
    db = sqlite3.connect(dbname)
    cursor = db.cursor()
    cursor.execute('''
    CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT,
                       phone TEXT, email TEXT, dob TEXT, age TEXT, height TEXT, mobile TEXT)
     ''')
    db.commit()
    db.close()
    
db = sqlite3.connect(dbname)
cursor = db.cursor()

# determine number of rows in table
cursor.execute('''INSERT INTO users(name, phone, email, dob, age, height, mobile)
     VALUES(?,?,?,?,?,?,?)''', ("test","test","test","test","test","test","test"))
db.commit()
max_id = cursor.lastrowid
cursor.execute('''DELETE FROM users WHERE id = ? ''', (max_id,))
db.commit()

class MainApplication(tk.Frame):
    def __init__(self, master):
        self.master = master
        tk.Frame.__init__(self, self.master)
        self.dataure_gui()
        self.create_widgets()
        
    def dataure_gui(self):
        self.master.title('Data EDIT Form')
        self.master.geometry('600x240')
        self.master.resizable(0, 0)
        
    def create_widgets(self):
        self.create_frames()
        self.create_entries()
        self.create_labels()
        self.create_buttons()
        
    def create_frames(self):
        self.Frame10 = tk.Frame(width=600, height=240)
        self.Frame10.grid_propagate(0)
        self.Frame10.grid(row=0, column=0)


    def create_entries(self):
        global E1,E2,E3,E4,E5,E6,E7
        E1 = tk.Text(self.Frame10, height=1, width=30)
        E1.place(x=190, y=25)
        E2 = tk.Text(self.Frame10, height=1, width=30)
        E2.place(x=190, y=50)
        E3 = tk.Text(self.Frame10, height=1, width=30)
        E3.place(x=190, y=75)
        E4 = tk.Text(self.Frame10, height=1, width=30)
        E4.place(x=190, y=100)
        E5 = tk.Text(self.Frame10, height=1, width=5)
        E5.place(x=190, y=125)
        E6 = tk.Text(self.Frame10, height=1, width=5)
        E6.place(x=190, y=150)
        E7 = tk.Text(self.Frame10, height=1, width=10)
        E7.place(x=190, y=175)
        
    def create_buttons(self):
        self.B1 = tk.Button(self.Frame10, text='Clear',  command = self.clear_data)       
        self.B1.grid(row=0, column=0,padx=420, pady=5)
        self.B2 = tk.Button(self.Frame10, text='VIEW NEXT', command = self.read_next)       
        self.B2.grid(row=1, column=0,padx=420, pady=5)
        self.B3 = tk.Button(self.Frame10, text='VIEW PREVIOUS', command = self.read_prev)       
        self.B3.grid(row=2, column=0,padx=420, pady=5)
        self.B4 = tk.Button(self.Frame10, text=' MODIFY ', command = self.modify_data)       
        self.B4.grid(row=3, column=0,padx=420, pady=5)
        self.B5 = tk.Button(self.Frame10, text='SAVE NEW',  command = self.save_data)       
        self.B5.grid(row=4, column=0,padx=420, pady=5)
        self.B6 = tk.Button(self.Frame10, text=' DELETE ',  command = self.delete_data)       
        self.B6.grid(row=5, column=0,padx=420, pady=5)
        
    def create_labels(self):
        LU = tk.Label(self.Frame10, text="ID :" , font = 30)
        LU.place(x=20, y=0)
        L1 = tk.Label(self.Frame10, text="Name :" , font = 30)
        L1.place(x=20, y=25)
        L2 = tk.Label(self.Frame10, text="Contact Number :" , font = 30)
        L2.place(x=20, y=50)
        L3 = tk.Label(self.Frame10, text="Email:" , font = 30)
        L3.place(x=20, y=75)
        L4 = tk.Label(self.Frame10, text="Date of Birth :" , font = 30)
        L4.place(x=20, y=100)
        L5 = tk.Label(self.Frame10, text="Age :" , font = 30)
        L5.place(x=20, y=125)
        L6 = tk.Label(self.Frame10, text="Height :" , font = 30)
        L6.place(x=20, y=150)
        L7 = tk.Label(self.Frame10, text="Mobile Phone Maker :" , font = 30)
        L7.place(x=20, y=175)
        self.read_next()
        
    def clear_data(self):
        global E1,E2,E3,E4,E5,E6,E7
        L6 = tk.Label(self.Frame10, text="       ")
        L6.config(font=("Courier", 10))
        L6.place(x=190, y=0)
        E1.delete('1.0','30.0')
        E2.delete('1.0','30.0')
        E3.delete('1.0','30.0')
        E4.delete('1.0','30.0')
        E5.delete('1.0','30.0')
        E6.delete('1.0','30.0')
        E7.delete('1.0','30.0')
        LT = tk.Label(self.Frame10, text="                  ")
        LT.config(font=("Courier", 10))
        LT.place(x=280, y=210)

    def read_next(self):
       global E1,E2,E3,E4,E5,E6,E7, user_id, max_id
       if user_id < max_id:
          user_id +=1
       cursor.execute('''SELECT name, phone, email, dob, age, height, mobile FROM users WHERE id=?''', (user_id,))
       user = cursor.fetchone()
       E1.delete('1.0','30.0')
       E2.delete('1.0','30.0')
       E3.delete('1.0','30.0')
       E4.delete('1.0','30.0')
       E5.delete('1.0','30.0')
       E6.delete('1.0','30.0')
       E7.delete('1.0','30.0')
       L6 = tk.Label(self.Frame10, text="       ")
       L6.config(font=("Courier", 10))
       L6.place(x=190, y=0)
       L6 = tk.Label(self.Frame10, text=user_id)
       L6.config(font=("Courier", 10))
       L6.place(x=190, y=0)
       if user != None :
          E1.insert('1.0',user[0])
          E2.insert('1.0',user[1])
          E3.insert('1.0',user[2])
          E4.insert('1.0',user[3])
          E5.insert('1.0',user[4])
          E6.insert('1.0',user[5])
          E7.insert('1.0',user[6])
       
    def read_prev(self):
       global E1,E2,E3,E4,E5,E6,E7, user_id
       user_id -=1
       if user_id < 1:
           user_id = 1
       cursor.execute('''SELECT name, phone, email, dob, age, height, mobile FROM users WHERE id=?''', (user_id,))
       user = cursor.fetchone() 
       E1.delete('1.0','30.0')
       E2.delete('1.0','30.0')
       E3.delete('1.0','30.0')
       E4.delete('1.0','30.0')
       E5.delete('1.0','30.0')
       E6.delete('1.0','30.0')
       E7.delete('1.0','30.0')
       L6 = tk.Label(self.Frame10, text="       ")
       L6.config(font=("Courier", 10))
       L6.place(x=190, y=0)
       L6 = tk.Label(self.Frame10, text=user_id)
       L6.config(font=("Courier", 10))
       L6.place(x=190, y=0)
       if user != None:
          E1.insert('1.0',user[0])
          E2.insert('1.0',user[1])
          E3.insert('1.0',user[2])
          E4.insert('1.0',user[3])
          E5.insert('1.0',user[4])
          E6.insert('1.0',user[5])
          E7.insert('1.0',user[6])

    def save_data(self):
        global E1,E2,E3,E4,E5,E6,E7,user_id, max_id
        Name = str(E1.get('1.0','30.0'))
        name1 = Name.strip()
        Phone  = str(E2.get('1.0','30.0'))
        phone1  = Phone.strip()
        Email = str(E3.get('1.0','30.0'))
        email1 = Email.strip()
        dob  = str(E4.get('1.0','30.0'))
        dob1  = dob.strip()
        Age  = str(E5.get('1.0','20.0'))
        age1 = Age.strip()
        Height  = str(E6.get('1.0','20.0'))
        height1 = Height.strip()
        Mobile  = str(E7.get('1.0','20.0'))
        mobile1 = Mobile.strip()
               
        if len(name1) > 0 :  
            cursor = db.cursor()
            cursor.execute('''INSERT INTO users(name, phone, email, dob, age, height, mobile)
                 VALUES(?,?,?,?,?,?,?)''', (name1,phone1, email1, dob1, age1, height1, mobile1))
            db.commit()
            user_id = cursor.lastrowid
            max_id = user_id
            L6 = tk.Label(self.Frame10, text=user_id)
            L6.config(font=("Courier", 10))
            L6.place(x=190, y=0)
            LT = tk.Label(self.Frame10, text="Data Saved        ")
            LT.config(font=("Courier", 10))
            LT.place(x=280, y=210)
        else:
            LT = tk.Label(self.Frame10, text="Fields Missing    ")
            LT.config(font=("Courier", 10))
            LT.place(x=280, y=210)
            
    def modify_data(self):
        global E1,E2,E3,E4,E5,E6,E7
        global user_id
        cursor = db.cursor()

        Name = str(E1.get('1.0','30.0'))
        name1 = Name.strip()
        Phone  = str(E2.get('1.0','30.0'))
        phone1  = Phone.strip()
        Email = str(E3.get('1.0','30.0'))
        email1 = Email.strip()
        dob  = str(E4.get('1.0','30.0'))
        dob1  = dob.strip()
        Age  = str(E5.get('1.0','20.0'))
        age1 = Age.strip()
        Height  = str(E6.get('1.0','20.0'))
        height1 = Height.strip()
        Mobile  = str(E7.get('1.0','20.0'))
        mobile1 = Mobile.strip()

        cursor.execute('''UPDATE users SET name = ? WHERE id = ? ''',(name1, user_id))
        cursor.execute('''UPDATE users SET phone = ? WHERE id = ? ''',(phone1, user_id))
        cursor.execute('''UPDATE users SET email = ? WHERE id = ? ''',(email1, user_id))
        cursor.execute('''UPDATE users SET dob = ? WHERE id = ? ''',(dob1, user_id))
        cursor.execute('''UPDATE users SET age = ? WHERE id = ? ''',(age1, user_id))
        cursor.execute('''UPDATE users SET height = ? WHERE id = ? ''',(height1, user_id))
        cursor.execute('''UPDATE users SET mobile = ? WHERE id = ? ''',(mobile1, user_id))
        db.commit()
        
    def delete_data(self):
        global user_id
        cursor.execute('''DELETE FROM users WHERE id = ? ''', (user_id,))
        db.commit()
        self.clear_data()
            
if __name__ == '__main__':
    
    root = tk.Tk()
    main_app =  MainApplication(root)
    root.mainloop()

[DougieLawson]

Where's your commit() call? Have you ended the transaction?

Try adding a

Code: Select all

PRAGMA schema.auto_vacuum;

call after you open the database for DELETE.

[Andyroo]

To update a specific record you could try

Code: Select all

db.execute("UPDATE users set name = ‘Fred Bloggs’ where ID = 123")
db.commit()

Ignore the row numbers - your ID field is the primary key and bares no relationship to its location in the database Unless you are using something like ISAM, the key is the way to go. In fact most ISAM databases are hidden so a ‘find’ just works

Edit: Rereading you code you do have them update am I missing something in your question

[gordon77]

Where's your commit() call?

I do a commit() when doing a save, modify or delete.

[gordon77]

Where's your commit() call?

I do a commit() when doing a save, update or delete. They all appear to work.

[DougieLawson]

Where's your commit() call?

I do a commit() when doing a save, modify or delete.

See my edit and https://www.sqlite.org/pragma.html#pragma_auto_vacuum

[gordon77]

To update a specific record you could try

Code: Select all

db.execute("UPDATE users set name = ‘Fred Bloggs’ where ID = 123")
db.commit()

Ignore the row numbers - your ID field is the primary key and bares no relationship to its location in the database Unless you are using something like ISAM, the key is the way to go. In fact most ISAM databases are hidden so a ‘find’ just works

Edit: Rereading you code you do have them update am I missing something in your question

I can update a specific row, using user_id, providing it hasn't previously deleted.

[Andyroo]

If it’s deleted then it should not let you update

Try doing a fetch first - if the name is null then you create else do an update.

Normally primary keys are unique and never get re-used. Think club membership where a person rejoins and gets a new card with a new membership number.

[gordon77]

If it’s deleted then it should not let you update

Try doing a fetch first - if the name is null then you create else do an update.

Normally primary keys are unique and never get re-used. Think club membership where a person rejoins and gets a new card with a new membership number.

Thanks, but can I create using that id ? If I create it normally goes at the last row.

[gordon77]

Where's your commit() call?

I do a commit() when doing a save, modify or delete.

See my edit and https://www.sqlite.org/pragma.html#pragma_auto_vacuum

Thanks Dougie, PRAGMA looks promising but I can't get it to work syntax errors whatever I try

it accepts (no errors) cursor.execute('PRAGMA auto_vacuum = FULL;') after the DELETE, but no change to the outcome

[DougieLawson]

You have to run that PRAGMA before you create your table.
You can do an explicit "vacuum;" to force removal of deleted rows. https://www.sqlite.org/lang_vacuum.html

The most important thing with SQLite3 is one writer multiple readers. If you get two active writers you screw your database. So all of my reader programs include "PRAGMA query_only;"

[gordon77]

You have to run that PRAGMA before you create your table.
You can do an explicit "vacuum;" to force removal of deleted rows. https://www.sqlite.org/lang_vacuum.html

The most important thing with SQLite3 is one writer multiple readers. If you get two active writers you screw your database. So all of my reader programs include "PRAGMA query_only;"

Thanks. Can you show me the syntax?

[DougieLawson]

Code: Select all

cursor.execute('PRAGMA thingyouwantgoeshere;')

[gordon77]

So l need...?

Code: Select all

  db = sqlite3.connect(dbname)
    cursor = db.cursor()
    cursor.execute('PRAGMA  auto_vacuum = FULL;') 
    cursor.execute('''CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT,
                       phone TEXT, email TEXT, dob TEXT, age TEXT, height TEXT, mobile TEXT)
     ''')  
    ..... 
      

[DougieLawson]

Should work. Although I'd probably run a periodic update program to run an explicit vacuum.

My main SQLite3 database is data collection (lots of inserts) and a few readers only. (Because I've not written the aggregate, archive & delete process.)

[gordon77]

So l need...?

Code: Select all

  db = sqlite3.connect(dbname)
    cursor = db.cursor()
    cursor.execute('PRAGMA  auto_vacuum = FULL;') 
    cursor.execute('''CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT,
                       phone TEXT, email TEXT, dob TEXT, age TEXT, height TEXT, mobile TEXT)
     ''')  
    ..... 
      

No, didn't work

[ghp]

Hello, a few comments.

- Pragma vacuum is needed to reduce file size. It allows the sqlite to free deleted entries from the file. It does not affect the way how sqlite handles primary keys. Scenario: assume to insert zillions of records, delete most of them and database size does not shrink. With vacuum, the database shrinks, at the cost of extra perfomance needed to rearrange the file.

- there is a comment in the code '# determine number of rows in table' and this is done by inserting/deleting a record. Although possible, I'd recommend to use:

Code: Select all

# determine number of rows in table
res = cursor.execute("select count(*) from users;")
rows = cursor.fetchall()
print("number of rows", rows[0])

also possible: select max(id) from users; but not always the same as row count.

- if you want to handle the id column on your own, then you can create this different by omitting the primary key.
CREATE TABLE users( uid INTEGER type UNIQUE, name TEXT )
Then you are away from the database policies managing the primary keys. Although it is most times nice to have a separate primary key column for technical purpose. Then use
CREATE TABLE users( id integer primary key, uid INTEGER type UNIQUE, name TEXT )

[gordon77]

Thanks for the replies.

I have tried cursor.execute('PRAGMA auto_vacuum = FULL;') BEFORE and AFTER creating the TABLE, and cursor.execute('PRAGMA VACUUM;') BEFORE and AFTER the DELETE, all changed nothing.

I also tried..as a seperate script..

Code: Select all

import sqlite3
conn = sqlite3.connect('mydb7', isolation_level=None)
conn.execute("PRAGMA VACUUM")
conn.close()

again no luck.

I'll try what ghp suggests. I tried code similar to find the number of rows but I think it misses out the deleted rows so the max_id is incorrect.

[ghp]

Perhaps it needs to be clarified what the intended behavior of the application is.

When a database deletes a row, the data are lost (ok, still there on some files in disk for some time, possibly somewhere in a backup, but not available easily with sql statements).
Depending on how the database is working, new primary key will always increase or 'fill the gap'. Different databases have different strategies for this.

If the application needs to keep traces also of deleted entries (remember the user-id so new users never get an uid of previous users or to keep a 'delete time' and 'reason-for-delete' for the user), then the application has to handle an 'active/deleted-state'. Add a field 'deleted' of type integer (sqlite has no bools) and set this to 0 for active users and 1 for deleted users.

alter table users add column deleted integer default 0

Then you get all active entries with 'select ... from users where deleted=0', the deleted ones with '... with deleted = 1'.

[gordon77]

Thanks ghp,

Thanks for the info.

I can modify my script to ignore the 'deleted' rows, by checking they contain 'None' when retrieved. My concern is if the database gets large and with many deleted entries then it's occupying unnecessary disk space,but maybe that's not a problem.

I would still like to know why l can't get the vacuum statements to have any affect, maybe l am not clear what it is doing, but l certainly don't see any change in the database file size.

[ghp]

Hello, I have used vacuum in a database where there have been 100GB and more data.
A sample with prox 27 MB size:

Code: Select all

#!/usr/bin/python3

import os
import sqlite3
import time

dbname = "mydb_10.sqlite"

if os.path.exists(dbname):
    os.remove( dbname)
    
db = sqlite3.connect(dbname)
cursor = db.cursor()
cursor.execute(''' CREATE TABLE users( id integer primary key, uid INTEGER type UNIQUE, name TEXT ) ''')
db.commit()

    
db = sqlite3.connect(dbname)
cursor = db.cursor()

N = 20000
t0 = time.time()

for i in range(0,N):
    cursor.execute('''INSERT INTO users( uid, name) VALUES(?,?)''', (i, "test"* 256))
    if i % 100 == 0:
        db.commit()

db.commit()
t1 = time.time()

def report(comment, t0, t1, cursor):
    print("-----------------------------------------")
    print(comment, "time execution ", (t1-t0))
    cursor.execute( "select count(*), max(uid) from users;")
    r = cursor.fetchall()
    print(comment, "count, max(uid)", r)
    print(comment, "file size with", r[0][0], "entries:  {size:10,d}".format(size= os.path.getsize(dbname) ) )
    
report ("insert 20000    ", t0, t1, cursor)

t0 = time.time()
cursor.execute('''delete from users where uid < 18000''')
db.commit()
t1 = time.time()

report ("delete 0..18000 ", t0, t1, cursor)
      
t0 = time.time()
cursor.execute("vacuum;")
db.commit()
t1 = time.time()

report ("vacuum          ", t0, t1, cursor)

print("-----------------------------------------")
cursor.execute("select sqlite_version();")
r = cursor.fetchall()
print ("sqlite version ", r )

cursor.close()

The results are (not from a PI)

Code: Select all

-----------------------------------------
insert 20000     time execution  1.4560832977294922
insert 20000     count, max(uid) [(20000, 19999)]
insert 20000     file size with 20000 entries:  27,611,136
-----------------------------------------
delete 0..18000  time execution  0.3000171184539795
delete 0..18000  count, max(uid) [(2000, 19999)]
delete 0..18000  file size with 2000 entries:  27,611,136
-----------------------------------------
vacuum           time execution  0.10600638389587402
vacuum           count, max(uid) [(2000, 19999)]
vacuum           file size with 2000 entries:   2,772,992
-----------------------------------------
sqlite version  [('3.21.0',)]

[gordon77]

Thanks, that certain works, l'll have to establish where l'm going wrong.

[gordon77]

Thanks, now working
using...

cursor.execute("vacuum;")
db.commit()

I think part of the problem was the data I was entering was very small so the file didn't show any decrease until several entries deleted.

It didn't resolve the id numbering 'issue' but I worked around that.