Reducing number of calls to Database-CodePudding

I am using the following approach to make db calls,

 for record in records:
        num = "'" str(record['Number']) "'"
        id = "'" str(record['Id']) "'"
        query = """select col2_text,col3_text from table where id= {} and num = {} and is_active = 'Y';""".format(id,num)

Since it is iteration where total number of DB calls is equal to the number of records. I want to optimize my call and make minimum number of DB calls, ideally in a single call.

CodePudding user response：

You can reduce the number of DB calls to a single one. You might want to have a look at the SQL-IN operator.

You could do the following:

values = ""

for record in records:
    num = "'" str(record['Number']) "'"
    id = "'" str(record['Id']) "'"
    
    values  = "({},{}),".format(num, id)

values = values[:-1]

query = """select col2_text,col3_text from table where (id, num) in ({}) and is_active = 'Y';""".format(values)