Roundtrip SQL data especially datetime

[Frank Millman]

dyork wrote:
> When getting data from a database using the dbapi and an SQL query, how do
> you in general round trip the data? Especially date-time?
>

This is what I do. I am posting this partly because I hope it helps,
partly because it is a bit clunky and I would appreciate suggestions
for improvements.

I have two constraints.

1. I support PostgreSQL using psycopg, which handles datetime objects
very well, and MS SQL Server using pywin32.odbc, which does not handle
datetime objects at all.

2. PostgreSQL has datatypes for 'timestamp' and for 'date'. I use the
former for things like 'time/date record was created', and the latter
for things like 'invoice date'. However, internally in my app, I only
want to use datetime.datetime objects.

I agree with the principle that dates should only be stored internally
as datetime objects, but I also allow None where the database value is
null. To achieve this I use the following -

    import datetime as dt

    def dbToDate(date):
        if date is None:
            return date
        if isinstance(date,dt.datetime):  # psycopg can return this
type
            return date  # already in datetime format
        if isinstance(date,dt.date):  # psycopg can return this type
            return dt.datetime.combine(date,dt.time(0))  # convert to
datetime
        return dt.datetime.fromtimestamp(int(date))  # win32/odbc
returns type DbiDate

When writing the date back to the database, I cannot pass the datetime
object directly, as pywin32.odbc does not recognise this. I have found
that str(date) - where date is a datetime object - converts it into a
string that is acceptable to both PostgreSQL and MS SQL Server.

HTH

Frank Millman