MySQLdb - weird formatting inconsistency

Discussion in 'Python' started by Anthra Norell, Apr 24, 2010.

  1. Hi all,

    Can anyone explain this? Three commands with three different cutoff
    dates (12/30, 12/31 and 1/1) produce a formatting inconsistency. Examine
    the third field. The first and last run represents it correctly. The
    second run strips it. The field happens to be a record ID and getting it
    stripped in an unpredictable manner obviously makes it useless as an ID.

    >>> finance.execute ('select * from j where Date between "1987-01-01"

    and "1987-12-30";')
    761L
    >>> for item in finance: print item


    (9737, '', ' 2278', datetime.date(1987, 1, 1), 5000.0, 'IIfVNg',
    'IIfAC', '', '', 'Schweigegeld')
    (9738, '', ' 2279', datetime.date(1987, 1, 5), 28.5, 'IIfVZc', 'IIfAC',
    '', '', '1 Schachtel 9mm')
    (9739, '', ' 2280', datetime.date(1987, 1, 5), 73.85, 'IIfVZm', 'IIfAC',
    '', '', 'Gladiolen')
    .. . .

    >>> finance.execute ('select * from j where Date between "1987-01-01"

    and "1987-12-31";')
    792L
    >>> for item in finance: print item


    (9737, '', '2278', datetime.date(1987, 1, 1), 5000.0, 'IIfVNg', 'IIfAC',
    '', '', 'Schweigegeld')
    (9738, '', '2279', datetime.date(1987, 1, 5), 28.5, 'IIfVZc', 'IIfAC',
    '', '', '1 Schachtel 9mm')
    (9739, '', '2280', datetime.date(1987, 1, 5), 73.85, 'IIfVZm', 'IIfAC',
    '', '', 'Gladiolen')
    .. . .

    >>> finance.execute ('select * from j where Date between "1987-01-01"

    and "1988-01-01";')
    796L
    >>> for item in finance: print item


    (9737, '', ' 2278', datetime.date(1987, 1, 1), 5000.0, 'IIfVNg',
    'IIfAC', '', '', 'Schweigegeld')
    (9738, '', ' 2279', datetime.date(1987, 1, 5), 28.5, 'IIfVZc', 'IIfAC',
    '', '', '1 Schachtel 9mm')
    (9739, '', ' 2280', datetime.date(1987, 1, 5), 73.85, 'IIfVZm', 'IIfAC',
    '', '', 'Gladiolen')
    .. . .
    Curiously, the second command with a limit works correctly, including
    'limit 792' which is equal to the number of selected records.

    >>> finance.execute ('select * from j where Date between "1987-01-01"

    and "1987-12-31" limit 792;')
    792L
    >>> for item in finance: print item


    (9737, '', ' 2278', datetime.date(1987, 1, 1), 5000.0, 'IIfVNg',
    'IIfAC', '', '', 'Schweigegeld')
    (9738, '', ' 2279', datetime.date(1987, 1, 5), 28.5, 'IIfVZc', 'IIfAC',
    '', '', '1 Schachtel 9mm')
    (9739, '', ' 2280', datetime.date(1987, 1, 5), 73.85, 'IIfVZm', 'IIfAC',
    '', '', 'Gladiolen')
    .. . .

    A variation of the problematic command showing only the IDs between
    start and end marks works okay too:

    >>> finance.execute ('select concat("|", Item, "|") from j where Date

    between "1987-01-01" and "1987-12-31";')
    792L
    >>> for item in finance: print item


    ('| 2278|',)
    ('| 2279|',)
    ('| 2280|',)
    .. . .


    After playing around with various date combinations the impression
    emerges that only 1/1/87-12/31/87 misbehaves. All other combinations I
    tried did okay.
    All runs were done in an IDLE window, one after the other, with the
    same cursor object.
    Ubuntu: 9.10. Python: 2.6. MySQL: 5.1.45.
    The record ID is declared as char (8) and a unique key. I don't think
    this matters, though, since run from the mysql command line everything
    works fine. It does look like a MySQLdb problem and a pretty weird one
    at that. Suggestions?

    Thanks

    Frederic
    Anthra Norell, Apr 24, 2010
    #1
    1. Advertising

  2. Anthra Norell

    John Nagle Guest

    Anthra Norell wrote:
    > Hi all,
    >
    > Can anyone explain this? Three commands with three different cutoff
    > dates (12/30, 12/31 and 1/1) produce a formatting inconsistency. Examine
    > the third field. The first and last run represents it correctly. The
    > second run strips it. The field happens to be a record ID and getting it
    > stripped in an unpredictable manner obviously makes it useless as an ID.
    >
    > >>> finance.execute ('select * from j where Date between "1987-01-01"

    > and "1987-12-30";')
    > 761L
    > >>> for item in finance: print item

    >
    > (9737, '', ' 2278', datetime.date(1987, 1, 1), 5000.0, 'IIfVNg', 'IIfAC', '', '', 'Schweigegeld')
    > (9738, '', ' 2279', datetime.date(1987, 1, 5), 28.5, 'IIfVZc', 'IIfAC', '', '', '1 Schachtel 9mm')
    > (9739, '', ' 2280', datetime.date(1987, 1, 5), 73.85, 'IIfVZm', 'IIfAC', '', '', 'Gladiolen')
    > . . .
    >
    > >>> finance.execute ('select * from j where Date between "1987-01-01"

    > and "1987-12-31";')
    > 792L
    > >>> for item in finance: print item

    > (9737, '', '2278', datetime.date(1987, 1, 1), 5000.0, 'IIfVNg', 'IIfAC', '', '', 'Schweigegeld')
    > (9738, '', '2279', datetime.date(1987, 1, 5), 28.5, 'IIfVZc', 'IIfAC', '',

    '', '1 Schachtel 9mm')
    > (9739, '', '2280', datetime.date(1987, 1, 5), 73.85, 'IIfVZm', 'IIfAC', '', '', 'Gladiolen')
    > . . .


    Those are the first and second runs. Where, exactly, do they differ?

    John Nagle
    John Nagle, Apr 24, 2010
    #2
    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. Chris Withers
    Replies:
    0
    Views:
    275
    Chris Withers
    Apr 3, 2004
  2. Replies:
    2
    Views:
    4,694
  3. John Nagle
    Replies:
    20
    Views:
    650
    M.-A. Lemburg
    Feb 7, 2008
  4. dorayme
    Replies:
    1
    Views:
    602
    richard
    Jan 21, 2011
  5. richard
    Replies:
    0
    Views:
    571
    richard
    Jan 21, 2011
Loading...

Share This Page