Beginner Python user - need help iterating through a column in a MySQL db and store the values in a list

Question

The python code below connects to my database. What I'm trying to do is iterate through a column which contains integer values, and put all of those values into a list. I will need the values in the list because I want to normalise them between a range of 0 and 1.

import MySQLdb
import sys

db = MySQLdb.connect(host="****", user="****", passwd="****", db="****", port=3306)
cursor = db.cursor()
cursor.execute("SELECT columnName FROM TABLE5")

col1 = []
numrows = int(cursor.rowcount)
for x in range(0,numrows):
    row = cursor.fetchall()
    col1.append(row[x])
    print col1

cursor.close()
db.close()
sys.exit()

The above currently outputs the following:

[('67',)]
Traceback (most recent call last):
 File "miner.py", line 12, in <module>
  col1.append(row[x])
IndexError: tuple index out of range

If anyone could help that would be great!

I think you're iterating the rows, but trying to read the column with the rowindex. — Caramiriel
– Caramiriel, Commented Apr 2, 2014 at 19:20
Look at your code and the error you are given: You say for x in range(numrows) (which has to do with how many rows you have) then try to get row[x]. If you have 100,000 rows with 3 columns each, do you see why this will fail by the 4th row? — Two-Bit Alchemist
– Two-Bit Alchemist, Commented Apr 2, 2014 at 19:21

Joran Beasley · Accepted Answer · 2014-04-02 19:25:10Z

1

cursor.execute("SELECT columnName FROM TABLE5")
col_names = [row[0] for row in cursor.fetchall()]

is probably what you want cursor.fechall() will return something like

[("ColName1",),("Colname2",),...]

so we just iterate over that list and take the first item from each tuple

you could also do something fancy like

col_names = list(itertools.chain.from_iterable(cursor.fetchall()))

but really the simple list comprehesion should be plenty sufficient

answered Apr 2, 2014 at 19:25

Joran Beasley

114k13 gold badges168 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Harry V Over a year ago

Thank you Joran, this is what I needed!

Larry Lustig · Accepted Answer · 2014-04-02 19:32:24Z

I think you're confused about the use of cursor.fetchall(). I'm guessing you think it will return all the columns for a single row. But really it returns all the rows from the query you executed.

Therefore, you need to execute cursor.fetchall() only once, outside your loop.

Additionally, while your loop (or rather, something close to it) will work, it's unnecessarily complex and "un-Pythonic". That's because the results of fetchall() (a list of all the rows) can be directly iterated over without worrying about the count. It iteration will produce a single row object, which contains the columns you want. So:

col1 = []
all_the_rows = cursor.fetchall()
for row in all_the_rows:
    # We want the first-and-only column value in the row
    col1.append(row[0])

 # Wait until we have all the values to print them.
print col1

You can simplify this further (Joran has an example above), but it may be best to start with a regular for-loop construction and move on to the advanced concepts once you have this nailed down.

Collectives™ on Stack Overflow

Beginner Python user - need help iterating through a column in a MySQL db and store the values in a list

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related