Database Timestamp conversion error

[kyosohma]

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

 

[attn.steven.kuo]

On Apr 6, 1:48 pm, (e-mail address 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

 

[John Machin]

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

 

[kyosohma]

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:

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

 

[kyosohma]

On Apr 7, 6:48 am, (e-mail address removed) wrote:

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.

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

 

[Gabriel Genellina]

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.