Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > MySQLdb extracting to a list

Reply
Thread Tools

MySQLdb extracting to a list

 
 
dave.dex@googlemail.com
Guest
Posts: n/a
 
      12-13-2007
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.
 
Reply With Quote
 
 
 
 
John Machin
Guest
Posts: n/a
 
      12-13-2007
On Dec 13, 9:03 pm, (E-Mail Removed) 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', )

so:
>>> result = (('Tom', 6), ('Dick', 7), ('Harry', )
>>> [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

 
Reply With Quote
 
 
 
 
dave.dex@googlemail.com
Guest
Posts: n/a
 
      12-13-2007
On Dec 13, 10:40 am, John Machin <(E-Mail Removed)> wrote:
> On Dec 13, 9:03 pm, (E-Mail Removed) 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', )
>
> so:>>> result = (('Tom', 6), ('Dick', 7), ('Harry', )
> >>> [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.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extracting a List from a List of lists Damo Java 4 02-09-2007 01:02 AM
problem with appending to a list, possibly mysqldb related John Salerno Python 5 08-31-2006 07:06 PM
Efficiently Extracting Identical Values From A List/Array Adam Hartshorne C++ 7 02-21-2005 04:58 PM
MySQLDB - generating "...not in (1,2,3)" from Python list ? Richard Shea Python 4 02-29-2004 03:56 AM
RE: MySQLDB - generating "...not in (1,2,3)" from Python list ? Marc Boeren Python 2 02-23-2004 06:20 PM



Advertisments