CODEWITHSUNDEEP
pythonmysqlsqldictionary
JakeCowton
JakeCowton

Reputation: 1445

Creating an SQL query from a dictionary

I have a function to update an entry in a database (MySQL) and it needs to be as dynamic as possible.

The function allows the user of the API to pass an id of a record and then kwargs to handle the values to change (there is no limit on how many values can be passed).

So lets say I get this dictionary from kwargs

{'hello':'world', 'foo':'bar'}

Is there a way I can use this in an update statement.

UPDATE myTable
SET key_n = val_n, key_n+1 = val_n+1, ...
WHERE id = the_id_passed_to_the_function

Thanks in advance.

Upvotes: 2

Views: 2276

Answers (3)

sotirov
sotirov

Reputation: 328

This works on Python 3

def sql_update_from_dictionary(self, update_table, update_dict, update_id):
    """
    UPDATE query from dictionary
    Args:
        update_table (str): Name of the table
        update_dict (dict): Data to be updated
        update_id    (str): id of the item that will be updated
    """
    sql = "UPDATE "+update_table+" SET {} WHERE id=".format(', '.join('{}=%s'.format(k) for k in update_dict))+str(update_id)
    db.execute(sql, list(update_dict.values()))
    connection.commit()

Upvotes: 0

Burhan Khalid
Burhan Khalid

Reputation: 174614

How about:

foo = kwargs['bar']
id = 1
if foo:
    k = foo.keys()
    q = '''UPDATE myTable'''
    q += ','.join('SET {0}=%s'.format(i) for i in k)
    q += 'WHERE id = %s'
    cur.execute(q,tuple(foo[i] for i in k)+(id,))

Some points to note:

  1. Since dictionaries are unordered, we are fetching a list of the keys first.
  2. Instead of putting the values directly in the query; you should pass them in. Again, we step through the same list of keys so that the values match the column names.
  3. Finally, we add the id to the set of ordered arguments to the query.

Upvotes: 0

Shashi
Shashi

Reputation: 2175

Try this:

def UpdateQuery(data,where):
    if isinstance(data, dict) and where:
        vals = ','.join(["%s=?" %(k) for k,v in data.iteritems()])
        query = "update myTable set %s where id=?" % (vals)
        res = cr.execute(query,where)

Upvotes: 5

Related Questions