How to call a Python function from a SQLite trigger

Question

This is my trigger

cursor.execute(
 '''CREATE TRIGGER IF NOT EXISTS Car_Park_row_
    BEFORE INSERT ON Car_Park
    WHEN (SELECT COUNT(*) FROM Car_PARK) >= 10
    BEGIN
        SELECT RAISE (ABORT, 'FULL');
        
        END;

and this is my function

def C(x):
    print('Error')

# Create Database
connector = sqlite3.connect('cparks.db')
connector.create_function("sql", -1, C)
cursor = connector.cursor()

I've limited my rows on my database to 10 with the trigger. What I need now is a message box or something along the lines of to appear to let the GUI user know that the table is full.

def C (x): print (‘Error’) is the python function. I think it says ‘maximum number of rows reached’ in the terminal when the trigger fires can’t say exactly because I’m not at my computer but the trigger works. I just need a message box or something along the lines of to appear to let the gui user know that the table is full. — chris fairhurst
– chris fairhurst, Commented Jan 10, 2022 at 2:17

mkrieger1 · Accepted Answer · 2022-01-10 10:37:10Z

Executing the RAISE() function in a query raises a sqlite3.IntegrityError exception in the Python code which you can handle like any other exception.

Example script:

import sqlite3

db = sqlite3.connect(':memory:')
db.executescript('''
    CREATE TABLE car_park (car);

    CREATE TRIGGER car_park_row
    BEFORE INSERT ON car_park
    WHEN (SELECT count(*) FROM car_park) >= 10
    BEGIN
        SELECT RAISE (ABORT, 'full');
    END;
''')

for i in range(15):
    car = f'car{i}'
    try:
        res = db.execute('insert into car_park values (?)', (car,))
    except sqlite3.IntegrityError as e:
        print(f'Could not insert {car}: {e}')

for row in db.execute('SELECT * FROM car_park'):
    print(row)

Output:

Could not insert car10: full
Could not insert car11: full
Could not insert car12: full
Could not insert car13: full
Could not insert car14: full
('car0',)
('car1',)
('car2',)
('car3',)
('car4',)
('car5',)
('car6',)
('car7',)
('car8',)
('car9',)

(It may be advisable to break out of the loop if an error occurs, it is not done here just for demonstration purposes.)

Instead of printing an error message, you can call any Python function in the exception handler. You do not need to add a user-defined function to the database to do this:

def handle_insert_car_error(car, error):
    create_message_box(f'Could not insert {car}: {error}')  # or whatever

Then:

# ...
    try:
        res = db.execute('insert into car_park values (?)', (car,))
    except sqlite3.IntegrityError as e:
        handle_insert_car_error(car, e)

That is a beautiful pythonic way to do this! Thanks for this answer @mkrieger1!

Collectives™ on Stack Overflow

How to call a Python function from a SQLite trigger

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related