CODEWITHSUNDEEP
pythonpython-2.7sqlite
user2242044
user2242044

Reputation: 9213

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

Upvotes: 2

Views: 600

Answers (1)

Eugene Yarmash
Eugene Yarmash

Reputation: 149776

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)

Upvotes: 2

Related Questions