Python dictionary map to SQL string with list comprehension

Question

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

Note: this question was answered in a previous thread for a different query string, but this query string is more complicated and I'm not sure if it can be generated using the same list comprehension method.

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, HostName, Number) VALUES (%(computer_id.name)s, %(host_name)s, %(number)s)

I started with a list comprehension but I only was able to generate the first part of the query string so far with this technique.

queryStrInsert = '('+','.join([tm_val for tm_key, tm_val in tablemap_incident.items()])+')'
print(queryStrInsert)

#Output
#(computer_id,host_name,number)
#Still must generate the remaining part of the query string parameterized VALUES

Answer 1

If I understand what you're trying to get at, you can get it done this way:

holder = list(zip(*tablemap_computer.items()))

"insert into mytable ({0}) values ({1})".format(",".join(holder[0]), ",".join(["%({})s".format(x) for x in holder[1]]))

This should yield:

# 'insert into mytable (HostName,Number,ComputerID) values (%(host_name)s,%(number)s,%(computer_id)s)'

I hope this helps.