MySQLdb extracting to a list

Discussion in 'Python' started by dave.dex@googlemail.com, Dec 13, 2007.

  1. Guest

    Hi all,

    I've been searching the docs like mad and I'm a little new to python
    so apologies if this is a basic question.

    I would like to extract the results of the following query into a list
    - SELECT columnname FROM tablename. I use the following code.

    # Create a connection object and create a cursor
    db = MySQLdb.Connect(<my-db-info)
    cursor = db.cursor()
    # Make SQL string and execute it
    sql = "SELECT columnname FROM tablename"
    cursor.execute(sql)
    # Fetch all results from the cursor into a sequence and close the
    connection
    results = cursor.fetchall()
    db.close()
    print results

    The output from the above gives the following:

    (('string1',), ('string2',), ('string3',))

    When I'm expecting
    ('string1', 'string2', 'string3')

    I could pass this through some string manipulation but I'd guess I'm
    doing something wrong. Please could someone point me in the right
    direction.

    Thanks in advance.
    , Dec 13, 2007
    #1
    1. Advertising

  2. John Machin Guest

    On Dec 13, 9:03 pm, wrote:
    > Hi all,
    >
    > I've been searching the docs like mad and I'm a little new to python
    > so apologies if this is a basic question.
    >
    > I would like to extract the results of the following query into a list
    > - SELECT columnname FROM tablename. I use the following code.
    >
    > # Create a connection object and create a cursor
    > db = MySQLdb.Connect(<my-db-info)
    > cursor = db.cursor()
    > # Make SQL string and execute it
    > sql = "SELECT columnname FROM tablename"
    > cursor.execute(sql)
    > # Fetch all results from the cursor into a sequence and close the
    > connection
    > results = cursor.fetchall()
    > db.close()
    > print results
    >
    > The output from the above gives the following:
    >
    > (('string1',), ('string2',), ('string3',))
    >
    > When I'm expecting
    > ('string1', 'string2', 'string3')
    >
    > I could pass this through some string manipulation but I'd guess I'm
    > doing something wrong. Please could someone point me in the right
    > direction.
    >


    Your SQL query has returned 3 rows. Each row contains only 1 column.

    Each row is returned as a tuple of 1 element. The whole result is a
    tuple of 3 rows. You don't need string manipulation, you need tuple
    manipulation.

    Better example:
    select name, hat_size from friends;
    results in:
    (('Tom', 6), ('Dick', 7), ('Harry', 8))

    so:
    >>> result = (('Tom', 6), ('Dick', 7), ('Harry', 8))
    >>> [row[0] for row in result]

    ['Tom', 'Dick', 'Harry']
    >>> for n, h in result:

    .... print 'Name: %s; hat size: %d' % (n, h)
    ....
    Name: Tom; hat size: 6
    Name: Dick; hat size: 7
    Name: Harry; hat size: 8
    >>> result[2][1]

    8
    >>>


    HTH,
    John
    John Machin, Dec 13, 2007
    #2
    1. Advertising

  3. Guest

    On Dec 13, 10:40 am, John Machin <> wrote:
    > On Dec 13, 9:03 pm, wrote:
    >
    >
    >
    > > Hi all,

    >
    > > I've been searching the docs like mad and I'm a little new to python
    > > so apologies if this is a basic question.

    >
    > > I would like to extract the results of the following query into a list
    > > - SELECT columnname FROM tablename. I use the following code.

    >
    > > # Create a connection object and create a cursor
    > > db = MySQLdb.Connect(<my-db-info)
    > > cursor = db.cursor()
    > > # Make SQL string and execute it
    > > sql = "SELECT columnname FROM tablename"
    > > cursor.execute(sql)
    > > # Fetch all results from the cursor into a sequence and close the
    > > connection
    > > results = cursor.fetchall()
    > > db.close()
    > > print results

    >
    > > The output from the above gives the following:

    >
    > > (('string1',), ('string2',), ('string3',))

    >
    > > When I'm expecting
    > > ('string1', 'string2', 'string3')

    >
    > > I could pass this through some string manipulation but I'd guess I'm
    > > doing something wrong. Please could someone point me in the right
    > > direction.

    >
    > Your SQL query has returned 3 rows. Each row contains only 1 column.
    >
    > Each row is returned as a tuple of 1 element. The whole result is a
    > tuple of 3 rows. You don't need string manipulation, you need tuple
    > manipulation.
    >
    > Better example:
    > select name, hat_size from friends;
    > results in:
    > (('Tom', 6), ('Dick', 7), ('Harry', 8))
    >
    > so:>>> result = (('Tom', 6), ('Dick', 7), ('Harry', 8))
    > >>> [row[0] for row in result]

    >
    > ['Tom', 'Dick', 'Harry']>>> for n, h in result:
    >
    > ... print 'Name: %s; hat size: %d' % (n, h)
    > ...
    > Name: Tom; hat size: 6
    > Name: Dick; hat size: 7
    > Name: Harry; hat size: 8
    >
    > >>> result[2][1]

    > 8
    >
    > HTH,
    > John


    Many thanks John,

    Really well explained and I understand what to do now. It's much
    appreciated.

    Thanks again.
    , Dec 13, 2007
    #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. Adam Hartshorne
    Replies:
    7
    Views:
    421
    Ivan Vecerina
    Feb 21, 2005
  2. Richard Shea
    Replies:
    4
    Views:
    420
  3. Marc Boeren
    Replies:
    2
    Views:
    261
    Steve Zatz
    Feb 23, 2004
  4. John Salerno
    Replies:
    5
    Views:
    294
    John Salerno
    Aug 31, 2006
  5. Damo
    Replies:
    4
    Views:
    504
Loading...

Share This Page