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

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.

CodePudding user response：

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] )

CodePudding user response：

I'm not sure why I'm still facing this error

here is the put route

@app.route('/movie/<int:movieid>', methods=['PUT'])
def updateMovie(movieid):
    try:
        movieJSON = request.json  # body JSON data
        rows = Movie.updateMovie(movieid, movieJSON)
        msg = str(rows) " row(s) updated"
        output = {"message": msg}
        return jsonify(output), 200
    except Exception as err:
        print(err)
        return jsonify({}), 500

while my movie class

@classmethod
    def updateMovie(cls, movieid, movieJSON):
        clauses = []
        values = []

        if movieJSON['name']:
            clauses.append('name=%s')
            values.append(movieJSON['name'])
        if movieJSON['description']:
            clauses.append('description=%s')
            values.append(movieJSON['description'])
        try:
            if values:
                values.append(movieid)
                dbConn = DatabasePool.getConnection()
                cursor = dbConn.cursor(dictionary=True)
                sql = "update movie set "   \
                    (','.join(clauses))   " where movieID=%s"
                print(sql)
                print(values)
                cursor.execute(sql, (values))
                dbConn.commit()
                rows = cursor.rowcount
                return rows

        finally:
            dbConn.close()

the print statement which work

update movie set name=%s,description=%s where movieID=%s
['asdasd', 'asdasda123', 21]
127.0.0.1 - - [03/Feb/2022 20:20:22] "PUT /movie/21 HTTP/1.1" 200 -

if i provide 1 of the fields i get

'description'
127.0.0.1 - - [03/Feb/2022 20:14:50] "PUT /movie/21 HTTP/1.1" 500 -

'name'
127.0.0.1 - - [03/Feb/2022 20:25:27] "PUT /movie/21 HTTP/1.1" 500 -