Syntax To Update A Blob Field In An Existing Sqlite Record?

What is the syntax to UPDATE a BLOB field in an existing SQLite record, using Python? I create a 13x13 array of floats and want to Update a specific record (i.e. using a WHERE clau

Solution 1:

The syntax for the SQL is :-

UPDATE mytable SET myblobcolumn = x'ffeedd'WHERE your_where_clause;

Where

• mytable is the table name,
• myblobcolumn is the name of the column that is to be updated,
• your_where_clause is the selection criteria,
• x'ffeedd' is the byte array value, converted to hexadecimal, that is to be used to update the column.

Obviously the above are only representations, you would have to substitute appropriate values

• SQL As Understood By SQLite - UPDATE

• Datatypes In SQLite Version 3

Solution 2:

A friend pointed me to this solution, which works nicely. The original answer was from StackOverflow, to give proper credit.

def adapt_array(arr): """ Reformat a numpy array so as to be writeable to SQLite BLOB fields Input: Numpy Array Returns: formatted Binary BLOB compatable Code Source: Python insert numpy array into sqlite3 database """ out = io.BytesIO() np.save(out, arr) out.seek(0) return sqlite3.Binary(out.read())

def convert_array(text): """ Reformat a SQLite BLOB field into the originating Numpy array Input: Numpy BLOB from SQLite Returns: numpy array Code Source: Python insert numpy array into sqlite3 database """ out = io.BytesIO(text) out.seek(0) return np.load(out)