sqlite JSON null's raise `RuntimeError: Cannot infer type from null for SQLite`

[sslivkoff]

When python connectorx is used to query a sqlite JSON column full of null entries, it raises RuntimeError: Cannot infer type from null for SQLite. This issue does not occur for other sqlite column types.

Minimal Example:

import sqlite3
import connectorx

# initialize data
with sqlite3.connect('test.db') as conn:
    conn.execute('CREATE TABLE records ( name TEXT, access_list JSON )')
    for name in ['hi', 'bye', 'yes', 'no']:
        conn.execute('INSERT INTO records (name) VALUES (?)', [name])
    
# show data
with sqlite3.connect('test.db') as conn:
    result = conn.execute('SELECT * FROM records')
print('rows in table:')
for row in result.fetchall():
    print(row)

>>> rows in table:
>>> ('hi', None)
>>> ('bye', None)
>>> ('yes', None)
>>> ('no', None)

# read using connectorx
connectorx.read_sql('sqlite://test.db', 'SELECT * FROM records', return_type='polars')

>>> RuntimeError: Cannot infer type from null for SQLite

This exception is raised for each possible return_type

It would be nice to be able to pass a types argument to read_sql(..., types) or have a default fallback to avoid raising an Exception