Roundtrip SQL data especially datetime
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 -- http://mail.python.org/mailman/listinfo/python-list
Re: Roundtrip SQL data especially datetime
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" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On 15 dic, 07:44, "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'. 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 > -- http://mail.python.org/mailman/listinfo/python-list
Re: Roundtrip SQL data especially datetime
"John Machin" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: Roundtrip SQL data especially datetime
"fumanchu" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: Roundtrip SQL data especially datetime
"Carsten Haese" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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/. It needs two conversion layers, and I would rather use someone else's than write my own. Lazy, I guess. DY -- http://mail.python.org/mailman/listinfo/python-list
Re: Roundtrip SQL data especially datetime
"Carsten Haese" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: Roundtrip SQL data especially datetime
"Dennis Lee Bieber" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: Roundtrip SQL data especially datetime
"John Nagle" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] >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 -- http://mail.python.org/mailman/listinfo/python-list
Re: Roundtrip SQL data especially datetime
"Fredrik Lundh" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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 -- http://mail.python.org/mailman/listinfo/python-list
