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

[Robert Pollak]

Robert Pollak <robert.pollak at posteo.net> added the comment:

Paul, the sniffing would be only active for people who explicitly add a connect() argument like detect_types=DETECT_DATETIME, which can have the appropriate warning in the docs. You can also extend the sniffing to all values, not just the first non-missing one. (I'd gladly pay the computational price.) How many cases are there where a column is full of `YYYY-MM-DD HH:MM:SS.SSS` data, DETECT_DATETIME is switched on, and the user _doesn't_ want this column to be interpreted as datetime?

(I'm of course not suggesting to try detecting REAL as Julian day, or INTEGER as Unix Time.)

Forget about my test file, by the way:
I have now found out that I can extract the DATETIME type information from my test file SQLiteStudio-3449.sqlite in the following two ways:
```
In [3]: pd.read_sql_query('pragma table_info("t")', con)
Out[3]: 
   cid name      type  notnull dflt_value  pk
0    0    x      TEXT        0       None   0
1    1    y  DATETIME        0       None   0

In [4]: pd.read_sql_query('SELECT SQL FROM sqlite_master WHERE name = "t"', con)
Out[4]: 
                                   sql
0  CREATE TABLE t (x TEXT, y DATETIME)
```
For my real use case however, those two statements yield empty results :-(

----------

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