Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.


#131 result set sometimes array, not

Andy Dustman
MySQLdb (285)

I have several queries that work fine under MySQLdb
0.9.2 with mysql 4.0.23 and python 2.2 that are causing
major headache with python 2.3 or 2.4 and MySQLdb
1.0.0 or 1.2.0 and mysql 4.1.5 under FreeBSD 5.3

Under the previous version, the query returns a set of
rows with a single string in each. Now, depending on the
column specified, and whether the query is ordered, the
set contains arrays with an extra string, 'c', as the first


rowsAffected = curs_ta.execute("select concat
(ga_name, '(Q', ca.logical, case when description is null
then '' else description end, ')') as region from
return_carrier_daily ca join g on ca.ocean =
g.ocean and ca.beam = g.beam and frame_date = '" +
datestamp + "' LEFT OUTER JOIN queue_description qd
ON ca.ocean = qd.ocean and ca.beam = qd.beam and
ca.logical = qd.logical and '" + datestamp + "' between
start_date and end_date order by region")

rows = curs_ta.fetchall()
print rows
((array('c', 'AOREGL(Q0)'),), (array('c', 'AORWGL
(Q0 Sky)'),), (array('c', 'AORWGL(Q3 Sat)'),), (array
('c', 'AORWSC(Q1 Sky)'),), (array('c', 'AORWSC(Q4
Sat)'),), (array('c', 'IORGL(Q0 Sat)'),), (array('c', 'IORGL
(Q2 Sky)'),), (array('c', 'PORGL(Q0)'),))

Remove the "order by region" and it returns the correct
set. You can also remove the 'description' field to fix it.
Neither of these are an option in my situation however.


  • Andy Dustman
    Andy Dustman

    Logged In: YES

    You should look up the array module:

    'c' is not the first element; it's the type of the array.

    You have some BLOB columns in your database. BLOBs are
    retuned as as character arrays (i.e. array('c',...)) because
    this is the recommend practice in PEP-249.

    You can turn array objects into strings using the
    .tostring() method.

    Additionally, if you don't like to use arrays, you can do this:

    from MySQLdb.constants import FIELD_TYPE
    del db.converter[FIELD_TYPE.BLOB]

    This will return stings instead. There are a couple of
    variations on how to achieve this; read the user's guide.

    You are putting literal values into your string directly.
    This is bad. Read PEP-249 and the User's Guide, particularly
    stuff about cursor.execute().

  • Logged In: NO

    There are no BLOBs in the data that is being changed from a
    string to an array, it is purely a string - which is what the
    mysql function "concat" returns. I am creating a single string
    column from multiple source columns, a common practice in
    summarizing results, and very common in pivot table queries.

    The return type of a column should not change based on
    whether a result set is ordered or not.

  • Andy Dustman
    Andy Dustman

    Logged In: YES

    I agree, the return type of a column should not change based
    on whether a result set is ordered or not. And MySQLdb does
    not do this; it is only using the column types that MySQL
    gives it. If this is happening, then it's a MySQL bug, and
    you should file a bug there.

    If you do, you can reference this bug, if you think it'll help.

  • Logged In: NO

    Thanks. I have installed the 0.9.2 version for now and it is
    working fine - presumably that version didn't check the
    column type as rigorously.

    I will try casting the column and ping the mysql gang.

  • Andy Dustman
    Andy Dustman

    Logged In: YES

    Using 0.9.2 should not make any difference. Are you still
    using MySQL-4.1.5 with MySQLdb-0.9.2? If so, you must be
    patching it for the mysql_shutdown() API change; otherwise
    you may be using a different (4.0) MySQL version.

    One thing you can do to test this is examine the cursor's
    description attribute after you execute the query. Read
    PEP-249 to see what the structure is. The column types are
    numeric, corresponding to values in
    MySQLdb.constants.FIELD_TYPES. See if 0.9.2 behaves
    differently from 1.2.0, and particularly if MySQL-4.0 and
    4.1 behave differently.