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

Question

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"
 try:
 cur.execute(getQuestionId)
 for row in cur.fetchall():
 print 'printing row'
 print row
 qIds.append(str(row['question_id']))
 except Exception, e:
 traceback.print_exc()
 return qIds

Printing what my method does:

Database version : 5.5.10 
getting all the question ids
printing row
(u'20090225230048AAnhStI',)
Traceback (most recent call last):
 File "YahooAnswerScraper.py", line 76, in questionIds
 qIds.append(str(row['question_id'][0]))
TypeError: tuple indices must be integers, not str

Answer 1

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

• Landon Poch: If you want to be able to pull columns values out by column name you may want to try creating your cursor with MySQLdb.cursors.DictCursor. See stackoverflow.com/questions/10195139/…

Answer 2

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

#!/usr/bin/python
# -*- 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"]

Answer 3

I know the question is old, but I found another way to do it that I think it is better than the accepted solution. So I'll just leave it here in case anyone needs it.

When creating the cursor you can use

cur = connection.cursor(dictionary=True);

which will allow you to do exactly what you want without any additional modifications.

rows = cur.fetchall()
for row in rows:
 print "%s %s %s" % (row["Id"], row["Name"], row["Price"])

Answer 4

you can see here: enter link description here ,I think its your want

#!/usr/bin/python
# -*- coding: utf-8 -*-
import sqlite3 as lite
con = lite.connect('test.db') 
with con:
 con.row_factory = lite.Row # its key
 cur = con.cursor() 
 cur.execute("SELECT * FROM Cars")
 rows = cur.fetchall()
 for row in rows:
 print "%s %s %s" % (row["Id"], row["Name"], row["Price"])

Answer 5

To retrieve data from database use dictionary cursor

import psycopg2
import psycopg2.extras
con = psycopg2.connect(database="test", user="test", password="test", host="localhost", port="5432")
if con != None:
 print "Connection Established..!\n"
else:
 print "Database Connection Failed..!\n"
cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute("SELECT * FROM emp")
rows = cur.fetchall()
for row in rows:
 print "%s %s %s" % (row["id"],row["name"],row["address"])
print "\nRecords Display Successfully"
con.commit()
con.close()

Answer 6

Integer indices are not allowed. To get it working you can declare the DICT as specified below:

VarName = {}

Hope this works for you.

Answer 7

row is a tuple. When you do row['question_id'], you are trying to access a tuple using a string index which gives you an error.

• add-semi-colons: so i tried the following row['question_id'][0] but it still gave me the same answer
• Lanaru: row is a tuple. Tuples look like this: t = ('a','b','c'). You access them using an integer index, ie t[0] = 'a', t[1] = 'b', t[2] = 'c'. You can not access them using a string as an index; you can only do that with a dict.