#111 Return rowcount instead of select-union

MySQLdb-1.1
closed
Andy Dustman
MySQLdb (285)
5
2012-09-19
2004-10-20
Anonymous
No

Hello I'm using debian and mysql-4.1.4

After a query as this one :

( SELECT 'M' AS T,COUNT(*) AS N,MACROCAT_COD AS A,
MACROCAT_DESC AS B FROM ARTICOLICAT LEFT JOIN MACROCAT
ON MACROCAT_COD = ARTICOLICAT_MACROCAT AND
ARTICOLICAT.COD_SOC = MACROCAT.COD_SOC WHERE
ARTICOLICAT.COD_SOC = '001' GROUP BY
ARTICOLICAT_MACROCAT ) UNION ( SELECT 'C' AS T,0 AS
N,ARTICOLICAT_COD AS A, ARTICOLICAT_DESC AS B FROM
ARTICOLICAT WHERE COD_SOC = '001' ) ORDER BY A

that is, a union of two select, I get the number of rows
instead of the recordset.........

Do I miss something ?

Discussion

  • Andy Dustman
    Andy Dustman
    2004-10-20

    Logged In: YES
    user_id=71372

    You'll need to demostrate that it works correctly in the
    MySQL command line client. Additionally, I'll need your
    MySQLdb version and Python version. Also check for a newer
    version of MySQL.

     
  • Logged In: NO

    mysql version : 4.1.4-gamma
    python-mysqldb : 1.1.6

    here is a demo :

    mysql> ( SELECT 'M' AS T,COUNT(*) AS N,MACROCAT_COD AS A,
    -> MACROCAT_DESC AS B FROM ARTICOLICAT LEFT JOIN MACROCAT
    -> ON MACROCAT_COD = ARTICOLICAT_MACROCAT AND
    -> ARTICOLICAT.COD_SOC = MACROCAT.COD_SOC WHERE
    -> ARTICOLICAT.COD_SOC = '001' GROUP BY
    -> ARTICOLICAT_MACROCAT ) UNION ( SELECT 'C' AS T,0 AS
    -> N,ARTICOLICAT_COD AS A, ARTICOLICAT_DESC AS B FROM
    -> ARTICOLICAT WHERE COD_SOC = '001' ) ORDER BY A;
    +---+---+------+---------------------------+
    | T | N | A | B |
    +---+---+------+---------------------------+
    | M | 7 | NULL | NULL |
    | M | 1 | NULL | NULL |
    | C | 0 | ABB | Abbigliamento |
    | C | 0 | ACC | Accessori |
    | C | 0 | ALI | Alimentari |
    | C | 0 | OGG | Oggetti prestigiosi |
    | C | 0 | PIO | In caso di pioggia |
    | C | 0 | TEM | Tempo libero |
    | C | 0 | VIA | Per i viaggi |
    | C | 0 | VID | Video e DVD e CD musicali |
    +---+---+------+---------------------------+
    10 rows in set (0.00 sec)

    mysql>

    python replies :

    TypeError: iteration over non-sequence
    args = ('iteration over non-sequence',)

    when I try to do a "for row in rSet:"

    that is because the recordset instance returns the number of
    rows (10) instead of the recordset

    thank you

     
  • Andy Dustman
    Andy Dustman
    2004-10-22

    Logged In: YES
    user_id=71372

    You're going to have to include the code so I know what
    you're talking about. Is rSet something that you got from
    cursor.fetchall()? Or is it something else?

    Are your client libraries and server the same version
    (4.1.4-gamma)?

     
  • Lorenzo Viola
    Lorenzo Viola
    2004-10-25

    Logged In: YES
    user_id=780168

    the code is quite long, I should cut it or send it to you in
    some way (along with the tables and data, I guess)

    Anyway it is of this type :

    db = MySQLdb.connect(db=DB, host=DBHOST,user=DBUSER,
    passwd=DBPASS)
    cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
    sSQL = str( " ( SELECT 'M' AS T,COUNT(*) AS N,MACROCAT_COD
    AS A, MACROCAT_DESC AS B FROM ARTICOLICAT " +
    " LEFT JOIN MACROCAT ON MACROCAT_COD =
    ARTICOLICAT_MACROCAT AND ARTICOLICAT.COD_SOC =
    MACROCAT.COD_SOC "+
    " WHERE ARTICOLICAT.COD_SOC = '001' GROUP BY
    ARTICOLICAT_MACROCAT ) UNION " +
    " ( SELECT 'C' AS T,0 AS N,ARTICOLICAT_COD AS A,
    ARTICOLICAT_DESC AS B FROM ARTICOLICAT " +
    " WHERE COD_SOC = '001' ) ORDER BY A ")

    cur.execute(sSQL)
    rSet = cur.fetchall()

    the problem happens also without setting the cursorclass

    If I try to connect using the plain mysql command (example
    4.0.21) from shell, the query executes and returns the right
    data

    How do I check the client library version I'm using from
    python ?

    Can I hard link from code the library to use, just to be sure ?

    Probably the library python uses is the one in path (and so
    the 4.0.21 of debian) but I don't feel it as the problem (as
    it works from the shell...)

    If there is no "shared object" library, could I use the .a
    format ?

    best regards

     
  • Andy Dustman
    Andy Dustman
    2004-10-26

    Logged In: YES
    user_id=71372

    MySQLdb.get_client_info() will tell you want client version
    you are linked against.

    There are also some connection object methods you can call:

    | get_host_info(...)
    | Returns a string that represents the MySQL client
    library
    | version. Non-standard.
    |
    | get_proto_info(...)
    | Returns an unsigned integer representing the
    protocol version
    | used by the current connection. Non-standard.
    |
    | get_server_info(...)
    | Returns a string that represents the server version
    number.
    | Non-standard.

    Have you tried doing a simpler query to test your MySQLdb? I
    suspect that MySQL-4.0 libraries will have trouble talking
    to a 4.1 server. I am pretty sure 4.1 libraries will have
    trouble with a 4.0 server.

    Also note that MySQL-4.1.7 (final) is out today.

    Tip: Format your query using triple quotes:

    sSQL = """ ( SELECT 'M' AS T,COUNT(*) AS N,MACROCAT_COD AS A,
    MACROCAT_DESC AS B FROM ARTICOLICAT LEFT JOIN MACROCAT
    ON MACROCAT_COD = ARTICOLICAT_MACROCAT AND
    ARTICOLICAT.COD_SOC = MACROCAT.COD_SOC WHERE
    ARTICOLICAT.COD_SOC = '001' GROUP BY
    ARTICOLICAT_MACROCAT ) UNION ( SELECT 'C' AS T,0 AS
    N,ARTICOLICAT_COD AS A, ARTICOLICAT_DESC AS B FROM
    ARTICOLICAT WHERE COD_SOC = '001' ) ORDER BY A """

     
  • Lorenzo Viola
    Lorenzo Viola
    2004-10-26

    Logged In: YES
    user_id=780168

    I just can't understand it......

    It replies : 3.23.56 (!!!)

    I thought that it would at least use 4.0.21, which is
    installed as client as a debian package !

    By now, the solution for me is using temporary heap tables...

    Anyway , many thanks for the triple quote tip =)

    How could I tell to MySQLdb to use the library I want ?
    I will also check the last mysql version...

    best regards !

     
  • Andy Dustman
    Andy Dustman
    2004-10-27

    Logged In: YES
    user_id=71372

    I will guess that you are using the Debian package. You can
    only change the client library at compile time. I suspect
    you will have to build MySQLdb from the sources. It's
    possible you might have to change the library search path in
    setup.py.

     
  • Lorenzo Viola
    Lorenzo Viola
    2004-10-28

    Logged In: YES
    user_id=780168

    ok ,clear =) debian is very slow in updates...

    thank you for your advices (and patience !)

    By now I've simply used temporary heap tables to quickly
    resolve,
    I'll check out mysqldb rebuild ASAP !

    best regards!