Creating tables with custom datatypes

Via https://stackoverflow.com/a/18622264/454773 I note the ability to register custom handlers for novel datatypes that can map into and out of things like sqlite BLOBs.

From a quick look and a quick play, I didn't spot a way to do this in sqlite_utils?

For example:

# Via https://stackoverflow.com/a/18622264/454773
import sqlite3
import numpy as np
import io

def adapt_array(arr):
    """
    http://stackoverflow.com/a/31312102/190597 (SoulNibbler)
    """
    out = io.BytesIO()
    np.save(out, arr)
    out.seek(0)
    return sqlite3.Binary(out.read())

def convert_array(text):
    out = io.BytesIO(text)
    out.seek(0)
    return np.load(out)


# Converts np.array to TEXT when inserting
sqlite3.register_adapter(np.ndarray, adapt_array)

# Converts TEXT to np.array when selecting
sqlite3.register_converter("array", convert_array)

from sqlite_utils import Database
db = Database('test.db')

# Reset the database connection to used the parsed datatype
# sqlite_utils doesn't seem to support eg:
#  Database('test.db', detect_types=sqlite3.PARSE_DECLTYPES)
db.conn = sqlite3.connect(db_name, detect_types=sqlite3.PARSE_DECLTYPES)

# Create a table the old fashioned way
# but using the new custom data type
vector_table_create = """
CREATE TABLE dummy 
    (title TEXT, vector array );
"""

cur = db.conn.cursor()
cur.execute(vector_table_create)


# sqlite_utils doesn't appear to support custom types (yet?!)
# The following errors on the "array" datatype
"""
db["dummy"].create({
    "title": str,
    "vector": "array",
})
"""

We can then add / retrieve records from the database where the datatype of the vector field is a custom registered array type (which is to say, a numpy array):

import numpy as np

db["dummy"].insert({'title':"test1", 'vector':np.array([1,2,3])})

for row in db.query("SELECT * FROM dummy"):
    print(row['title'], row['vector'], type(row['vector']))

"""
test1 [1 2 3] <class 'numpy.ndarray'>
"""

It would be handy to be able to do this idiomatically in sqlite_utils.

Start with the Database connection setup and the db.conn and db["dummy"].create() entry points shown in the issue, then trace how db.query() returns values. Check how SQLite connection options and declared column types are handled. Done means custom adapters and converters can be used idiomatically for inserting and retrieving values such as numpy arrays.