473,581 Members | 6,787 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
Dec 15 '06 #1
29 2759
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

Dec 15 '06 #2
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

Dec 15 '06 #3
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

Dec 16 '06 #4

"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


Dec 16 '06 #5
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.
Dec 16 '06 #6

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

Dec 16 '06 #7
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
Dec 16 '06 #8
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

Dec 16 '06 #9

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

Dec 16 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
2373
by: pmud | last post by:
Hi, I am using teh following code for sorting the data grid but it doesnt work. I have set the auto generate columns to false. & set the sort expression for each field as the anme of that field... This grid displayes results based on users search.. public static int numberDiv; private void Page_Load(object sender, System.EventArgs e) {
6
1772
by: Jon Davis | last post by:
I like the drag-and-drop accessibility of dragging a table to a Web Forms designer and seeing a SqlDataAdapter automatically created for me.. being able to create a DataSet from that is fun and exciting, because now with this schema based data outline Visual Studio provides me with a typed class for managing data complete with Intellisense...
2
1456
by: mawi | last post by:
Hi there, When removing page children controls created dynamically not in last-to-first order, the close button of the last control looses its event wiring, even though the handler is rewired on each postback. It needs one postback roundtrip to "get it back". Form has an "add panel" button. Using it I dynamically add 3 panels with a...
9
1829
by: David Harris | last post by:
Ok, so I'm semi-new to .NET, having done everything manually with SQL code back in VB6. So before I program this up completely manually again, I thought I'd ask for better ways to think through this problem. We have several client machines, and a central data warehousing server. Each machine may contain hundreds of surveys, and they all are...
5
1967
by: Sanjay Pais | last post by:
I have a table with over 1.3 million rows. I am retrieving only 20 at a time using the with - over clauses In query analyser, the data is retrieved in under a second. When retrieving using the data adaptor.fill or datareader to retrieve the data it takes over 24 seconds. public System.Data.SqlClient.SqlDataReader List1(int PageIndex, int...
2
1526
by: ashish1985s | last post by:
using System.IO; using System.Data; using System.Collections; using System.Configuration; using System.Xml; using System.Data.SqlClient; using System; using log4net; using log4net.Config;
4
1370
by: Peter | last post by:
Hi I was wondering about the use of interfaces for "data classes". Actually I don't know the accepted term for these types of classes - they are simply classes which have getters and setters, to contain data and not really provide any functions. Is it worth defining interfaces for these types of classes, or is it "overkill"?
5
1789
by: indika | last post by:
Hi, I'm a newbie to python but have some experience in programming. I came across this requirement of using datetime.date objects associated with some another object. eg. a dictionary containing datetime.date =string { datetime.date(2001, 12, 3): 'c', datetime.date(2001, 12, 1): 'a', datetime.date(2001, 12, 2): 'b' }
1
8317
Manikgisl
by: Manikgisl | last post by:
But the problem is we have dates in Varchar instead Datetime While Converting Varchar To Datetime All four formats are unable to Convert ie select Convert(Datetime,'18-11-2008 2:35:19 PM',102) -- error
0
7854
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8134
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7880
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8157
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6539
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3807
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2295
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1394
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1119
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.