[issue35145] sqlite3: "select *" should optionally sniff and autoconvert TEXT datetime fields

[Paul Ganssle]

Paul Ganssle <p.ganssle at gmail.com> added the comment:

With regards to automatically deducing column types, I am -1 on that. It has way too many dangerous edge cases, and I know of countless bugs in software that are the direct result of stringly-typed data being coerced into a specific data type based on its form. For example, if you have an excel column of alphanumeric strings and happen to get one that looks like "13943E1", that will be coerced (lossily) into the number 139430.0, because it happens to take the form of exponential notation.

> I contains a table `t` with a TEXT column `x` and a DATETIME (according to SQLiteStudio) column `y`.

It sounds to me like SQLiteStudio is doing the same thing that Python is doing, by extending the "types" available to include a DATETIME. Presumably they do it the same way, with "datetime" in the column name. If that's the case then it's just a mismatch between what they call their datetime adapter and what python does, and you just need to rename the relevant columns to say "timestamp" instead of "datetime".

As an aside, this is what we get from people not offering real datetime types in their standards. JSON has the exact same problem - people need to transmit datetimes but there's no way to do it in the standard, so everyone extends the standard in a sightly different way and you end up with a bunch of incompatible ways of storing datetimes as strings. Bah humbug.

----------

_______________________________________
Python tracker <report at bugs.python.org>
<https://bugs.python.org/issue35145>
_______________________________________