Python: tuple indices must be integers, not str when selecting from mysql table


I have following method that I select all the ids from table and append them to a list and return that list. But when execute this code I end up getting tuple indicies must be integers... error. I have attached the error and the print out along with my method:

def questionIds(con):
    print 'getting all the question ids'
    cur = con.cursor()
    qIds = []
    getQuestionId = "SELECT question_id from questions_new"
        for row in cur.fetchall():
            print 'printing row'
            print row
    except Exception, e:
    return qIds

Printing what my method does:

Database version : 5.5.10 
getting all the question ids
printing row
Traceback (most recent call last):
  File "", line 76, in questionIds
TypeError: tuple indices must be integers, not str
9/7/2012 8:52:43 PM

Accepted Answer

The python standard mysql library returns tuples from cursor.execute. To get at the question_id field you'd use row[0], not row['question_id']. The fields come out in the same order that they appear in the select statement.

A decent way to extract multiple fields is something like

for row in cursor.execute("select question_id, foo, bar from questions"):
    question_id, foo, bar = row
9/7/2012 8:57:43 PM

There are multiple cursor types in the MySQLdb module. The default cursor returns the data in a tuple of tuples. When we use a dictionary cursor, the data is sent in a form of Python dictionaries. This way we can refer to the data by their column names. Source

# -*- coding: utf-8 -*-

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

with con:

    cur = con.cursor(mdb.cursors.DictCursor)
    cur.execute("SELECT * FROM Writers LIMIT 4")

    rows = cur.fetchall()

    for row in rows:
        print row["Id"], row["Name"]

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow