9

I am working on database connectivity in Python 3.4. There are two columns in my database.

Below is the query which gives me all the data from two columns in shown format QUERY:

cur.execute(""" select * from filehash """)
data=cur.fetchall()
print(data)

OUTPUT:

[('F:\\test1.py', '12345abc'), ('F:\\test2.py', 'avcr123')]

To iterate through this output, my code is as below

cur.execute(""" select * from filehash """)
data=cur.fetchall()

i=0
j=1

for i,row in data:
    print(row[i])
    print(row[j])
    i=i+1

This gives me below error

print(row[i])
TypeError: string indices must be integers

Let me know how can we work on individual values of fetchall()

timgeb
  • 76,762
  • 20
  • 123
  • 145
npormambi
  • 99
  • 1
  • 1
  • 2
  • @ Redbeard011010 repzero zvone I can idivudally access like row[0], row[1] but when I loop it using for i,j in data: print(data[i]) print(data[j]) I am getting below error print(data[i]) TypeError: list indices must be integers, not str same for enumerate(data) as well – npormambi Dec 25 '15 at 15:34
  • @ Redbeard011010 repzero zvone I can idivudally access like row[0], row[1] but when I loop it using for i,j in data: print(data[i]) print(data[j]) I am getting below error print(data[i]) TypeError: list indices must be integers, not str same for enumerate(data) as well – npormambi Dec 25 '15 at 15:46

5 Answers5

6

It looks like you have two colunms in the table, so each row will contain two elements.

It is easiest to iterate through them this way:

for column1, column2 in data:

That is the same as:

for row in data:
    column1, column2 = row

You could also, as you tried:

for row in data:
    print row[0] # or row[i]
    print row[1] # or row[j]

But that failed because you overwrote i with the value of first column, in this line: for i, row in data:.

EDIT

BTW, in general, you will never need this pattern in Python:

i = 0
for ...:
    ...
    i += 1

Instead of that, it is common to do simply:

for item in container:
    # use item

# or, if you really need i:
for i, item in enumerate(container):
    # use i and item
zvone
  • 18,045
  • 3
  • 49
  • 77
  • @ Redbeard011010 repzero zvone I can idivudally access like row[0], row[1] but when I loop it using for i,j in data: print(data[i]) print(data[j]) I am getting below error print(data[i]) TypeError: list indices must be integers, not str same for enumerate(data) as well – npormambi Dec 25 '15 at 15:32
  • @npormambi You are now doing worse than in the question ;) npormambi explained well in his answer, but once again from a different angle: `data[7]` is 8th row; `data[7][1]` is 2nd column in 8th row; `for x in data` takes row by row from data and puts it in x, so you can access `x[0]` and `x[1]` to get columns; `for x, y in data` reads row by row from data, unpacks it and puts columns in `x`and `y`. It is cruicial that you understand that part perfectly. Now, **your error**: after `for i, j in data`, column data is written to `i` and `j`, they are no longer 0 and 1, so `data[i]` is just wrong. – zvone Dec 25 '15 at 16:37
  • @npormambi To get a better understanding of this, try printing everything: `print (data)`, `print (row)`, `print (i)`, `print (j)` in different places, especially in the line before the error. – zvone Dec 25 '15 at 16:40
2

To iterate over and print rows from cursor.fetchall() you'll just want to do:

for row in data:
    print row

You should also be able to access indices of the row, such as row[0], row[1], iirc.

Of course, instead of printing the row, you can manipulate that row's data however you need. Imagine the cursor as a set of rows/records (that's pretty much all it is).

Redbeard011010
  • 954
  • 6
  • 20
1

looking at

[('F:\\test1.py', '12345abc'), ('F:\\test2.py', 'avcr123')]
  i              j                 i            j

you are taking a strings i and j and indexing it like

   print(row['F:\\test1.py'])
    print(row['12345abc'])

which gave you typeError

TypeError: string indices must be integers

this is because i in data is a string and your a indexing this

try this

for i,j in data:
    print(i)
    print(j)
repzero
  • 8,254
  • 2
  • 18
  • 40
  • Hello repzero, I tried the link shown by you still not working using Mysql database... – npormambi Dec 25 '15 at 16:22
  • have you remove your variables "j=1" and "i=0" and "i=i+1" from your codes and does your data variables contains "[('F:\\test1.py', '12345abc'), ('F:\\test2.py', 'avcr123')]"? ....print(data) to verify this is the true value – repzero Dec 25 '15 at 16:51
0

As the output you given [('F:\\test1.py', '12345abc'), ('F:\\test2.py', 'avcr123')]

for i, row in data:
    print(i)  # i is column1's value
    print(row)# row is column's value

So you don't need row[i] or row[j], that was wrong, in that each step of that iteration 

for i, row in data

is the same as i, row = ('abc', 'def') it set abc to variable i and 'def' to row

BTW ,I don't know what database you use, if you use Mysql and python driverMySQL Connector, you can checkout this guide to fetch mysql result as dictionary you can get a dict in iteration, and the keys is your table fields' name. I think this method is convenient more.

Kevin Yan
  • 1,236
  • 11
  • 19
0

To iterate through this: [('F:\test1.py', '12345abc'), ('F:\test2.py', 'avcr123')]

Code:
for i in data:
   print i[0] + '\t' + i[1]

Output:

F:\test1.py 12345abc
F:\test2.py avcr123

SANTOSH SINGH
  • 1