Search code examples
python-2.7dictionarypymssql

Python dictionary map to SQL query string

I have a python dictionary that maps column names from a source table to a destination table.

Dictionary:

tablemap_computer = {
    'ComputerID' : 'computer_id',
    'HostName' : 'host_name',
    'Number' : 'number'
}

I need to dynamically produce the following query string, such that it will update properly when new column name pairs are added to the dictionary.

ComputerID=%(computer_id)s, HostName=%(host_name), Number=%(number)s

What is a good way to do this?

I have a start at this but it is very messy and has an extra comma at the end of the last element.

queryStrInsert = ''
for tm_key, tm_val in tablemap_computer.items():
    queryStrInsert += tm_key+'=%('+tm_val+')s,'
Solution

  • You might want to try using join and list comprehension.

    query = ', '.join([key + '=%(' + value + ')s' for key, value in tablemap_computer.items()])

    Using the dictionary you gave as an example one would end up with the following string:

    HostName=%(host_name)s, Number=%(number)s, ComputerID=%(computer_id)s