Dict Return or DataClassRow not workign

[Rajat Sethi]

Hi,

First of all enjoying apsw. Looks great.

However I am struggling with below code where would like to return the data as dict or dataclass but get only tuple. Any help would be appreciated.

import apsw.ext
import apsw.bestpractice
from datetime import datetime, date


def datetime_to_sqlite_value(dt: datetime) -> float:
return abs(dt.timestamp())

def date_to_sqlite_value(dt: date) -> int:
return dt.toordinal()

def sqlite_value_to_datetime(dt: float) -> datetime:
return datetime.fromtimestamp(dt)

def sqlite_value_to_date(dt: int) -> date:
return date.fromordinal(dt)

def row_factory(cursor, row):
columns = [t[0] for t in cursor.getdescription()]
return dict(zip(columns, row))


apsw.bestpractice.apply(apsw.bestpractice.recommended)
connection = apsw.Connection(":memory:")


connection.setrowtrace(row_factory)
registrar = apsw.ext.TypesConverterCursorFactory()
connection.cursor_factory = registrar
# connection.row_trace = apsw.ext.DataClassRowFactory()

registrar.register_adapter(datetime, datetime_to_sqlite_value)
registrar.register_adapter(date, date_to_sqlite_value)
registrar.register_converter('date', sqlite_value_to_date)
registrar.register_converter('datetime', sqlite_value_to_datetime)

connection.execute("""Create table if not exists t1 (id integer primary key autoincrement, b1 text, b2 integer, d1 text, d2 integer);""")
connection.execute("""insert into t1 (b1,b2,d1,d2) values (?,?,?,?)"""
, (True, True, date.today(), datetime.now()))

for row in connection.execute("""SELECT * from t1"""):
print(row)

[Roger Binns]

You are setting a row tracer here:

> connection.setrowtrace(row_factory)

And also set a cursor factory here.

> registrar = apsw.ext.TypesConverterCursorFactory()
> connection.cursor_factory = registrar

The way the TypesConverterCursorFactory works is by registering a row tracer for its cursors which will take precedence over the connection one. You can see that code here (line 246 is what causes the tuple).

https://github.com/rogerbinns/apsw/blob/master/apsw/ext.py#L237

If you wanted a dict, you'd need to copy/inherit from that code and do what you want there.

The good news is that the next APSW release is going to make this way easier. I'm developing the code and especially the test suite right now. It works with SQLite's JSON support - https://sqlite.org/json1.html

In particular SQLite has a binary JSON format which avoids having to parse JSON strings repeatedly, and APSW has code to automatically detect that format (JSONB).

You would write a converter where for example a datetime could be represented like this:

{ "$type": "datetime", "$value": "1757088099.227378" }

You will be able to register a converter that is called when bindings include a non-supported SQLite value. (The converter can be registered against apsw, the connection, or the cursor.)

See the default parameter of json.dumps:

https://docs.python.org/3/library/json.html#json.dump

You can either call the json module with your default converter which will produce a JSON text representation like above. Or your can call apsw's jsonb converter with the exact same default converter which will produce a SQLite JSONB blob. Either gets saved to the database.

Now see the object_hook parameter of json.loads:

https://docs.python.org/3/library/json.html#json.load

You would want an object hook that recognises the $type dict like above and convert it back to a datetime.

You will be able to register a converter for values coming back out of SQLite that are valid JSONB (blobs) and provide an object_hook. I am not currently intending to auto-recognise JSON (text) since that is a lot trickier.

The upshot of this is that it should be a lot easier using JSON to represent values, and conversion into and out of JSONB will be automagic. It will also free up the row factory so that you can get your dicts instead of tuples :)

There will be an example doc page for JSON which will use datetime as demonstration.

What do you think?

Roger

[Rajat Sethi]

I guess so :) I'll await the release to try it out. Meanwhile I'll try and see what I can do to inherit from TypesConverterCursorFactory and change `def _rowtracer` from `TypeConverterCursor`. Inner class definitely makes it more tricky though.

[Roger Binns]

> I guess so :) I'll await the release to try it out.

You can do JSON now (manually) to see how that fits. If you or anyone else does, I am keen to hear about the experience.

> Meanwhile I'll try
> and see what I can do to inherit from TypesConverterCursorFactory

You can copy the whole segment of code into your own file, and edit that. (The license allows it.) You would then use myfile.TypesConverterCursorFactory instead of apsw.ext.TypesConverterCursorFactory.

Roger

[Rajat Sethi]

I was able to get a `dict` out by patching `etx.py`.

def _rowtracer(self, cursor: apsw.Cursor, values: apsw.SQLiteValues) -> tuple[Any, ...]:
return {d[0] : self.factory.convert_value(d[1], v) for d, v in zip(cursor.get_description(), values)}

The above code returns : {'id': 1, 'b1': '1', 'b2': 1, 'd1': '739499', 'd2': 1757091012.330519} which is what I am looking at.

The issue which I am having is how'd I go about inheriting TypesConverterCursorFactory and changing the of a function of a inner class?

If you can shed any light, that'd be greatly appreciated.

---

On your earlier comment on bringing out data as json/jsonb seems like a performance penalty if i understood it correctly. You'd serialize it and then de-serialize it based on the type conversion provided by the user. Did I understand it correctly?

[Rajat Sethi]

 >  You can copy the whole segment of code into your own file, and edit that. (The license allows it.) You would then use myfile.TypesConverterCursorFactory instead of apsw.ext.TypesConverterCursorFactory.  

Thank you, that makes it simple :)