TypeError: tuple indices must be integers, not str

Question

I am trying to pull data from a database and assign them to different lists. This specific error is giving me a lot of trouble "TypeError: tuple indices must be integers, not str" I tried converting it to float and etc, but to no success.

The code goes as below

conn=MySQLdb.connect(*details*)
cursor=conn.cursor()
ocs={}
oltv={}
query="select pool_number, average_credit_score as waocs, average_original_ltv as waoltv from *tablename* where as_of_date= *date*"
cursor.execute(query)
result=cursor.fetchall()

for row in result:
    print(row)
    ocs[row["pool_number"]]=int(row["waocs"])
    oltv[row["pool_number"]]=int(row["waoltv"])

Sample output of print statement is as follows :

('MA3146', 711L, 81L)
('MA3147', 679L, 83L)
('MA3148', 668L, 86L)

And this is the exact error I am getting:

ocs[row["pool_number"]]=int(row["waocs"])
TypeError: tuple indices must be integers or slices, not str

How do I solve this error?

Shrubbery

Answer 1

Like the error says, row is a tuple, so you can't do row["pool_number"]. You need to use the index: row[0].

Cicily

Well, today Python 3 says 'tuple indices must be integers or slices, not str'. Slice notation like [start,stop,step] now is onboard. For more info look this answer https://mcmap.net/q/14759/-how-slicing-in-python-works – Lodgings

Answer 2

I think you should do

for index, row in result: 

If you wanna access by name.

Dian

Answer 3

I know it is not specific to this question, but for anyone coming in from a Google search: this error is also caused by a comma behind an object that creates a tuple rather than a dictionary

>>>dict = {}
>>>tuple = {},

Tuple

>>>tuple_ = {'key' : 'value'},
>>>type(tuple_)
<class 'tuple'>

Dictionary

>>>dict_ = {'key' : 'value'} 
>>>type(dict_)        
<class 'dict'>

Spectacle

Answer 4

TL;DR: add the parameter cursorclass=MySQLdb.cursors.DictCursor at the end of your MySQLdb.connect.

---

I had a working code and the DB moved, I had to change the host/user/pass. After this change, my code stopped working and I started getting this error. Upon closer inspection, I copy-pasted the connection string on a place that had an extra directive. The old code read like:

 conn = MySQLdb.connect(host="oldhost",
                        user="olduser",
                        passwd="oldpass",
                        db="olddb", 
                        cursorclass=MySQLdb.cursors.DictCursor)

Which was replaced by:

 conn = MySQLdb.connect(host="newhost",
                        user="newuser",
                        passwd="newpass",
                        db="newdb")

The parameter cursorclass=MySQLdb.cursors.DictCursor at the end was making python allow me to access the rows using the column names as index. But the poor copy-paste eliminated that, yielding the error.

So, as an alternative to the solutions already presented, you can also add this parameter and access the rows in the way you originally wanted. ^_^ I hope this helps others.

Faust

This error is returned: AttributeError: 'module' object has no attribute 'cursors' – Owen

I am using PyMysql and this worked for me. Only change I needed to make was cursorclass=pymysql.cursors.DictCursor . Then, I can use the column names. Thanks! – Tache

Answer 5

Just adding a parameter like the below worked for me.

cursor=conn.cursor(dictionary=True)

I hope this would be helpful either.

Mush

You can also do this with Postgres: psycopg.org/docs/extras.html – Execratory

Is this for an old version? For sqlite3 the method to get rows represented as dicts is this. – Cudlip

Answer 6

The Problem is how you access row

Specifically row["waocs"] and row["pool_number"] of ocs[row["pool_number"]]=int(row["waocs"])

If you look up the official-documentation of fetchall() you find.

The method fetches all (or all remaining) rows of a query result set and returns a list of tuples.

Therefore you have to access the values of rows with row[__integer__] like row[0]

Parthen

Answer 7

It's really late but OP's problem can be averted by unpacking the tuples in result in the for statement as the result is iterated over. Given result looks like [('MA3146', 711L, 81L), ('MA3147', 679L, 83L), ('MA3148', 668L, 86L)], a solution could be as follows.

result = [('MA3146', 711, 81), ('MA3147', 679, 83), ('MA3148', 668, 86)]
ocs, oltv = {}, {}
for pool_number, waocs, waoltv in result:
    ocs[pool_number] = int(waocs)
    oltv[pool_number] = int(waoltv)

---

In general, if you got a TypeError in the title (or more specifically, TypeError: tuple indices must be integers or slices, not str), see if the following captures your case.

1. Iterate over a string and try to index a tuple. You probably never intended to iterate over a string and it should probably be a range/list etc. that returns integers.

   tpl = tuple(range(10))
   for i in '10':
       tpl[i]           # <---- TypeError: tuple indices must be integers...
   
   
   for i in range(int('10')):
       tpl[i]           # <---- OK
   

2. Iterate over an enumerate object without unpacking. Enumerate returns a tuple of index and item pairs, which can be directly unpacked as you iterate over it.

   lst = [{'a': 1}, {'a': 2}, {'a': 3}]
   for dct in enumerate(lst):
       dct['a']                       # TypeError
   
   
   for i, dct in enumerate(lst):
       dct['a']                       # OK
   

3. Index a namedtuple using a string. Even if namedtuple field names are defined using a string, namedtuples cannot be indexed using a string like a dict key. It can be indexed using integer indices or by field attributes.

   from collections import namedtuple
   tpl = namedtuple('Tuple', 'x, y')(x=1, y=2)
   
   tpl['a']     # <---- TypeError
   tpl[0]       # <---- OK
   tpl.x        # <---- OK
   

Adcock

Answer 8

SQlite3 has a method named row_factory. This method would allow you to access the values by column name.

https://www.kite.com/python/examples/3884/sqlite3-use-a-row-factory-to-access-values-by-column-name

Shiism

Welcome. Thanks for your contribution. Please edit to include the exact code necessary to implement the solution you suggest. As is, this may be considered (and would be welcomed as) a comment. As is, it's bordering on a Link-only response: not quite a complete, self-contained solution, as SO requires for Answers. When you have enough rep, you can add comments. Including reference links for follow up & explanations are hallmarks of quality answers, & tend to accumulate upvotes over time as users learn something to apply to their own coding issues. So, excellent. (code-only is discouraged) – Profanatory

Official documentation is here: docs.python.org/3/library/… – Cudlip

Answer 9

I see that you're trying to identify by the name of a row. If you are looking for a specific column within the row, you can do [integer][column name]

For example, to iterate through each row and only pull out the value from the row with the column header of "pool number", you can do this:

for row in df_updated.iterrows():
    cell = row[1]['pool number']
    print(cell)

The code will then iterate through each row but only print out the value that matches the "pool number" column

Chessman

Answer 10

This problem had me stumped for a while too. In order to have the sqlite cursor return dicts for each row, so you can use things like row["waocs"], you need to specify the row_factory. When connecting to the db, set the row_factory property of the connection:

conn = sqlite3.connect('database.db')
conn.row_factory = sqlite3.Row

Bobble