Database Timestamp conversion error

[kyosohma at gmail.com]

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