creating mysql update statement from python dictionary with key in where clause

Question

I am having some challenges creating MySQL update statements from python dictionary with the key in where clause

Here is how my struggling code looks like:

for key, values in d.items():
    sql = """UPDATE news SET title={},avail={},URL={},status={},degree={}""".format(key, .join(values[0::2]),.join(values[1::3]),.join(values[2::4]),.join(values[3::5]))
    print(sql)

Here are the values in my dictionary

d={'ZB06WW34556GND5G': [u'[<font color="red">Google</font>] News: Sports & Outdoors',
                ' Only 13',
                u'https://www.google.com',
                u'13',
                '-2.0'],
'ZB06WWG4444444ND5G': [u'[<font color="red">CNN</font>] News: Sports & Outdoors',
                ' Only 15',
                u'https://www.cnn.com',
                u'14',
                '-4.0']
                }

Following are expecting results:

UDPATE new SET title="[<font color="red">Google</font>] News: Sports & Outdoors",avail="Only 13",URL="https://www.google.com",status="13",degree="-2.0" where id="ZB06WW34556GND5G"
UDPATE new SET title="[<font color="red">CNNGoogle</font>] News: Sports & Outdoors",avail="Only 15",URL="https://www.cnn.com",status="14",degree="-4.0" where id="ZB06WWG4444444ND5G"

Can somebody guide me in the proper direction? I am lost in keys and values...

pgngp · Accepted Answer · 2018-05-03 01:03:18Z

1

The following code gives the expected output:

for key, values in d.items():
    sql = """UPDATE news SET title = '{}', avail = '{}', URL = '{}', status = '{}', degree = '{}' where id = '{}'""".format(d[key][0], d[key][1], d[key][2], d[key][3], d[key][4], key)
    print(sql)

answered May 3, 2018 at 1:03

pgngp

1,5805 gold badges16 silver badges27 bronze badges

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

Collectives™ on Stack Overflow

creating mysql update statement from python dictionary with key in where clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related