MySQLdb and column names

Discussion in 'Python' started by beef, Jan 30, 2008.

  1. beef

    beef Guest

    Hello all,

    I am using MySQLdb 1.2.2 and have a question about the construction of
    the dictionary keys of a result set.

    Here is an example query, from which you may intuit some of the
    structure of the tables:

    SELECT
    shots.*,
    users.*,
    sequences.*,
    jobs.*
    FROM
    shots
    LEFT JOIN
    users ON users.id=shots.user_id
    INNER JOIN
    sequences ON sequences.id=shots.sequence_id
    INNER JOIN
    jobs AS j ON j.id=sequences.job_id
    WHERE
    shots.id=%s

    1. The 'users' table has a column named 'id', as do all the other
    tables.
    2. I define my cursor as a 'DictCursor' so that my results are
    dictionaries
    3. The 'shots' id has the key name of 'id', while all the -other-
    'id's have key names of the form: "<TABLE>.id"

    I would prefer to have the key names consistent, so that event the
    "shots" fields have key names of the form "<TABLE>.id"

    Is this possible?

    Thanks in advance!

    --
    Wellington
     
    beef, Jan 30, 2008
    #1
    1. Advertising

  2. beef

    beef Guest

    change:
    I would prefer to have the key names consistent, so that event the

    to:
    I would prefer to have the key names consistent, so that *even* the
     
    beef, Jan 30, 2008
    #2
    1. Advertising

  3. On Wed, 30 Jan 2008 09:04:26 -0800 (PST), beef <>
    declaimed the following in comp.lang.python:


    > SELECT
    > shots.*,
    > users.*,
    > sequences.*,
    > jobs.*
    > FROM
    > shots
    > LEFT JOIN
    > users ON users.id=shots.user_id
    > INNER JOIN
    > sequences ON sequences.id=shots.sequence_id
    > INNER JOIN
    > jobs AS j ON j.id=sequences.job_id
    > WHERE
    > shots.id=%s
    >
    > 3. The 'shots' id has the key name of 'id', while all the -other-
    > 'id's have key names of the form: "<TABLE>.id"
    >

    The <TABLE> prefix is probably being used when the query processor
    discovers that there are duplicate field names. IE, it is just
    processing the table descriptions from left to right, and when a
    subsequent table has the same field name as one that has already been
    used, it prefaces the second (third, ...) with the source table.

    > I would prefer to have the key names consistent, so that event the
    > "shots" fields have key names of the form "<TABLE>.id"
    >

    Suggest, first step, do NOT use "table.*" notation, but rather
    explicitly list the fields you want from each table, in the order you
    want them. That, alone, might add the table names to all fields, though
    I suspect not -- so use an "as xxx" renaming of the fields you need to
    differentiate.

    ... shots.id as "shots.id", other.fields, other.tables...
    --
    Wulfraed Dennis Lee Bieber KD6MOG

    HTTP://wlfraed.home.netcom.com/
    (Bestiaria Support Staff: )
    HTTP://www.bestiaria.com/
     
    Dennis Lee Bieber, Jan 30, 2008
    #3
    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. Paddy McCarthy
    Replies:
    3
    Views:
    719
    Anthony J Bybell
    Sep 24, 2004
  2. Bob
    Replies:
    1
    Views:
    387
    Lucas Tam
    Jul 30, 2004
  3. Lewis G. Pringle, Jr.
    Replies:
    0
    Views:
    602
    Lewis G. Pringle, Jr.
    Sep 30, 2003
  4. fBechmann
    Replies:
    0
    Views:
    403
    fBechmann
    Jun 10, 2004
  5. wanwan
    Replies:
    3
    Views:
    433
    Alex Martelli
    Oct 14, 2005
Loading...

Share This Page