This relates to the fact that dictionaries, lists and tuples get special treatment and are converted to JSON strings, using this code:

sqlite-utils/sqlite_utils/db.py

Lines 2937 to 2947 in e8d9581

	def jsonify_if_needed(value):
	if isinstance(value, decimal.Decimal):
	return float(value)
	if isinstance(value, (dict, list, tuple)):
	return json.dumps(value, default=repr, ensure_ascii=False)
	elif isinstance(value, (datetime.time, datetime.date, datetime.datetime)):
	return value.isoformat()
	elif isinstance(value, uuid.UUID):
	return str(value)
	else:
	return value

So the COLUMN_TYPE_MAPPING should include those too - right now it looks like this:

sqlite-utils/sqlite_utils/db.py

Lines 165 to 188 in e8d9581

	COLUMN_TYPE_MAPPING = {
	float: "FLOAT",
	int: "INTEGER",
	bool: "INTEGER",
	str: "TEXT",
	bytes.__class__: "BLOB",
	bytes: "BLOB",
	memoryview: "BLOB",
	datetime.datetime: "TEXT",
	datetime.date: "TEXT",
	datetime.time: "TEXT",
	decimal.Decimal: "FLOAT",
	None.__class__: "TEXT",
	uuid.UUID: "TEXT",
	# SQLite explicit types
	"TEXT": "TEXT",
	"INTEGER": "INTEGER",
	"FLOAT": "FLOAT",
	"BLOB": "BLOB",
	"text": "TEXT",
	"integer": "INTEGER",
	"float": "FLOAT",
	"blob": "BLOB",
	}

Originally posted by @simonw in #322 (comment)