By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,119 Members | 2,086 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,119 IT Pros & Developers. It's quick & easy.

MySQLdb extracting to a list

P: n/a
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 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Dec 13, 9:03 pm, dave....@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

Dec 13 '07 #2

P: n/a
On Dec 13, 10:40 am, John Machin <sjmac...@lexicon.netwrote:
On Dec 13, 9:03 pm, dave....@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.
Dec 13 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.