Roundtrip SQL data especially datetime

[Carsten Haese]

On Sat, 2006-12-16 at 04:27 +0000, dyork wrote:
> "John Machin" <sjmachin at lexicon.net> wrote in message 
> news:1166211949.065578.292600 at f1g2000cwa.googlegroups.com...
> > I suppose it all depends on your definition of obvious :-)
> I was looking for a constructor that was the complement of str(). Most/many 
> languages would provide that. Sometimes it's called parse().

We call it time.strptime.

> > 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:
> 
> But unobvious in a different way :). Thanks, I'll use that.
> 
> > If you have, as you should, Python 2.5, you can use this:
> 
> I would like to do that, but the tools I need are not released in 2.5 yet. 
> RSN!

In Python <2.5 you can use this clunky beast:

datetime.datetime(*time.strptime(s, '%Y-%m-%d %H:%M:%S')[:6])

> > 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.
> 
> Most values tend to work, but only because the SQL string representation 
> happens to be the same as the Python representation. That may not apply to 
> some float values, bool, perhaps others. I had hoped the tools would have 
> solved those problems so I don't have to. In typed languages (Java, C#) 
> those things tend to just work.

Python is a typed language, too, and "this thing" works just fine,
provided that you are using a reasonable DB-API implementation, and
provided that you're actually binding objects as parameters instead of
just sticking literal strings into your query.

When reading stuff from the database, keep the results in whatever form
they come. Convert to strings for display purposes if you must, but
don't overwrite the object you got from the database if you intend to
save it back into the database. If you need to save a datetime "from
scratch", construct an appropriate object and use it as a parameter to
your insert/update query. If the database module is DB-API 2.0
compliant, it provides a Timestamp factory function for constructing an
appropriate object.

Hope this helps,

Carsten.