Roundtrip SQL data especially datetime

[dyork]

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'. 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, and
having done so no obvious way to push that back into a SQL datetime column.
Am I missing something?

[I would particularly like to avoid getting trapped by SQL "local settings"
too.]

DavidY

[Gabriel Genellina]

On 15 dic, 07:44, "dyork" <reverse york...@david.c omwrote:

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'. No problem
with that -- all works quite nicely, until you try to put data back the
other way.

Dont convert to string and keep the datetime object.

--
Gabriel Genellina

[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.dateti me(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.dateti me(*a)
| >>dt
| datetime.dateti me(2005, 8, 3, 7, 32, 48)

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

| >>datetime.date time.strptime(s , '%Y-%m-%d %H:%M:%S')
| datetime.dateti me(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

[dyork]

Thanks Gabriel, but when I said "round trip" I really did mean: convert all
the way to string and all the way back again, so your kind advice is not all
that helpful. I need string to get to a non-Python object or a Web page.

DY

"Gabriel Genellina" <ga******@yahoo .com.arwrote in message
news:11******** *************@l 12g2000cwl.goog legroups.com...

On 15 dic, 07:44, "dyork" <reverse york...@david.c omwrote:

>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'. No
problem
with that -- all works quite nicely, until you try to put data back the
other way.

Dont convert to string and keep the datetime object.

--
Gabriel Genellina

[dyork]

"John Machin" <sj******@lexic on.netwrote in message
news:11******** **************@ f1g2000cwa.goog legroups.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().

The constructor is datetime.dateti me(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!

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.

DY

[Carsten Haese]

On Sat, 2006-12-16 at 04:27 +0000, dyork wrote:

"John Machin" <sj******@lexic on.netwrote in message
news:11******** **************@ f1g2000cwa.goog legroups.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.dateti me(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.dateti me(*time.strpti me(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.

[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.dateti me 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.com bine(date,dt.ti me(0)) # convert to
datetime
return dt.datetime.fro mtimestamp(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

[John Nagle]

dyork wrote:

"John Machin" <sj******@lexic on.netwrote in message
news:11******** **************@ f1g2000cwa.goog legroups.com...
I was looking for a constructor that was the complement of str(). Most/many
languages would provide that. Sometimes it's called parse().

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

>

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

Actually, MySQLdb isn't released for Python 2.5 yet, so for
anything with a database, you need an older version of Python.

If you really want to change the conversions for TIMESTAMP, add the
"conv" argument to "connect". Make a copy of "MySQLdb.conver ters.conversion s",
then replace the key "MySQLdb.FIELD_ TYPE.TIMESTAMP" , which normally has
the value 'mysql_timestam p_converter' with your own converter. You can
then get the interface to emit a "datetime" object.

Routinely converting MySQL DATETIME objects to Python "datetime"
objects isn't really appropriate, because the MySQL objects have a
year range from 1000 to 9999, while Python only has the UNIX range
of 1970 to 2038. Remember, a database may have DATETIME dates which
reflect events in the distant past or future.

None of this will help performance; dates and times are sent over the
connection to a MySQL database as strings.

John Nagle

[Leo Kislov]

John Nagle wrote:

Routinely converting MySQL DATETIME objects to Python "datetime"
objects isn't really appropriate, because the MySQL objects have a
year range from 1000 to 9999, while Python only has the UNIX range
of 1970 to 2038.

You're mistaken. Python datetime module excepts years from 1 up to
9999:

>>datetime.MINY EAR

1

>>datetime.MAXY EAR

9999

-- Leo

[John Machin]

John Nagle wrote:

dyork wrote:

"John Machin" <sj******@lexic on.netwrote in message
news:11******** **************@ f1g2000cwa.goog legroups.com...

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

Actually, MySQLdb isn't released for Python 2.5 yet

Actually, that's interesting information [why should it take so long?],
but the OP didn't actually say what brand of database he was actually
using :-)

Cheers,
John