SQLite3 Python update whole row with a list

Other Tech Topics
1

###System###

  • Python 2.7
  • SQLite3

###Info###

I have table with the following structure:

c.execute("CREATE TABLE foo (id INTEGER, number INTEGER, number2 REAL, letters TEXT)")

I got the following list

list_foo = [9, 1, 2.3, 'abc']

In the table I already have fields, so the table should in this case be updated where id=9

###Question###
How do I update a row with my list variable?

###Solution idea###
c.execute("UPDATE foo SET VALUES = ? WERE id = ?", (list, list[0]))

But of course VALUES doesn't work. How do I instantly update the row without typing:

c.execute("UPDATE foo SET id = ?, number = ?, number2 = ?, letters = ? WERE id = ?", (list_foo[1], list_foo[2], list_foo[3], list_foo[0]))

My actual table has more entries than this, so it would be a pain to type it like this.

###stackoverflow##
h ttp://stackoverflow.com/questions/30194417/sqlite3-python-update-whole-row-with-a-list

###Edit###
If it's not possible and I have to use my long SQLite3 code, is it at least possible to have:

(list_foo[1:], list_foo[0])

instead of

(list_foo[1], list_foo[2], list_foo[3], list[0]))
2

You pretty much need to do what you don't want to do. That is, have a template UPDATE statement with the columns you want to updated.

However, if you have a large application, consider an ORM manager like http://www.sqlalchemy.org/

3

It's just once in my code, so an ORM wouldn't be useful, but I was hoping it could be shorter than what I had. Is it at least possible to have (list_foo[1:], list_foo[0])? Should I somehow fist convert the list_foo to a dict?

4

You can always do this:

>>>"UPDATE foo set bar = {bar}".format(bar="newbar")
'UPDATE foo set bar = newbar'

The Python format function is quite powerful. You can inline it like I did or pass it a list or dictionary using vformat:

vformat(format_string, args, kwargs)This function does the actual work of formatting. It is exposed as a
separate function for cases where you want to pass in a predefined dictionary of arguments, rather than unpacking and repacking the dictionary as individual arguments using the *args and **kwargs syntax. vformat() does the work of breaking up the format string into character data and replacement fields.

5

But if I then want to update all the parameters I have to write even more code?

"UPDATE foo SET id={id}, number={num}".format(list_foo[1], list_foo[2])

is longer than:

"UPDATE foo SET id=?, number=?", (list_foo[1], list_foo[2])
6

see Sqlite3 with Python for examples that may fit your needs