Database Timestamp conversion error

Discussion in 'Python' started by kyosohma@gmail.com, Apr 6, 2007.

  1. Guest

    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\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.

    Thanks a lot!

    Mike
    , Apr 6, 2007
    #1
    1. Advertising

  2. Guest

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

    --
    Hope this helps,
    Steven
    , Apr 6, 2007
    #2
    1. Advertising

  3. John Machin Guest

    On Apr 7, 6:48 am, 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
    John Machin, Apr 7, 2007
    #3
  4. Guest

    On Apr 6, 6:20 pm, "John Machin" <> wrote:
    > On Apr 7, 6:48 am, 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
    , Apr 7, 2007
    #4
  5. Guest

    On Apr 6, 10:15 pm, wrote:
    > On Apr 6, 6:20 pm, "John Machin" <> wrote:
    >
    >
    >
    > > On Apr 7, 6:48 am, 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
    , Apr 9, 2007
    #5
  6. En Mon, 09 Apr 2007 11:42:33 -0300, <> escribió:

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


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


    --
    Gabriel Genellina
    Gabriel Genellina, Apr 9, 2007
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. praba kar
    Replies:
    1
    Views:
    393
    Max M
    Apr 8, 2005
  2. praba kar
    Replies:
    3
    Views:
    571
    George Sakkis
    May 17, 2005
  3. Replies:
    7
    Views:
    254
    Miquel van Smoorenburg
    Nov 9, 2013
  4. Igor Korot

    Proper conversion of timestamp

    Igor Korot, Mar 4, 2014, in forum: Python
    Replies:
    0
    Views:
    53
    Igor Korot
    Mar 4, 2014
  5. MRAB
    Replies:
    0
    Views:
    54
Loading...

Share This Page