Database Timestamp conversion error

[kyosohma at gmail.com]

On Apr 6, 10:15 pm, kyoso... at gmail.com wrote:
> On Apr 6, 6:20 pm, "John Machin" <sjmac... at lexicon.net> wrote:
>
>
>
> > 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
>
> 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