how do i skip empty values supplied to %s in python flask mysql connector

Question

For example im doing a query to my database

try:
            dbConn = DatabasePool.getConnection()
            cursor = dbConn.cursor(dictionary=True)
            sqlquery = f"update table set name=%s if(name=%s is not null),description=%s if(description=%s is not null) where jsonid=%s"
            cursor.execute(
                sqlquery, (JSON['name'], JSON['description'], jsonid))

            dbConn.commit()
            rows = cursor.rowcount
            return rows

        finally:
            dbConn.close()

if i leave the name field blank. i will still be able to update my description likewise for my name.

how do i go about it doing it with the %s

i have also tried another way

sql = f"update table set name=if(name is null, name, %s), description = if(description is null, description, %s) where jsonID=%s"
            cursor.execute(
                sql, (JSON['name'], JSON['description'], jsonid))

i have to provide the 2 fields, if not it will throw me an 500 internal server error if one of the field is less.

Answer 1

If the set of fields you're updating is variable, then you need to build your query dynamically. Note here that I add additional "SET" clauses based on which fields are present.

        clauses = []
        values = []
        if JSON['name']:
            clauses.append( 'name=%s' )
            values.append( JSON['name'] )
        if JSON['description']:
            clauses.append( 'description=%s' )
            values.append( JSON['description'] )
        if values:
            values.append( jsonid )
            cursor = dbConn.cursor(dictionary=True)
            sqlquery = "UPDATE table SET " + (','.join(clauses)) + " WHERE jsonid=%s;"
            cursor.execute( sqlquery, values )
            rows = cursor.rowcount
            dbConn.commit()
            return rows

If you have more than just these two fields, you can make it even more automated:

        for column in ('name','description'):
            if JSON[column]:
                clauses.append( column + '=%s' )
                values.append( JSON[column] )