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]

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.

 

[John Machin]

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

 

[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

 

[dyork]

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.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!

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]

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.

But unobvious in a different way . Thanks, I'll use that.


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])

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]

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

 

[John Nagle]

I was looking for a constructor that was the complement of str(). Most/many
languages would provide that. Sometimes it's called parse().

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.converters.conversions",
then replace the key "MySQLdb.FIELD_TYPE.TIMESTAMP", which normally has
the value 'mysql_timestamp_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]

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:
9999

-- Leo

 

[Dennis Lee Bieber]

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.

If you actually look at what the various DB-API adapters produce
when sending to the database engine, floats, bools, etc. are all sent as
string representations; about the only source for problems would be
involved in the number of significant digits transferred for a float
(you might feed 15 digits in, and only get 7 or 10 back)

And you should be able to transfer datetime using variations of
strptime() and strftime() [or whatever the names are]... If you don't
have it in the datetime module, the variation in the time module might
work, at least long enough to get from string to individual fields which
can be converted to a datetime object...

--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[Dennis Lee Bieber]

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

Pardon? Agree if the database engine is MySQL. And even then, one
could probably use an ODBC (on Windows) DSN with an ODBC adapter to get
to MySQL.

But, unless someone knows otherwise, there may be P2.5 adapters
available for Firebird/Interbase, PostgreSQL, MS SQL Server... And 2.5
comes with SQLite standard.

--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[John Machin]

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

 

[fumanchu]

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.

Then you need two adaptation layers: one between your app and the DB
(which you already have) and one between your app and the web page or
other user interface.

Here's the web adaptation layer I use:
http://projects.amor.org/misc/browser/alamode.py
Have a look at the coerce_in and coerce_out functions.


Robert Brewer
System Architect
Amor Ministries
(e-mail address removed)

 

[John Nagle]

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

True.

Why should it take so long? The Python people don't support the
MySQL interface, and the MySQL people don't support the Python interface.
There's one guy on Sourceforge doing the MySQLdb glue code. And he's busy.

This is unusual for database bindings. The standard Perl distribution
supports MySQL and several other databases, so you can use MySQL
out of the box, and it's tested for new releases. The standard MySQL
distribution supports PHP, Java, etc., so that's standard and gets
tested.

With Python/MySQL, though, neither side takes responsibility
for making that binding work.

One side effect of this being third party code is that hosting
services may not have it available, even when they have both Python
and MySQL up. This is never a problem with Perl or PHP, so that's
a negative for Python. I'm currently trying to get "EZpublishing",
usually a good hosting service, to fix this on their systems.

There are also some recent problems between versions of
Apache, mod_python, MySQLdb, Python, and MySQL, some of which can
crash a web server. (Ref:
http://packages.debian.org/changelogs/pool/main/p/python-mysqldb/python-mysqldb_1.2.1-p2-4/changelog)
Not sure if this has been resolved yet.

John Nagle

 

[Marc 'BlackJack' Rintsch]

dyork wrote:


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

True.

Why should it take so long? The Python people don't support the
MySQL interface, and the MySQL people don't support the Python interface.
There's one guy on Sourceforge doing the MySQLdb glue code. And he's busy.

AFAIK there's no MySQLdb module for Python 2.5 on *Windows* yet, because
the author of the module doesn't use Windows anymore and he don't want to
maintain a binary he can't test.

Ciao,
Marc 'BlackJack' Rintsch

 

[John Nagle]

John Nagle wrote:

dyork wrote:




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

True.

Why should it take so long? The Python people don't support the
MySQL interface, and the MySQL people don't support the Python interface.
There's one guy on Sourceforge doing the MySQLdb glue code. And he's busy.

AFAIK there's no MySQLdb module for Python 2.5 on *Windows* yet, because
the author of the module doesn't use Windows anymore and he don't want to
maintain a binary he can't test.

The SourceForge page

http://sourceforge.net/project/showfiles.php?group_id=22307

says

"MySQL versions 3.23-5.0; and Python versions 2.3-2.4 are supported."

Last release was April, 2006. There's no binary for Python 2.5 yet.

See the help discussions for the project. Apparently you can build
it for Python 2.5 from the SVN repository and it can be made to work,
but just building it may not work on your platform. Some edits
may be required. Problems have been reported with the Windows version
and the 64-bit Linux version.

These are all normal development issues. But they're not fully resolved
yet. So users should hold off from using Python 2.5 in production
database applications.

John Nagle

 

[Carsten Haese]

The SourceForge page

http://sourceforge.net/project/showfiles.php?group_id=22307

says

"MySQL versions 3.23-5.0; and Python versions 2.3-2.4 are supported."

Last release was April, 2006. There's no binary for Python 2.5 yet.
[...]
So users should hold off from using Python 2.5 in production
database applications.

This may come as a shock to you, but MySQL is not the only database
engine on the planet. Your recommendation may apply to MySQL, but it is
not true for all databases in general. I can name at least two examples
(Informix and Oracle) of database engines that are supported under
Python 2.5, and if I were less lazy I could probably find more.

-Carsten

 

[John Machin]

The SourceForge page

http://sourceforge.net/project/showfiles.php?group_id=22307

says

"MySQL versions 3.23-5.0; and Python versions 2.3-2.4 are supported."

Last release was April, 2006. There's no binary for Python 2.5 yet.
[...]
So users should hold off from using Python 2.5 in production
database applications.

This may come as a shock to you, but MySQL is not the only database
engine on the planet. Your recommendation may apply to MySQL, but it is
not true for all databases in general. I can name at least two examples
(Informix and Oracle) of database engines that are supported under
Python 2.5, and if I were less lazy I could probably find more.

Not sure if sqlite qualifies as an "engine", but it works just fine
with Python 2.5. Heck, it's even supplied in the standard python.org
distribution, Windows DLL and all and all ...

 

[Diez B. Roggisch]

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.

Not true. There might be frameworks that aid this process - as there are
for python (e.g. TurboGears validators), but especially when it comes to
dates, even the larger ones like Struts for Java pretty much suck.

Diez

 

[Fredrik Lundh]

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.

if you think that Python isn't typed, you've completely missed how
things work. your problem is that you're removing every trace of the
type information by casting everything to strings, not that Python
itself (nor the database adapters) cannot handle typed data.

</F>