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, not str
Any help would be appreciated! Thanks people!
Like the error says, row
is a tuple, so you can't do row["pool_number"]
. You need to use the index: row[0]
.
I think you should do
for index, row in result:
If you wanna access by name.
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.
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]
Just adding a parameter like the below worked for me.
cursor=conn.cursor(dictionary=True)
I hope this would be helpful either.
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
Source: Stackoverflow.com