2

I have a dictionary of values where the keys are the column names. I want to send a single update statement that included all of the data to update. I loop through the dict keys and then add the corresponding values during the query. I am stuck on how to move forward. Here is what I have:

cart={}
sn = request.form['cartSN']
cart["camera_front_SN"] = request.form['frontCameraSN']
cart["battery_board_SN"] = request.form['batterySN']
cart["speaker_board_SN"] = request.form['speakerSN']
cart["ibcs_SN"] = request.form['ibcsSN']
table = 'cart_info'
sql = 'UPDATE '+table+' SET {} WHERE cart_SN = '+sn+' '.format(', '.join('{}=%s'.format(k) for k in cart))
print(sql)
db = MySQLdb.connect(host="localhost",
                     user="nope",
                     password="*******",
                     db="cart_data")
cur = db.cursor(MySQLdb.cursors.DictCursor)
cur.execute(sql, [cart.values()])
db.commit()
db.close()

When I run this i get the following error:

    [2018-03-25 06:08:39,972] ERROR in app: Exception on /update [POST]
Traceback (most recent call last):
  File "C:\Users\Colin Roberts\AppData\Local\Programs\Python\Python36-32\lib\site-packages\MySQLdb\cursors.py", line 238, in execute
    query = query % args
TypeError: not all arguments converted during string formatting

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\Colin Roberts\AppData\Local\Programs\Python\Python36-32\lib\site-packages\flask\app.py", line 1982, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\Users\Colin Roberts\AppData\Local\Programs\Python\Python36-32\lib\site-packages\flask\app.py", line 1614, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\Users\Colin Roberts\AppData\Local\Programs\Python\Python36-32\lib\site-packages\flask\app.py", line 1517, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "C:\Users\Colin Roberts\AppData\Local\Programs\Python\Python36-32\lib\site-packages\flask\_compat.py", line 33, in reraise
    raise value
  File "C:\Users\Colin Roberts\AppData\Local\Programs\Python\Python36-32\lib\site-packages\flask\app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\Users\Colin Roberts\AppData\Local\Programs\Python\Python36-32\lib\site-packages\flask\app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "FlaskApp.py", line 154, in cartUpdate
    cur.execute(sql, [cart.values()])
  File "C:\Users\Colin Roberts\AppData\Local\Programs\Python\Python36-32\lib\site-packages\MySQLdb\cursors.py", line 240, in execute
    self.errorhandler(self, ProgrammingError, str(m))
  File "C:\Users\Colin Roberts\AppData\Local\Programs\Python\Python36-32\lib\site-packages\MySQLdb\connections.py", line 52, in defaulterrorhandler
    raise errorclass(errorvalue)
_mysql_exceptions.ProgrammingError: not all arguments converted during string formatting

I am stuck. Also, I don't like this 'your post is mostly code; please add more details". No, my post is mostly error message. However, I would rather include too much error message than not enough.

1 Answer 1

3

Change your SQL

sql = 'UPDATE '+table+' SET {} WHERE cart_SN = '.format(', '.join('{}=%s'.format(k) for k in cart))+sn

Because in your case

sql = 'UPDATE '+table+' SET {} WHERE cart_SN = '+sn+' '.format(', '.join('{}=%s'.format(k) for k in cart))

format is applied to ' '.fomat() empty string not the main string

Sign up to request clarification or add additional context in comments.

4 Comments

Thank you. I tried this and got a different error, which is progress!! I am now getting TypeError: not enough arguments for format string
That did it. I needed single quotes around the sn variable. Thank you all!!
Welcome!! @ColinRoberts

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.