python - Python 或 Bash 或 CLI 中的 SQLite 数据更改通知回调

Question

SQLite 有 Data Change Notification Callbacks在 C API 中可用。可以从 SQLite CLI、Bash 或 Python 使用这些回调吗？

如果是，怎么做到的？

Answer 1

Can these callbacks be used from the SQLite CLI...

通读 SQLite 源代码，在 CLI 源代码的任何地方似乎都没有使用该函数，所以我怀疑您是否可以通过 CLI 完成它。

...or from Bash...

不确定你的意思。

...or from Python?

它没有通过标准公开 sqlite3模块，但您可以将其与 ctypes 一起使用模块。

If so, how?

这是一个通过 ctypes 使用它的简单示例...

from ctypes import *

# Define some symbols
SQLITE_DELETE =  9
SQLITE_INSERT = 18
SQLITE_UPDATE = 23

# Define our callback function
#
# 'user_data' will be the third param passed to sqlite3_update_hook
# 'operation' will be one of: SQLITE_DELETE, SQLITE_INSERT, or SQLITE_UPDATE
# 'db name' will be the name of the affected database
# 'table_name' will be the name of the affected table
# 'row_id' will be the ID of the affected row
def callback(user_data, operation, db_name, table_name, row_id):
    if operation == SQLITE_DELETE:
        optext = 'Deleted row'
    elif operation == SQLITE_INSERT:
        optext = 'Inserted row'
    elif operation == SQLITE_UPDATE:
        optext = 'Updated row'
    else:
        optext = 'Unknown operation on row'
    s = '%s %ld of table "%s" in database "%s"' % (optext, row_id, table_name, db_name)
    print(s)

# Translate into a ctypes callback
c_callback = CFUNCTYPE(c_void_p, c_void_p, c_int, c_char_p, c_char_p, c_int64)(callback)

# Load sqlite3
dll = CDLL('libsqlite3.so')

# Holds a pointer to the database connection
db = c_void_p()

# Open a connection to 'test.db'
dll.sqlite3_open('test.db', byref(db))

# Register callback
dll.sqlite3_update_hook(db, c_callback, None)

# Create a variable to hold error messages
err = c_char_p()

# Now execute some SQL
dll.sqlite3_exec(db, b'create table foo (id int, name varchar(255))', None, None, byref(err))
if err:
    print(err.value)
dll.sqlite3_exec(db, b'insert into foo values (1, "Bob")', None, None, byref(err))
if err:
    print(err.value)

...打印出...

Inserted row 1 of table "foo" in database "main"

...在第一次运行时...

table foo already exists
Inserted row 2 of table "foo" in database "main"

...第二次运行。