18

SQLite has Data Change Notification Callbacks available in the C API. Can these callbacks be used from the SQLite CLI, or from Bash or from Python?

If so, how?

  • 1
    from Go lang ... https://godoc.org/code.google.com/p/go-sqlite/go1/sqlite3#Conn.UpdateFunc – Anentropic Mar 13 '14 at 21:05
  • 3
    On PyPI, apsw has bindings: http://rogerbinns.github.io/apsw/connection.html#apsw.Connection.setupdatehook – GrantJ Mar 13 '15 at 00:07
  • related: [Launch a Python Script from a sqlite3 Trigger](https://stackoverflow.com/q/33021255/4279) – jfs May 30 '18 at 13:24

1 Answers1

30

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

Reading through the SQLite source code, it doesn't look like that function is used anywhere in the CLI source code, so I doubt you can do it via the CLI.

...or from Bash...

Not sure what you mean by that.

...or from Python?

It's not exposed via the standard sqlite3 module, but you can use it with the ctypes module.

If so, how?

Here's a quick n' dirty example of using it via 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)

...which prints out...

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

...on the first run and...

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

...on the second run.

vy32
  • 28,461
  • 37
  • 122
  • 246
Aya
  • 39,884
  • 6
  • 55
  • 55
  • 1
    "Not sure what you mean by that." I mean, from a Bash script; e.g. one that wraps the SQLite CLI, or that intercepts arguments/options passed to the `sqlite3` command. –  Jun 06 '13 at 22:42
  • N.B. Your line `dll = CDLL('libsqlite3.so')` may need on some platforms to be changed to `dll = CDLL('libsqlite3.0.dylib')` –  Jun 06 '13 at 23:49
  • 1
    @sampablokuper Regarding the bash script comment: the SQLite CLI doesn't use the `sqlite3_update_hook()` function anywhere in its code, so I don't see how you could do it using a bash script which only wraps/intercepts the `sqlite3` command. – Aya Jun 07 '13 at 11:13
  • 1
    @sampablokuper Regarding `dll = CDLL('libsqlite3.0.dylib')`: I guess that's the OSX way, and you'd need to use something like `dll = CDLL('sqlite3.dll')` on Windows. – Aya Jun 07 '13 at 11:18
  • This doesn't seem to work if the database is updated in a different process. – vy32 Jan 11 '20 at 19:03
  • @vy32 That's a limitation of sqlite. See [this question](https://stackoverflow.com/q/677028). – Aya Jan 13 '20 at 16:47