Roundtrip SQL data especially datetime

[John Machin]

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?
>
> An SQL datetime column translates nicely into a Python datetime (surprise),
> which then translates into a string like '2005-08-03 07:32:48'.

It doesn't translate itself. You translated it. As Gabriel has said,
don't do that.

> No problem
> with that -- all works quite nicely, until you try to put data back the
> other way.

> There is no obvious way to parse that string back into a datetime,

I suppose it all depends on your definition of obvious :-)

The constructor is datetime.datetime(year, ....., second) so the
following (which works all the way back to Python 2.3) seems not too
obscure to me:

| >>> import datetime
| >>> s = '2005-08-03 07:32:48'
| >>> a = map(int, s.replace('-', ' ').replace(':', ' ').split())
| >>> a
| [2005, 8, 3, 7, 32, 48]
| >>> dt = datetime.datetime(*a)
| >>> dt
| datetime.datetime(2005, 8, 3, 7, 32, 48)

If you have, as you should, Python 2.5, you can use this:

| >>> datetime.datetime.strptime(s, '%Y-%m-%d %H:%M:%S')
| datetime.datetime(2005, 8, 3, 7, 32, 48)

> and
> having done so no obvious way to push that back into a SQL datetime column.

How do you push a str or float object back into an SQL column of
appropriate type? What's the difference? Your DB API should handle this
quite transparently. Try it and see what happens.

HTH,
John