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.

hridayns · Accepted Answer · 2017-06-29T12:33:22.673

0

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

edited Jun 29 '17 at 12:33

answered Jun 29 '17 at 10:56

hridayns

697
8
16

ok but that only answer to a part of my question and I m not sur it exist escape_string for pymysql @code_byter – JunkyKiller Jun 29 '17 at 11:00
See EDIT 2 in the link. The other part of your question is about the number of arguments you have supplied. I am still trying to figure that out. Please wait. EDIT: I have a strong feeling the way you are passing the params is the reason its not able to get those 13 arguments.Try supplying all 13 arguments individually to see if it works. – hridayns Jun 29 '17 at 11:03
But how can I do insert if I follow your way ? – JunkyKiller Jun 29 '17 at 12:10
`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))` – hridayns Jun 29 '17 at 12:23
Can you please edit your answer to make it clearer ? – JunkyKiller Jun 29 '17 at 12:23
@JunkyKiller edited. Do tell me if I have not understood your question. – hridayns Jun 29 '17 at 12:26
Thank you for your help @code_byter, but in my case I want to update 13 value for only one where value – JunkyKiller Jun 29 '17 at 12:30
that's not what I ve did ? I did : cur.execute("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;", (all values)) – JunkyKiller Jun 29 '17 at 12:38
Yes, but you've used params to store them. I'm asking you to try them individually. So basically, you will have 13 variables in place of `params` – hridayns Jun 29 '17 at 12:39
Understood! But all my values are in a dict, so how can I pass them individually ? – JunkyKiller Jun 29 '17 at 12:46
if I do : cur.execute('UPDATE table_name SET arg1=%s, arg2=%s, arg3=%s, ...upto 13 args where where_column=val',(dict['value'])) it like giving this in parameter... ['09e1b295-2d9e-4a52-85fe-7c1d5215', u'afterschool-2', u'Afterschool', u'Afterschool is your higher education advisor, nd institution ahe Nysia.', u'Afterschool is your higher edisor, guiding you to the right course and institution and the No. 1 education site in.', u'http://afterschool.my/', u'0', u'1', u'http://public.crunchbase.com/t_api_images/v1489043271/rgyzkulb0mslq9qbvien.jpg', u'51', u'100', '0', u'0'] – JunkyKiller Jun 29 '17 at 12:55
Do you know how to do the insert following this method @code_byter ? – JunkyKiller Jun 29 '17 at 13:52
I'm sorry, confused why you ticked my answer, if it was not resolved? Can you explain your above question again? – hridayns Jun 29 '17 at 15:36

PymySQL insert and update separing request to param values

1 Answers1