PymySQL insert and update separing request to param values

Question

I'm separing my request to the values for more security, using pymysql for python 2.7.

I have dynamic parameters so I'm using .join() to build my request.

This is how I do:

def updateItem(self, table, body, where):
    print 'updateItem'
    if body is not None:
        if body["data"]:
            key_list = []
            for key, data in zip(body['keys'], body['data']):
                placeholder = '{} = '.format(key) + '%s'
                key_list.extend([placeholder])
                val_list = ", ".join(key_list)
            req = """UPDATE """ +table+ """ SET {values} WHERE {where_key}={where_value};""".format(table=table, values=val_list, where_key=where[0], where_value=where[1])
            print req
            params = '"' + '", "'.join(body['data']) + '"'
            print params
            return self.update(req, (params))

And:

def update(self, request, params):
    print request
    try:
        affected_rows = self.cursor.execute(request, params)
        self.connexion.commit()
        logger.info("Affected rows: " + str(affected_rows))
        return affected_rows
    except Exception as e:
        raise e

The first print give me:

UPDATE organizations SET uuid = %s, permaname = %s, name = %s, short_description = %s, description = %s, website = %s, is_startup = %s, is_corporate = %s, logo = %s, headcount_min = %s, headcount_max = %s, is_closed = %s, number_of_investments = %s WHERE uuid=3325e470-542a-44cd-b094-3fcfd55bb32c;

And the second is :

"c7ba44f9-xxx-4a7b-89b0-xxxxx", "xxx-2", "xxxxxxx", "xxxxxxx is your higher xxxxxx advisor, guiding you to the xxxright course and xxxxx and the No. 1 xxxxx site in xxxxx.", "xxxxx is your higher xxxx advisor, guiding you to the xxxx xxxx and xxx and the Noxxxxxxx xxx site in xxxxx.", "http://xxxxxxxxx.my/", "0", "1", "http://xxx.xxxxx.xx/t_api_images/xxxxx/rgyzkulb0mslq9qbvien.jpg", "51", "100", "0", "0"

I have this error message :

not enough arguments for format string: TypeError Traceback (most recent call last): File "/var/task/setter_organizations.py", line 38, in handler structured_data.insert() File "/var/task/setter_organizations.py", line 109, in insert self.update(["uuid", uuid]) File "/var/task/setter_organizations.py", line 112, in update self.rds.updateItem(self.type, self.data, where) File "/var/task/RDS/rds.py", line 87, in updateItem return self.update(req, (params)) File "/var/task/RDS/rds.py", line 51, in update raise e TypeError: not enough arguments for format string

There is 13 keys and 13 values. I can not figure out what is wrong in my code, does anyone can help me ?

I also don't know how manage if I have quotes like " or ' in of of my fields, so any answer would be great.

Answer 1

Please check out a similar question's answer that I have given here: how to insert \ backslah before quotes in string python

For 13 arguments, you would modify it to something like this:

cur.execute('UPDATE table_name SET arg1=%s, num=1 where arg2=%s and arg3=%s and ...upto 13 args',(val1, val2,val3,...upto 13 values))

EDIT: (13 value update for 1 where clause)

cur.execute('UPDATE table_name SET arg1=%s, arg2=%s, arg3=%s, ...upto 13 args where where_column=val',(val1, val2,val3,...upto 13 values))