MySQLdb extracting to a list

[dave.dex at googlemail.com]

On Dec 13, 10:40 am, John Machin <sjmac... at lexicon.net> wrote:
> On Dec 13, 9:03 pm, dave.... at googlemail.com 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.