make_virtual_module fails when using TypesConverterCursorFactory

[alexneufeld]

I have this small script based on examples in the Example/Tour section of the documentation (1, 2)

import apsw
import apsw.ext

connection = apsw.Connection(":memory:")

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

def complex_to_sqlite_value(c: complex) -> str:
    return f"{ c.real }+{ c.imag }"

registrar.register_adapter(complex, complex_to_sqlite_value)

def sqlite_to_complex(v: str) -> complex:
    return complex(*(float(part) for part in v.split("+")))

registrar.register_converter("COMPLEX", sqlite_to_complex)

# this dataset works
# table_data = (3, 8, 1)
# this one fails
table_data = (3+4j, 8-2j, 1-5j)

def table_complex(start=0, stop=len(table_data)):
    for c in range(start, stop):
        yield {"col1": table_data[c]}

table_complex.columns = ("col1",)
table_complex.column_access = apsw.ext.VTColumnAccess.By_Name
apsw.ext.make_virtual_module(connection, "table_complex", table_complex)

query = """
    SELECT * FROM table_complex"""

print(apsw.ext.format_query_table(connection, query))

# clear registrar
connection.cursor_factory = apsw.Cursor

it fails with the following error on my system:

Traceback (most recent call last):
  File "/home/alex/conversion_and_virtual_tables.py", line 39, in <module>
    print(apsw.ext.format_query_table(connection, query))
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/site-packages/apsw/ext.py", line 661, in format_query_table
    for row in cursor.execute(query, bindings):
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/site-packages/apsw/ext.py", line 261, in execute
    return super().execute(statements,
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "src/vtable.c", line 2427, in VirtualTable.xColumn
TypeError: Bad return type from function callback

Is there a simple solution to this error, or do I need to look further into the documentation for virtual tables?

[rogerbinns]

Nothing to do with TypesConverterCursorFactory which you can see if you comment out setting it as cursor_factory.

Virtual tables are a SQLite thing which means they only operate on SQLite supported data types. I've changed the error message, so now you get this as the last line of the exception:

TypeError: Value from Python is not supported by SQLite. It should be one of None, int, float, str, or bytes. Received complex.

There is a repr_invalid parameter to make_virtual_table which would have turned the complex number into the string '(3+4j)' but that is repr output and not something the types converter would understand. The parameter is off by default because it is expensive checking every value in every column of every row returned.

You could change the yield line to this which would convert the value into something SQLite supports.

yield {"col1": registrar.adapt_value(table_data[c])}

But then on running you will get this where the values are the adapted ones (strings).

┌──────────┐
│   col1   │
│ 3.0+4.0  │
│ 8.0+-2.0 │
│ 1.0+-5.0 │
└──────────┘

The reason for that is the virtual table at the SQLite level does not have declared column types. Using apsw.ext.query_info will include this where the None is the declared column type:
description=(('col1', None),),

In short, this is trying really hard to pretend SQLite supports COMPLEX but that has to be done everywhere. If the yield line was changed and make_virtual_table did declare the column at the SQLite level to have type COMPLEX then it would work. But the latter is not something that will be changed in apsw because the library code is applicable to everyone.

You can however do it yourself. You can copy make_virtual_table, and where it gets the function signature you have to look at the annotation and turn that into whatever string you want in the SQL that creates the virtual table.

[rogerbinns]

Thinking about it a little more, I'd recommend you avoid make_virtual_table and implement your own virtual tables. That will provide precise control over exactly what values are provided to SQLite, as well as the table and column declarations for the virtual table which are necessary for TypesConverterCursorFactory to work as expected.

[alexneufeld]

Will do. Thanks for your help!