MySQLdb extracting to a list

[John Machin]

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