table.upsert_all() fails if input has a single column that should be a primary key

[simonw]

This works:

>>> db['foo'].insert_all([{"name": "hello"}], pk="name")
<Table foo (name)>

But this fails:

>>> db['foo3'].upsert_all([{"name": "hello"}], pk="name")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/simon/.local/share/virtualenvs/datasette.io-TK86ygSO/lib/python3.9/site-packages/sqlite_utils/db.py", line 1837, in upsert_all
    return self.insert_all(
  File "/Users/simon/.local/share/virtualenvs/datasette.io-TK86ygSO/lib/python3.9/site-packages/sqlite_utils/db.py", line 1778, in insert_all
    self.insert_chunk(
  File "/Users/simon/.local/share/virtualenvs/datasette.io-TK86ygSO/lib/python3.9/site-packages/sqlite_utils/db.py", line 1588, in insert_chunk
    result = self.db.execute(query, params)
  File "/Users/simon/.local/share/virtualenvs/datasette.io-TK86ygSO/lib/python3.9/site-packages/sqlite_utils/db.py", line 213, in execute
    return self.conn.execute(sql, parameters)
sqlite3.OperationalError: near "WHERE": syntax error

With the debugger:

>>> import pdb; pdb.pm()
> /Users/simon/.local/share/virtualenvs/datasette.io-TK86ygSO/lib/python3.9/site-packages/sqlite_utils/db.py(213)execute()
-> return self.conn.execute(sql, parameters)
(Pdb) print(sql, parameters)
UPDATE [foo3] SET  WHERE [name] = ? ['hello']

[simonw]

Looks like this is the problem:

sqlite-utils/sqlite_utils/db.py

Lines 1724 to 1742 in b0f9d1e

	for record_values in values:
	# TODO: make more efficient:
	record = dict(zip(all_columns, record_values))
	sql = "INSERT OR IGNORE INTO [{table}]({pks}) VALUES({pk_placeholders});".format(
	table=self.name,
	pks=", ".join(["[{}]".format(p) for p in pks]),
	pk_placeholders=", ".join(["?" for p in pks]),
	)
	queries_and_params.append((sql, [record[col] for col in pks]))
	# UPDATE [book] SET [name] = 'Programming' WHERE [id] = 1001;
	set_cols = [col for col in all_columns if col not in pks]
	sql2 = "UPDATE [{table}] SET {pairs} WHERE {wheres}".format(
	table=self.name,
	pairs=", ".join(
	"[{}] = {}".format(col, conversions.get(col, "?"))
	for col in set_cols
	),
	wheres=" AND ".join("[{}] = ?".format(pk) for pk in pks),
	)

Note how set_cols = [col for col in all_columns if col not in pks] can potentially return an empty list if ALL of the columns are primary keys - but the next line of code that assigns sql2 continues regardless, when it should instead be skipped if there are no columns in set_cols.