473,230 Members | 1,914 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,230 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 6267
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Stephan Diehl | last post by:
Since using Python2.3, MySQLdb doesn't work anymore correctly. Inserting data fails with "IndexError: list index out of range". Since the MySQLdb website states, that it runs up to version 2.2, I...
5
by: Chris Stromberger | last post by:
When issuing updates in mysql (in the console window), mysql will tell you if any rows matched and how many rows were updated (see below). I know how to get number of rows udpated using MySQLdb,...
1
by: Peter Nikolaidis | last post by:
Greetings, I am attempting to get MySQLdb 0.9.2 installed on Mac OS 10.2 with a Fink distribution of Python 2.2.2. I have seen only a few posts on the subject, some of them relate to...
1
by: Michael | last post by:
Is there a mailing list or anything like that for users of the MySQLdb module? Using version 0.9.2 with Python 2.3.2 and whenever I try to open a connection I get this error: File...
21
by: John Fabiani | last post by:
Hi, I'm a newbie and I'm attempting to learn howto create a select statement. When I use >>> string1='18 Tadlock Place' >>> cursor.execute("SELECT * FROM mytest where address = %s",string1) All...
5
by: Harold Trammel | last post by:
Hi everyone, Does anyone know the status of a version of MySQLdb that will work with Python 2.5? I will accept a workaround if you know one. Thanks in advance. Harold Trammel
2
rhitam30111985
by: rhitam30111985 | last post by:
hi all .. consider the following code: i basically need to build a table in the mysql database with two fields , country and office list... import MySQLdb import sys import os ...
8
by: geert | last post by:
Hi all, I have a mac mini running maocosx 10.5 leopard I want to deploy a django project on. My backend is MySQL, and I have it running as a 64- bit app. Of course, apache2 is also running as...
0
by: Edwin.Madari | last post by:
replace the name of table before calling *.execute. s.dbptr.execute(str % (e)) good luck. Edwin -----Original Message----- From: python-list-bounces+edwin.madari=verizonwireless.com@python.org...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

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.