MySQLdb extracting to a list

[dave.dex]

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.

 

[John Machin]

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

 

[dave.dex]

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.