Updating multiple SQLite rows given columns and data

Question

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 UPDATE 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)

Eugene Yarmash · Accepted Answer · 2016-04-27 20:30:40Z

2

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)))
        print(sql)
        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)

edited Apr 27, 2016 at 20:30

answered Apr 27, 2016 at 20:07

Eugene Yarmash

152k44 gold badges346 silver badges391 bronze badges

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

4 Comments

user2242044 Over a year ago

Thank you. Much cleaner!

user2242044 Over a year ago

One comment. I think SQLite uses ? as a placeholder rather than %s

Cubic Over a year ago

@user2242044 Then why did you use %s in your question?

Eugene Yarmash Over a year ago

@Cubic: %s in the question is not a placeholder; it's a conversion specifier for the python's string formatting operator %.

Collectives™ on Stack Overflow

Updating multiple SQLite rows given columns and data

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related