CODEWITHSUNDEEP
pythonsqlite
chris fairhurst
chris fairhurst

Reputation: 13

How to call a Python function from a SQLite trigger

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.

Upvotes: 1

Views: 1849

Answers (1)

mkrieger1
mkrieger1

Reputation: 23148

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)

Upvotes: 3

Related Questions