471,354 Members | 2,075 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,354 software developers and data experts.

MySQLdb extracting to a list

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
2 6160
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
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.

Similar topics

2 posts views Thread by Stephan Diehl | last post: by
5 posts views Thread by Chris Stromberger | last post: by
1 post views Thread by Peter Nikolaidis | last post: by
1 post views Thread by Michael | last post: by
21 posts views Thread by John Fabiani | last post: by
5 posts views Thread by Harold Trammel | last post: by
reply views Thread by Edwin.Madari | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.