Add db type as attribute

[robmarkcole]

As pointed out by @balloob 'Most DBAPIs have built in support for the datetime module, with the noted exception of SQLite. In the case of SQLite, date and time types are stored as strings which are then converted back to datetime objects when rows are returned.' Detective should have the db type as an attribute, allowing for db type specific behaviour if required.

[balloob]

The problem is that SQLAlchemy only sees the DB schema stored in the DB. In Home Assistant we have SQLAlchemy objects describing the data, and we can tell it to see a column as datetime. SQLLite has no datetime type, so returns that column as a string because SQLAlchemy doesn't know any better.

[robmarkcole]

I think the best approach is just to detect if a string has been returened where a datetime is expected, then parse appropriately. I think SQLAlchemy & ORM is overkill for the timebeing, and we have the HA rest API for complex interactions with entities

[balloob]

I agree with it being overkill, it will add too much overhead.

Where would you want to do the detection ? We can extract the type from the DB url.

from urllib.parse import urlparse
type = urlparse(db_url).scheme