Launch a Python Script from a sqlite3 Trigger

问题:

Is it possible to have sqlite3 have a trigger that will launch a python script? I have a table that has a list of items for which I am monitoring reviews on a web page. Some items don't have any. I have a script that checks the items on a regular basis and updates the table of items based on what it finds for each item. I would like to have it so that when the table of items is updated a trigger can kick off a python script that can then run and update other tables based on data it pulls from other sites.

Is this even possible?

回答1:

SQL statements (also inside triggers) can call user-defined functions, which are created with create_function:

import sqlite3

def hello(x):
    print "Hello"

con = sqlite3.connect(":memory:")
con.create_function("hello", 1, hello)
cur = con.cursor()
cur.execute("CREATE TABLE t(x)")
cur.execute("CREATE TRIGGER tt AFTER INSERT ON t BEGIN SELECT hello(NEW.x); END;")
cur.execute("INSERT INTO t VALUES(1)")

The function is allowed to change the database; the documentation says:

An application-defined function is permitted to call other SQLite interfaces. However, such calls must not close the database connection nor finalize or reset the prepared statement in which the function is running.