On Apr 6, 10:15 pm, [EMAIL PROTECTED] wrote: > On Apr 6, 6:20 pm, "John Machin" <[EMAIL PROTECTED]> wrote: > > > > > On Apr 7, 6:48 am, [EMAIL PROTECTED] 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 -- http://mail.python.org/mailman/listinfo/python-list
