Writing a query in a simpler way to enable better substitution by pymysql

Question

I have the following query in which I want to substitute values for level and content from a list.

SELECT count(DISTINCT(USERS)) AS TOTAL_USERS FROM db.CCN 
JOIN UCR ON CCN.COLLECTIVE = UCR.COLLECTIVE 
WHERE USER NOT LIKE 'IMM%%' 
AND USER NOT LIKE 'UWEB%%'
AND( 
    (CCN.CONTENT='C1' AND CCN.LEVEL='L1')OR
    (CCN.CONTENT='C2' AND CCN.LEVEL='L2')OR
    (CCN.CONTENT='C3' AND CCN.LEVEL='L3')
)

Is there any way to simplify this query to be able to substitute the values for for content and level using python? I have thought of using two IN expressions but this would not work in the same way. I have list of combinations of contents and levels that I have to substitute. I would prefer to avoid the current syntax since I think I would have to use f-strings to build the query and I prefer to use the correct substitution that is supported by pymysql. I have tried using CONCAT in the WHERE but this is too slow.

Thank you,

Answer 1

Generate the sequence of OR expressions dynamically to create a parametrized statement with the appropriate number of %s placeholders.

content_levels = [('C1', 'L1'), ('C2', 'L2'), ('C3', 'L3')]
conditions = ' OR '.join(['(CCN.CONTENT=%s AND CCN.LEVEL=%s)'] * len(content_levels))
sql = f'''SELECT count(DISTINCT(USERS)) AS TOTAL_USERS FROM db.CCN 
JOIN UCR ON CCN.COLLECTIVE = UCR.COLLECTIVE 
WHERE USER NOT LIKE 'IMM%%' 
AND USER NOT LIKE 'UWEB%%'
AND ({conditions})'''
# flatten content_levels
params = sum(content_levels, start=())
cursor.execute(sql, params)