python iterate over sql query for select statement

Question

How do i execute select statement for all the 4 id's that is 10, 20, 34 and 1 and would expect the result to be in a list

import pymysql
import json

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='', 
db='mydb', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

cursor = conn.cursor()

select_query = "select * from users where id = "
ids = ["10", "20", "34", "1"]
for x in ids:
    var = select_query + x
    # print(var)
    print(list(cursor.execute(var)))

What results are you currently getting? How are the desired results different from that? — Eric Ed Lohmar
– Eric Ed Lohmar, Commented Sep 19, 2017 at 16:23
getting error - TypeError: 'int' object is not iterable. not sure where i went wrong — deepak murthy
– deepak murthy, Commented Sep 19, 2017 at 16:25
The stack trace will tell you the line at which the error occurred. Which line was it, in this case? — Eric Ed Lohmar
– Eric Ed Lohmar, Commented Sep 19, 2017 at 16:30
stack trace - Traceback (most recent call last): File "C:\xampp\htdocs\sandbox\for.py", line 13, in <module> print(list(cursor.execute(var))) TypeError: 'int' object is not iterable — deepak murthy
– deepak murthy, Commented Sep 19, 2017 at 16:32

Eric Ed Lohmar · Accepted Answer · 2017-09-19 16:38:10Z

According to the PyMySQL docs, the cursor.execute() function returns an int, which is why your error is showing up. You'll want to call that, then use one of the fetch functions to actually return the results

import pymysql

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='',
                       db='mydb', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

cursor = conn.cursor()

select_query = "select * from users where id = "
ids = ["10", "20", "34", "1"]
for x in ids:
    var = select_query + x
    # print(var)
    cursor.execute(var)
    print(list(cursor.fetchall()))

or, if you want all of the results in one list:

import pymysql

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='',
                       db='mydb', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

cursor = conn.cursor()

lst = []

select_query = "select * from users where id = "
ids = ["10", "20", "34", "1"]
for x in ids:
    var = select_query + x
    # print(var)
    cursor.execute(var)
    lst.extend(cursor.fetchall())

print(lst)

or as a dictionary

import pymysql

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='',
                       db='mydb', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

cursor = conn.cursor()

results = {}

select_query = "select * from users where id = "
ids = ["10", "20", "34", "1"]
for x in ids:
    var = select_query + x
    # print(var)
    cursor.execute(var)
    results[x] = list(cursor.fetchall())

print(results)

Dharmendra Singh · Accepted Answer · 2017-09-19 16:28:20Z

0

You probably forgot to typecast x to str

import pymysql
import json

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='', 
db='mydb', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

cursor = conn.cursor()

select_query = "select * from users where id = "
ids = ["10", "20", "34", "1"]
for x in ids:
    var = select_query + str(x)    # here
    # print(var)
    print(list(cursor.execute(var)))

answered Sep 19, 2017 at 16:28

Dharmendra Singh

162 bronze badges

1 Comment

deepak murthy Over a year ago

getting same error - stack trace. Traceback (most recent call last): File "C:\xampp\htdocs\sandbox\for.py", line 14, in <module> print(list(cursor.execute(var))) TypeError: 'int' object is not iterable

Collectives™ on Stack Overflow

python iterate over sql query for select statement

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related