Python dictionary map to SQL query string

Question

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,'

Niebieski · Accepted Answer · 2017-01-13 20:13:17Z

1

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

answered Jan 13, 2017 at 20:13

Niebieski

1356 bronze badges

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

1 Answer 1