Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > Database Timestamp conversion error

Reply
Thread Tools

Database Timestamp conversion error

 
 
kyosohma@gmail.com
Guest
Posts: n/a
 
      04-06-2007
Hi,

I am populating a mySQL database with data from the MS Access
database. I have successfully figured out how to extract the data from
Access, and I can insert the data successfully into mySQL with Python.
My problem is that I keep hitting screwy records with what appears to
be a malformed dbiDate object when I insert certain records. I get the
following traceback:

Traceback (most recent call last):
File "\\someServer\Development\collectiveFleet.py", line 68, in -
toplevel-
mycursor.execute(sql)
TypeError: argument 1 must be string without null bytes, not str

When I print the timestamp variable, I get this output:

(I31
(S'OK'
p1
Nttp2
..

If I look in the MS Access database, I see the timestamp as "5/6/112".
Obviously some user didn't enter the correct date and the programmer
before me didn't give Access strict enough rules to block bad dates.
How do I test for a malformed date object so I can avoid this? There
are thousands of records to transfer.

I am using the odbc module for connection purposes with Python 2.4 on
Windows XP SP2.

Thanks a lot!

Mike

 
Reply With Quote
 
 
 
 
attn.steven.kuo@gmail.com
Guest
Posts: n/a
 
      04-06-2007
On Apr 6, 1:48 pm, (E-Mail Removed) wrote:

(snipped)

> If I look in the MS Access database, I see the timestamp as "5/6/112".
> Obviously some user didn't enter the correct date and the programmer
> before me didn't give Access strict enough rules to block bad dates.
> How do I test for a malformed date object so I can avoid this? There
> are thousands of records to transfer.
>


time.strptime ?


import time
for date in ("5/6/2008", "5/6/118"):
try:
struct_tm = time.strptime(date, "%m/%d/%Y")
print "Good date: " + date
print struct_tm
except ValueError:
print "Bad date: " + date

--
Hope this helps,
Steven

 
Reply With Quote
 
 
 
 
John Machin
Guest
Posts: n/a
 
      04-06-2007
On Apr 7, 6:48 am, (E-Mail Removed) wrote:
> Hi,
>
> I am populating a mySQL database with data from the MS Access
> database. I have successfully figured out how to extract the data from
> Access, and I can insert the data successfully into mySQL with Python.
> My problem is that I keep hitting screwy records with what appears to
> be a malformed dbiDate object when I insert certain records. I get the
> following traceback:


Ummm ... I didn't start using Python on databases till after DB API
2.0 came out (1999) so please pardon my ignorance, but isn't dbiDate
something that was in API 1.0 but vanished in API 2.0 [e.g. its
mentioned only briefly in the history section of the current mxODBC
docs]?

If that's what you are still using:
(a) I can't imagine how printing a dbiDate object would give such a
garbled result -- try:

print type(obj)
print repr(obj)
for both a "bad" obj and a "good" obj.

(b) The API 1.0 docs give a clue:
"""
dbiDate(value)

This function constructs a 'dbiDate' instance that holds a
date value. The value should be specified as an integer
number of seconds since the "epoch" (e.g. time.time()).
"""
and googling brought up a few hits mentioning that not handling dates
earlier that the "epoch" (1970-01-01T00:00:00) was a limitation.

So: if you are calling dbiDate yourself, you can inspect its input
argument; presumably a date in the year 112 will show up as negative.


>
> Traceback (most recent call last):
> File "\\someServer\Development\collectiveFleet.py", line 68, in -
> toplevel-
> mycursor.execute(sql)
> TypeError: argument 1 must be string without null bytes, not str
>
> When I print the timestamp variable, I get this output:
>
> (I31
> (S'OK'
> p1
> Nttp2
> .
>
> If I look in the MS Access database, I see the timestamp as "5/6/112".
> Obviously some user didn't enter the correct date and the programmer
> before me didn't give Access strict enough rules to block bad dates.
> How do I test for a malformed date object so I can avoid this?
> There
> are thousands of records to transfer.
>
> I am using the odbc module for connection purposes with Python 2.4 on
> Windows XP SP2.


If this is the odbc module that comes in the win32all package:
1. There are much better options available on Windows e.g. mxODBC.
2. Doesn't document dbiDate objects AFAICT.

If your SELECT from the Access db is returning you "seconds since the
epoch" values, proceed as I suggested earlier.

If it is returning you dbiDate objects directly, find out if the
dbiDate obj has any useful attributes or methods e.g.

obj.date_as_tuple() -> (2007, 4, 7, ...)
or
obj.year -> 2007
obj.month -> 4
etc

How to find out: insert code like
print dir(obj)
in your script and inspect the output for likely attribute/method
names.

And if that doesn't help, abandon the odbc module and use e.g. mxODBC
or Python adodb.

Hope some of this helps,
John

 
Reply With Quote
 
kyosohma@gmail.com
Guest
Posts: n/a
 
      04-07-2007
On Apr 6, 6:20 pm, "John Machin" <(E-Mail Removed)> wrote:
> On Apr 7, 6:48 am, (E-Mail Removed) wrote:
>
> > Hi,

>
> > I am populating a mySQL database with data from the MS Access
> > database. I have successfully figured out how to extract the data from
> > Access, and I can insert the data successfully into mySQL with Python.
> > My problem is that I keep hitting screwy records with what appears to
> > be a malformed dbiDate object when I insert certain records. I get the
> > following traceback:

>
> Ummm ... I didn't start using Python on databases till after DB API
> 2.0 came out (1999) so please pardon my ignorance, but isn't dbiDate
> something that was in API 1.0 but vanished in API 2.0 [e.g. its
> mentioned only briefly in the history section of the current mxODBC
> docs]?
>
> If that's what you are still using:
> (a) I can't imagine how printing a dbiDate object would give such a
> garbled result -- try:
>
> print type(obj)
> print repr(obj)
> for both a "bad" obj and a "good" obj.
>
> (b) The API 1.0 docs give a clue:
> """
> dbiDate(value)
>
> This function constructs a 'dbiDate' instance that holds a
> date value. The value should be specified as an integer
> number of seconds since the "epoch" (e.g. time.time()).
> """
> and googling brought up a few hits mentioning that not handling dates
> earlier that the "epoch" (1970-01-01T00:00:00) was a limitation.
>
> So: if you are calling dbiDate yourself, you can inspect its input
> argument; presumably a date in the year 112 will show up as negative.
>
>
>
>
>
> > Traceback (most recent call last):
> > File "\\someServer\Development\collectiveFleet.py", line 68, in -
> > toplevel-
> > mycursor.execute(sql)
> > TypeError: argument 1 must be string without null bytes, not str

>
> > When I print the timestamp variable, I get this output:

>
> > (I31
> > (S'OK'
> > p1
> > Nttp2
> > .

>
> > If I look in the MS Access database, I see the timestamp as "5/6/112".
> > Obviously some user didn't enter the correct date and the programmer
> > before me didn't give Access strict enough rules to block bad dates.
> > How do I test for a malformed date object so I can avoid this?
> > There
> > are thousands of records to transfer.

>
> > I am using the odbc module for connection purposes with Python 2.4 on
> > Windows XP SP2.

>
> If this is the odbc module that comes in the win32all package:
> 1. There are much better options available on Windows e.g. mxODBC.
> 2. Doesn't document dbiDate objects AFAICT.
>
> If your SELECT from the Access db is returning you "seconds since the
> epoch" values, proceed as I suggested earlier.
>
> If it is returning you dbiDate objects directly, find out if the
> dbiDate obj has any useful attributes or methods e.g.
>
> obj.date_as_tuple() -> (2007, 4, 7, ...)
> or
> obj.year -> 2007
> obj.month -> 4
> etc
>
> How to find out: insert code like
> print dir(obj)
> in your script and inspect the output for likely attribute/method
> names.
>
> And if that doesn't help, abandon the odbc module and use e.g. mxODBC
> or Python adodb.
>
> Hope some of this helps,
> John


I did find a workaround that I implemented right before it was
quitting time, but I want to look into both of your guy's answers. I
have used the adodb module and I can't recall why I switched to the
odbc one. I think one of my co-workers said that the adodb wouldn't
work with mySQL on Linux or something.

The quick-fix I used included using the datetime module and the time
module with the strftime() method. The type that was returned said it
was a dbiDate object (which is what I think I get in one of my other
programs that does use the adodb module!)

John - when I tried printing a dir() on the returned object, I got and
empty list.

Thanks for the suggestions. I won't get to try them until Monday.

Mike

 
Reply With Quote
 
kyosohma@gmail.com
Guest
Posts: n/a
 
      04-09-2007
On Apr 6, 10:15 pm, (E-Mail Removed) wrote:
> On Apr 6, 6:20 pm, "John Machin" <(E-Mail Removed)> wrote:
>
>
>
> > On Apr 7, 6:48 am, (E-Mail Removed) wrote:

>
> > > Hi,

>
> > > I am populating a mySQL database with data from the MS Access
> > > database. I have successfully figured out how to extract the data from
> > > Access, and I can insert the data successfully into mySQL with Python.
> > > My problem is that I keep hitting screwy records with what appears to
> > > be a malformed dbiDate object when I insert certain records. I get the
> > > following traceback:

>
> > Ummm ... I didn't start using Python on databases till after DB API
> > 2.0 came out (1999) so please pardon my ignorance, but isn't dbiDate
> > something that was in API 1.0 but vanished in API 2.0 [e.g. its
> > mentioned only briefly in the history section of the current mxODBC
> > docs]?

>
> > If that's what you are still using:
> > (a) I can't imagine how printing a dbiDate object would give such a
> > garbled result -- try:

>
> > print type(obj)
> > print repr(obj)
> > for both a "bad" obj and a "good" obj.

>
> > (b) The API 1.0 docs give a clue:
> > """
> > dbiDate(value)

>
> > This function constructs a 'dbiDate' instance that holds a
> > date value. The value should be specified as an integer
> > number of seconds since the "epoch" (e.g. time.time()).
> > """
> > and googling brought up a few hits mentioning that not handling dates
> > earlier that the "epoch" (1970-01-01T00:00:00) was a limitation.

>
> > So: if you are calling dbiDate yourself, you can inspect its input
> > argument; presumably a date in the year 112 will show up as negative.

>
> > > Traceback (most recent call last):
> > > File "\\someServer\Development\collectiveFleet.py", line 68, in -
> > > toplevel-
> > > mycursor.execute(sql)
> > > TypeError: argument 1 must be string without null bytes, not str

>
> > > When I print the timestamp variable, I get this output:

>
> > > (I31
> > > (S'OK'
> > > p1
> > > Nttp2
> > > .

>
> > > If I look in the MS Access database, I see the timestamp as "5/6/112".
> > > Obviously some user didn't enter the correct date and the programmer
> > > before me didn't give Access strict enough rules to block bad dates.
> > > How do I test for a malformed date object so I can avoid this?
> > > There
> > > are thousands of records to transfer.

>
> > > I am using the odbc module for connection purposes with Python 2.4 on
> > > Windows XP SP2.

>
> > If this is the odbc module that comes in the win32all package:
> > 1. There are much better options available on Windows e.g. mxODBC.
> > 2. Doesn't document dbiDate objects AFAICT.

>
> > If your SELECT from the Access db is returning you "seconds since the
> > epoch" values, proceed as I suggested earlier.

>
> > If it is returning you dbiDate objects directly, find out if the
> > dbiDate obj has any useful attributes or methods e.g.

>
> > obj.date_as_tuple() -> (2007, 4, 7, ...)
> > or
> > obj.year -> 2007
> > obj.month -> 4
> > etc

>
> > How to find out: insert code like
> > print dir(obj)
> > in your script and inspect the output for likely attribute/method
> > names.

>
> > And if that doesn't help, abandon the odbc module and use e.g. mxODBC
> > or Python adodb.

>
> > Hope some of this helps,
> > John

>
> I did find a workaround that I implemented right before it was
> quitting time, but I want to look into both of your guy's answers. I
> have used the adodb module and I can't recall why I switched to the
> odbc one. I think one of my co-workers said that the adodb wouldn't
> work with mySQL on Linux or something.
>
> The quick-fix I used included using the datetime module and the time
> module with the strftime() method. The type that was returned said it
> was a dbiDate object (which is what I think I get in one of my other
> programs that does use the adodb module!)
>
> John - when I tried printing a dir() on the returned object, I got and
> empty list.
>
> Thanks for the suggestions. I won't get to try them until Monday.
>
> Mike


I tried your suggestion:

print type(timestamp)
print repr(timestamp)

Here's my results:

# bad
<type 'DbiDate'>
<DbiDate object at 0x0099D5F0>

# good
<type 'datetime.datetime'>
datetime.datetime(2007, 4, 9, 0, 0)

When trying to use the "date_as_tuple" method on the object, I get the
following: AttributeError: date_as_tuple

Currently, I am using a "try" block to catch the error and just set it
to the current date. This does work, but I've discovered that there
are duplicates in the database somehow so I will need to find a way
around that as well. Thanks for the feedback.

Mike


 
Reply With Quote
 
Gabriel Genellina
Guest
Posts: n/a
 
      04-09-2007
En Mon, 09 Apr 2007 11:42:33 -0300, <(E-Mail Removed)> escribió:

> Here's my results:
>
> # bad
> <type 'DbiDate'>
> <DbiDate object at 0x0099D5F0>
>
> # good
> <type 'datetime.datetime'>
> datetime.datetime(2007, 4, 9, 0, 0)


You can convert a DbiDate object into a datetime object using:
dt = datetime.datetime.fromtimestamp(float(dbidate))
Only dates after 1970 are supported.


--
Gabriel Genellina

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Database Database Database scott93727@gmail.com Computer Information 0 09-27-2012 02:43 AM
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
Conversion in timestamp units in RTP pintos VOIP 0 10-21-2009 05:13 PM
Reg Date string conversion into timestamp function praba kar Python 3 05-17-2005 12:59 AM
doubt regarding Conversion of date into timestamp praba kar Python 1 04-08-2005 09:15 AM



Advertisments