Database Timestamp conversion error

[kyosohma@gmail.com]

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\D evelopment\coll ectiveFleet.py" , line 68, in -
toplevel-
mycursor.execut e(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@gmail.com]

Re: Database Timestamp conversion error

On Apr 6, 1:48 pm, kyoso...@gmail. com 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(d ate, "%m/%d/%Y")
print "Good date: " + date
print struct_tm
except ValueError:
print "Bad date: " + date

--
Hope this helps,
Steven

[John Machin]

Re: Database Timestamp conversion error

On Apr 7, 6:48 am, kyoso...@gmail. com 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\D evelopment\coll ectiveFleet.py" , line 68, in -
toplevel-
mycursor.execut e(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_tup le() -(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@gmail.com]

Re: Database Timestamp conversion error

On Apr 6, 6:20 pm, "John Machin" <sjmac...@lexic on.netwrote:

On Apr 7, 6:48 am, kyoso...@gmail. com 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\D evelopment\coll ectiveFleet.py" , line 68, in -
toplevel-
mycursor.execut e(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_tup le() -(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@gmail.com]

Re: Database Timestamp conversion error

On Apr 6, 10:15 pm, kyoso...@gmail. com wrote:

On Apr 6, 6:20 pm, "John Machin" <sjmac...@lexic on.netwrote:
>
>
>

On Apr 7, 6:48 am, kyoso...@gmail. com 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\D evelopment\coll ectiveFleet.py" , line 68, in -
toplevel-
mycursor.execut e(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_tup le() -(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.datet ime'>
datetime.dateti me(2007, 4, 9, 0, 0)

When trying to use the "date_as_tu ple" 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]

Re: Database Timestamp conversion error

En Mon, 09 Apr 2007 11:42:33 -0300, <kyosohma@gmail .comescribió:

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

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


--
Gabriel Genellina