user2242044 user2242044 - 2 years ago 147
Python Question

Updating multiple SQLite rows given columns and data

I'd like to be able to update multiple SQLite database rows given a list of columns and a list of lists of data. I have a working solution, but it's not elegant at all. How can I do this more efficiently. I've left out the actual

statement as I am just focusing on building the query.

def update(columns, values):
for value in values:
print ("Update myTable SET " + " ".join([x + ' = "%s",' for x in columns]) + ' WHERE SSN = "%s"').replace(", WHERE", " WHERE") % (tuple(value))

columns = ['Name', 'Age']
values = [['Jon', 12, '545-45-7987'], ['Sam', 13, '454-78-4545']]

update(columns, values)

Answer Source

You shouldn't be interpolating values for your UPDATE; rather you should be using placeholders and query parameters. This will avoid quoting complications and potential SQL injection vulnerabilities. Something like:

def update(cursor, columns, values):
    for value in values:
        sql = "UPDATE myTable SET {} WHERE ssn = ?".format(
            ", ".join(map("{} = ?".format, columns)))
        cursor.execute(sql, values)      

columns = ['Name', 'Age']
values  = [['Jon', 12, '545-45-7987'], ['Sam', 13, '454-78-4545']]
cursor = conn.cursor()  
update(cursor, columns, values)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download