Roundtrip SQL data especially datetime

[Hertha Steck]

Am Sat, 16 Dec 2006 16:31:18 -0500 schrieb Carsten Haese:

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.

Interbase / Firebird:

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

All you need, Windows binaries, .src.tar.gz for Python 2.3 - 2.5, Firebird
1.0 - 2.0.

 

[dyork]

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.

Thanks! Plenty of useful ideas there.

My web framework already does all the HTML stuff, so I don't need that.
Also, I note that alamode has hard coded dates in MDY order, which is
surprising given the Unicode support, and a real problem.

DY

 

[dyork]

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.

I'm currently using MySQLdb, but I'm looking for solutions that work
universally.

Binding objects is no different from literal strings. Since there is no
portable underlying type for an SQL date, the interface will AFAIK always
finish up using strings. At some point the SQL parser has to convert a
literal string, either embedded in the query or bound as a parameter, into
the equivalent date. I really hope the dbapi will know how to choose the
right string format so I don't have to, but so far that's not at all
certain.

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.

That's not feasible. For Web stuff, the object from the database got thrown
away after the page was rendered. We're dealing with a whole new request,
with little or no previous state, and all the dates coming in with the
request are strings, using formatting that depends on what the user wanted
to see. I need to package that into a form ready for either an INSERT or
UPDATE query. The user might have typed in dd-mmm-yy order, but the database
interface might use mm/dd/yyyy. It needs two conversion layers, and I would
rather use someone else's than write my own. Lazy, I guess.

DY

 

[dyork]

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.

Of course, no question about it.

However, the database is currently in MySQL and it's convenient to keep
working with it, given the other apps and other tools I'm using.

This would be the first time I've been told: don't use that database, the
language doesn't like it.

DY

 

[dyork]

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)

Having written adapters myself, I would not be confident that is true. It's
convenient to use native formats for floats and ints, and strings for
everything else. Regardless, you get trouble with (a) nulls (b) dates/times
(c) decimal/currency (d) precision mismatches (e) collation mismatches (f)
blobs (g) Unicode (h) special values like NaN. It takes great attention to
detail to be sure it all works, and I really don't want to write it (again).

I'd just like to choose some product X and "It Just Works"!

DY

 

[dyork]

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

It's not really a problem so far.

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.

Thanks! Very helpful. Actually, it's DATETIME I want and not TIMESTAMP, but
you certainly pointed me in the right direction.

Turns out the author of MySQLdb knows his onions, and virtually all of what
I need is in there. Seems it's critical to send in the right Python type to
trigger the right conversion routine to get the right result, and some of
the choices are not completely obvious. Still, the concept is good.

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

Whenever you touch SQL (a) you talk strings and (b) performance belongs on a
different planet. I can live with that.

DY

 

[dyork]

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.

Fortunately, I'm not offended. I know perfectly well that Python variables
are NOT typed, that Python relies on a dynamically typed model and most
other languages support static typing.I assume you know that too.

The advantage of static typing in this context is that the variable still
holds the type even if the value happens to be null. Any value that has been
exposed to user input comes back as a string and has to be validated and
converted to the correct data type. Static typing provides a convenient
place to generically find out what that type is, to drive a
validator/convertor. There are many ways to do the equivalent in Python, and
I'm interested in any suggestions that save me some work.

DY

 

[Diez B. Roggisch]

The advantage of static typing in this context is that the variable still
holds the type even if the value happens to be null. Any value that has been
exposed to user input comes back as a string and has to be validated and
converted to the correct data type. Static typing provides a convenient
place to generically find out what that type is, to drive a
validator/convertor. There are many ways to do the equivalent in Python, and
I'm interested in any suggestions that save me some work.

While this information in statically typed languages _can_ be used (but
not always is, in a web context), it also is available in the database
schema, which ultimately decides what it groks and what not.

But this is - especially in web-development - only half the truth.
Because even such a simple thing as reading a float value from the user
gets complicated in the presence of different locales. It buys you
nothing then to have static type declarations available.

A decent framework for webdevelopment, as e.g. TurboGears, allows you to
declare form field validation and coercion rules, thus on a higher
application level (your code), you only deal with the correctly typed
values.

Diez

 

[fumanchu]

Having written adapters myself, I would not be confident that is true. It's
convenient to use native formats for floats and ints, and strings for
everything else. Regardless, you get trouble with (a) nulls (b) dates/times
(c) decimal/currency (d) precision mismatches (e) collation mismatches (f)
blobs (g) Unicode (h) special values like NaN. It takes great attention to
detail to be sure it all works, and I really don't want to write it (again).

I'd just like to choose some product X and "It Just Works"!

Ah. Then feel free to have a look at Dejavu, wherein I've worked all
that out for you (okay, not NaN, but that wouldn't be hard).

A quick look over
http://projects.amor.org/dejavu/browser/trunk/storage/storemysql.py
shows proper string escaping, correct handling of 1/0 for True and
False, encoding, max precision, CURDATE, automatic scaling to BLOB
columns for bytes > 255, and BLOB index limitations. All of the Null,
date/time, decimal, unicode, and other type adaptation support is in
the generic
http://projects.amor.org/dejavu/browser/trunk/storage/geniusql.py

The same goes for Postgres, MS Access, SQLite, and SQL Server. Firebird
and sqlite3 are in the works.

Simple docs at http://projects.amor.org/docs/dejavu/


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

 

[John Machin]

Of course, no question about it.

However, the database is currently in MySQL and it's convenient to keep
working with it, given the other apps and other tools I'm using.

This would be the first time I've been told: don't use that database, the
language doesn't like it.

Simple fact: mySQLdb is not yet available for Python 2.5.
Nobody has said (or even hinted) that "the language doesn't like it."

 

[Dennis Lee Bieber]

UPDATE query. The user might have typed in dd-mmm-yy order, but the database
interface might use mm/dd/yyyy. It needs two conversion layers, and I would

Why is your user interface even giving the user the choice of input
format? Other than fancy genealogy programs, which support things like
"before <date>", "circa <date>", "between <date1> and <date2>", and dual
years for that overlap period when "new year" went from March to
January, you should be able to enforce the input format -- three numeric
fields labeled month, day, year, say...
--
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/

 

[GHUM]

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 for one think it is a good thing to not have MySQL adapters
integrated into Python core. Neither the philopsophie, nor the licence
of the two products fit together.

But maybe that's because I am a PostgreSQL zealot - and PostgreSQL and
Python is really a match made in heaven.

Harald

 

[John Nagle]

I for one think it is a good thing to not have MySQL adapters
integrated into Python core. Neither the philopsophie, nor the licence
of the two products fit together.

It's a wire protocol. At one end of a socket, there's MySQL, and
at the other end, there's a client. Neither side needs code from
the other. The protocol is published. So there's no license issue.

John Nagle

 

[Dennis Lee Bieber]

It's a wire protocol. At one end of a socket, there's MySQL, and
at the other end, there's a client. Neither side needs code from
the other. The protocol is published. So there's no license issue.

I think MySQLdb links to the MySQL C-client library, so there may be
that situation... Not sure as I've not looked at the source...
--
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/