python iterate over sql query for select statement

user_5893842 0
Viewed 15676

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)))
python-3.x
2 Answers

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)
user_20007930

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)))
user_50414150
Related