'Reducing number of calls to Database

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.

mysqlpython-3.x


Solution 1:[1]

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)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Marcel Gohsen

Related Questions