Dynamically Generating SQL Queries with Python and SQLite3

Question

Below is a generalisation of my problem:

Consider the table

    ID    A    B    C
r1  1     1    0    1
.   .     .    .    .
.   .     .    .    .
.   .     .    .    .
rN  N     1    1    0

Where the columns A,B,C contain either 0 or 1. I am trying to write a python function that takes a list of permutations of 0's and 1's, generates a query that will passed to SQLite3 that then counts the number of records that have A,B,C in one of these permutations.

For example if I passed the following list to my function permList = [[1,0,1],[1,0,0]], then it would count all records with the [A,B,C] combination as either [1,0,1] or [1,0,0].

Currently I am doing it like this

def permCount(permList):
    SQLexpression = "SELECT Count(*) FROM Table WHERE "

    for i in range(len(permList)):
        perm = permList[i]
        SQLexpression += "(A=" + str(perm[0]) + " AND B=" + str(perm[1]) + 
                      " AND C=" + str(perm[2]) + ")"
        if i!=len(permList)-1:
            SQLexpression += " OR "

    *Execute SQLexpression and return answer*

Now this is fine but it seems like a bit of a fiddle. Is there a better way to dynamically generate the SQL queries where the length of input permList is unknown?

Solution 1

def permCount(permList):
    condition = ' OR '.join(['(A=? AND B=? AND C=?)' 
                             for row in permList])
    sql = "SELECT Count(*) FROM Table WHERE {c}".format(
        c=condition)
    args = sum(permList, [])
    cursor.execute(sql, args)

Use parametrized SQL. That means instead of inserting the values with string formatting, use placemarkers (e.g. ?), and then supply the arguments as the second argument to cursor.execute.

This is easier code and prevents SQL injection.

Solution 2

Try these changes in your main for loop, to make use of pythons generators and list comprehension features.

def permCount(permList):

SQLexpression = "SELECT Count(*) FROM Table WHERE "

for perm in permList:    # if you need the i for other reason, you could write:
                         # for i, perm in enumerate(permList)

    a, b, c = [str(_) for _ in perm]

    SQLexpression += "(A=" + a + " AND B=" + b + \
                  " AND C=" + c + ") OR "

SQLexpression = SQLexpression[:-4] + ";"   # Trim the last " OR "