Wrong type being returned from MySQL?

[kumara]

I have just upgraded to MySql 1.2.0 for python 2.4 and am encountering the following glitch with the automatic type conversion. In the first two calls
the result is returned as a long integer (which is what I was expecting). In the last call it is being returned as a string. Hmmm.

>>> d.cur.execute("select Number from Application limit 1;")
1L
>>> d.cur.fetchone()[0]
10000014L #INTEGER
>>>
>>> d.cur.execute("select Number from Application where Number is not null limit 2;")
2L
>>> d.cur.fetchone()[0]
10000014L #INTEGER
>>>
>>> d.cur.execute("select ContractNumber from FactApplication where ContractNumber is not null limit 1;")
1L
>>> d.cur.fetchone()[0]
'10000014' #STRING!

I have a similar problem with a call that returns the average of a column of numbers.
self.cur.execute("select avg(Number) from Application;")
In the new version the result is being returned as a string, whereas previously it was being returned as a number.

Cheers :-)

[kumara]

Sorry,
The correct code should have been

>>> d.cur.execute("select Number from Application limit 1;")
1L
>>> d.cur.fetchone()[0]
10000014L #INTEGER
>>>
>>> d.cur.execute("select Number from Application where Number is not null limit 2;")
2L
>>> d.cur.fetchone()[0]
10000014L #INTEGER
>>>
>>> d.cur.execute("select Number from Application where Number is not null limit 1;")
1L
>>> d.cur.fetchone()[0]
'10000014' #STRING!

Duh!

[Andy Dustman]

You don't say what MySQL version you are using.

You shouldn't have the trailing semicolon; it's not needed, and you can't have multiple statements in a single execute() anyway. (Well, it's possible, but not normally.)

In your tests, also try printing d.cur.description. This is a sequence of tuples, one for each column. Item 1 will be the column type that the MySQL C API returns. In other words, what is d.cur.description[0][1]? The values are defined in MySQLdb.constants.FIELD_TYPE:

BLOB = 252
CHAR = 1
DATE = 10
DATETIME = 12
DECIMAL = 0
DOUBLE = 5
ENUM = 247
FLOAT = 4
GEOMETRY = 255
INT24 = 9
INTERVAL = 247
LONG = 3
LONGLONG = 8
LONG_BLOB = 251
MEDIUM_BLOB = 250
NEWDATE = 14
NULL = 6
SET = 248
SHORT = 2
STRING = 254
TIME = 11
TIMESTAMP = 7
TINY = 1
TINY_BLOB = 249
VAR_STRING = 253
YEAR = 13