Python SQLite avoid overcrowding by deleting the last item
Question: I use sqlite with python, when insert a new row I want to delete one of the
end

    
    
    conn.execute("INSERT INTO ORDERS (ORD_ID, TYPE) VALUES (?, ?)", [ord_id, type_n]);
    conn.commit()
    

> ID ID_ORD TYPE
>
> * * *
>
> 3 136984714 0 **< \--(-1)**
>
> 4 136982197 1
>
> 5 136983730 1
>
> 6 136984717 0 **< \--(+1)**

How it could be done this?


Answer: If your `ord_id` is always guaranteed to auto-increment you could do:

    
    
    import sqlite3
    
    conn = sqlite3.connect(':memory:')
    conn.execute('create table orders (ord_id, type);')
    conn.execute('insert into orders (ord_id, type) values (?,?);',(3,136984714))
    conn.execute('insert into orders (ord_id, type) values (?,?);',(4,136982197))
    conn.execute('insert into orders (ord_id, type) values (?,?);',(5,136983730))
    conn.execute('insert into orders (ord_id, type) values (?,?);',(6,136984717))
    conn.execute('delete from orders where ord_id = (select min(ord_id) from orders);')
    >>> conn.execute('select * from orders').fetchall()
    [(4, 136982197), (5, 136983730), (6, 136984717)]
    

As is mentioned in the comments you could alternately implement this as a
trigger:

    
    
    conn.execute('''CREATE TRIGGER delete_from_orders
                    AFTER INSERT ON orders
                    FOR EACH ROW
                    BEGIN
                        DELETE FROM orders WHERE ord_id = 
                            (SELECT MIN(ORD_ID) FROM orders);
                    END''')
    

