MySQLdb - weird formatting inconsistency

[Anthra Norell]

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