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
--
http://mail.python.org/mailman/listinfo/python-list
