CODEWITHSUNDEEP
pythonmysqlpython-2.7nullsql-update
Anna
Anna

Reputation: 281

Dealing with null in update where clause without hard-coding a statement

I want to write a method that generates an update statement, without hard-coding columns and values. The statement is going to include optional where clause and is going to be passed to executemany, the clause contains only columns and values, there is no select in there. Example:

update TABLE
set
    Col1 = 'a',
    Col2 = 'b',
    Col3 = 'c'
where
    Col4 = 'd'
        and Col5 = 'e'
        and Col1 is null;

What I wrote so far: 

def update(self, table_name, update_columns, values, where_columns=None, where=True):
    update_columns_and_values = self.generator.generateColumnsAndPlaceholders(update_columns)
    if where:
        where_clause = self.generator.generateWhereClause(where_columns)
    else:
        where_clause = ''
    query = '''
            update {t}
            set
                {cv}
            {w}
        '''.format(t=table_name, cv=update_columns_and_values, w=where_clause)
    self.cursor.executemany(query, values)
    self.connection.commit()

def generateColumnsAndPlaceholders(columns):
    if type(columns) is str:
        columns = columns.split(', ')
    return ', \n'.join([str(c) + ' = ' + "'%s'" for c in columns])

Now, how should I write a function generateWhereClause that takes any number of columns and returns a where clause with placeholders adjusted for both a not null value (indicated with =) and a null value (indicated with is null)? Also, I think that string returned by generateColumnsAndPlaceholders is not prepared for null due to single quotes around placeholders. If so, how should I change it?

In general, how do I deal with null in update statement without hard-coding specific statement?

Upvotes: 1

Views: 138

Answers (1)

Mateusz
Mateusz

Reputation: 452

The function that generates the query - it takes table name, dictionary of values for columns {column: value} and dictionary of constraints, that respects None as constraint {column: constraint}

def update_query(table_name, values, constraints):
    v_list = [k + '=' + '"' + v + '"' for k, v in values.iteritems()]
    v_query = ', '.join(v_list)
    c_list = [k + (' IS NULL' if c is None else '=' + '"' + c + '"') for k, c in constraints.iteritems()]
    c_query = ' AND '.join(c_list)
    return 'UPDATE ' + table_name + ' SET ' + v_query + ' WHERE ' + c_query

Test code:

tn = "table"
vl = {"Col1":"a","Col2":"b","Col3":"c"}
cn = {"Col4":"d","Col5":"e","Col":None}

Result:

UPDATE table SET Col2="b", Col3="c", Col1="a" WHERE Col6 IS NULL AND Col4="d" AND Col5="e"

I hope order is not an issue for you.

Upvotes: 1

Related Questions