Database Timestamp conversion error

[John Machin]

On Apr 7, 6:48 am, kyoso... at 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\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