sqlite3 cursor description does not provide required type codes

[Mahesh Vashishtha]

Hi Python SQLite experts,

I was trying to use sqlalchemy to get the metadata of the result of arbitrary SQL queries on a SQLite table. Following the suggestion here, I tried to access the `description` attribute of the `cursor`. I created my table with:

```

sqlite3 /tmp/sqlite.db

CREATE TABLE table1(string_col TEXT, int_col INT);

pragma table_info('table1');

```

and saw the expected schema:

```

0|string_col|TEXT|0||0
1|int_col|INT|0||0

```

When I try to get the metadata for a query selecting all rows from the table with:

```

import sqlite3

connection = sqlite3.connect("/tmp/sqlite.db")

cursor = connection.execute("SELECT * FROM table1")

print([col for col in cursor.description])

```

I get out:

```

[('string_col', None, None, None, None, None, None), ('int_col', None, None, None, None, None, None)]

```

but according to the documentation of `Cursor.description` in PEP 249, the columns must each provide a `type_code` that compares correctly to sqlite3's type objects.

I originally tried to get the metadata through sqlalchemy, but the solution given in the StackOverflow answer linked above gives the same results:

```

('string_col', None, None, None, None, None, None)
('int_col', None, None, None, None, None, None)

```

I think that the bug in sqlite3 prevents sqlalchemy from getting the right type codes.

If this is indeed a bug, I'm happy to help fix it. I would need some pointers to the relevant source code, though.

[Roger Binns]

On 3/7/22 10:42, Mahesh Vashishtha wrote:
> I tried to access the `description` attribute of the `cursor`

I was rather surprised at what you got, but in testing can confirm the
builtin sqlite3 module does not return the type information. The SQLite
API call is sqlite3_column_decltype.

Here is description being built:

https://github.com/python/cpython/blob/main/Modules/_sqlite/cursor.c#L614

Here is the function where a row cast map is built using that API:


https://github.com/python/cpython/blob/main/Modules/_sqlite/cursor.c#L150
(line 195 for api call)

I had no idea why the sqlite3 module is doing things this way, until
finding this on bugs.python.org:

https://bugs.python.org/issue11691 (wontfix)

The reason given is correct - SQLite is *not* a typed database like
regular databases, and more like Python where you get out whatever type
you put in a row with some type hints and conversions possible.

In general that is correct - code that tries hard to treat SQLite as a
typed database using the description will make things increasingly
difficult for itself.

See the doc and notice 'Manifest typing':

https://sqlite.org/different.html

Roger

[Mahesh Vashishtha]

@rog...@rogerbinns.com,

Thanks very much for the detailed answer. I agree that sqlite3 should not attempt to give type codes in `cursor.description`. However, I do think that this caveat should be noted on the sqlite3 documentation page, which claims that sqlite3 "provides a SQL interface compliant with the DB-API 2.0 specification described by PEP 249." Should I draft an edit adding that caveat to the documentation's source on GitHub?

--

---
You received this message because you are subscribed to a topic in the Google Groups "python-sqlite" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/python-sqlite/5GsnQKmKNtg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to python-sqlit...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/python-sqlite/f5fba3e3-c4cd-83d4-2351-610ac374d058%40rogerbinns.com.

[Roger Binns]

On 3/8/22 20:06, Mahesh Vashishtha wrote:
> I agree that sqlite3 should
> not attempt to give type codes in `cursor.description`. However, I do

> think that this caveat should be noted on the sqlite3 documentation page ...

It gets worse! SQLite 3.37 (2021) added "strict" tables:

https://sqlite.org/stricttables.html

So it is possible to make SQLite look like less flexible databases, and
have the description be meaningful, although there will be still be
problems on computed result columns (eg SUM).

> the DB-API 2.0 specification described by PEP 249.

I believe that spec was written by people who were using Postgres,
MySQL, Oracle etc which is why SQLite doesn't map that well to it.

> Should I draft an edit adding that
> caveat to the documentation's source on GitHub

> <https://github.com/python/cpython/tree/3.10/Lib/sqlite3/>?

You'll need to ask on the Python dev groups. Note that Python is in the
process of changing bug trackers.

I solved the issues with DBAPI mismatches and pysqlite (as it was almost
20 years ago) by writing my own wrapper - APSW - which exposes SQLite as
it is.

https://rogerbinns.github.io/apsw/

The first doc page (tips) starts by pointing out how SQLite is
different. This page gives an idea of how DBAPI doesn't fit:

https://rogerbinns.github.io/apsw/dbapi.html

Roger

[Gerhard Häring]

You have already found my answer from 2015 closing this as "wontfix". Actually, I tried very hard when writing pysqlite to fill this field, but no matter what you did, there was no way to reliably get at the information. The concept is both incompatible with the way SQLite handles types and with the cursor-based approach of getting results.

As for putting a caveat in the module's docs, I personally don't think it's worth it.

PEP 249 is really a bastard when trying to bend sqlite3 to it. A great deal of effort was made to implement a transaction behaviour that is compatible with PEP 249; this introduced a problem or two in edge-cases along the way.

You received this message because you are subscribed to the Google Groups "python-sqlite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to python-sqlit...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/python-sqlite/CAM0zix_KUBOJkXTXQxR5zJ5eOaCquu2P2zqOb%3D1U4BZaJK2tgA%40mail.gmail.com.

[Gerhard Häring]

On Wed, Mar 9, 2022 at 5:49 PM Roger Binns <rog...@rogerbinns.com> wrote:

On 3/8/22 20:06, Mahesh Vashishtha wrote:
> I agree that sqlite3 should
> not attempt to give type codes in `cursor.description`. However, I do
> think that this caveat should be noted on the sqlite3 documentation page ...

It gets worse!  SQLite 3.37 (2021) added "strict" tables:

  https://sqlite.org/stricttables.html

So it is possible to make SQLite look like less flexible databases, and
have the description be meaningful, although there will be still be

problems on computed result columns (eg SUM). [...]

AFAIR there were multiple cases where the type information was not available:

- UNION of different queries

- any kind of NULL value

- others

Basically, you would have to fetch the whole resultset into memory to be able to (maybe) compute type information. And this would still only work some of the time. There absolutely is no way to do that reliably when interfacing sqlite3 from Python.

If I want to abstract the database away, I'll use SQLAlchemy or Django ORM, and not the PEP 249 interface.

Or I just stick the the sqlite3 module's behaviour, which I still use from time to time if I quickly need to analyse a dataset.

 -- Gerhard