incompatibility with sqlite-utils v2+ #20
Description
Hi @simonw and thanks for such great tools! I'm using datasette on http://data.greenferries.org/ , it's been a delight.
I think there is an incompatibility with the breaking changes you made on upsert_all in version 2+ of sqlite-utils.
I get this error when trying to convert a PostgresQL db:
$ db-to-sqlite --all "postgresql://localhost/greenferries_prod_tmp" greenferries.db
Traceback (most recent call last):
File "/Users/adipasquale/.venvs/greenferries-data/bin/db-to-sqlite", line 8, in <module>
sys.exit(cli())
File "/Users/adipasquale/.venvs/greenferries-data/lib/python3.7/site-packages/click/core.py", line 764, in __call__
return self.main(*args, **kwargs)
File "/Users/adipasquale/.venvs/greenferries-data/lib/python3.7/site-packages/click/core.py", line 717, in main
rv = self.invoke(ctx)
File "/Users/adipasquale/.venvs/greenferries-data/lib/python3.7/site-packages/click/core.py", line 956, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/Users/adipasquale/.venvs/greenferries-data/lib/python3.7/site-packages/click/core.py", line 555, in invoke
return callback(*args, **kwargs)
File "/Users/adipasquale/.venvs/greenferries-data/lib/python3.7/site-packages/db_to_sqlite/cli.py", line 111, in cli
db[table].upsert_all(rows, pk=pk)
File "/Users/adipasquale/.venvs/greenferries-data/lib/python3.7/site-packages/sqlite_utils/db.py", line 1159, in upsert_all
upsert=True,
File "/Users/adipasquale/.venvs/greenferries-data/lib/python3.7/site-packages/sqlite_utils/db.py", line 1086, in insert_all
result = self.db.conn.execute(query, params)
sqlite3.OperationalError: near "WHERE": syntax error
I've managed to mitigate this problem by adding sqlite-utils==1.12.1 to my requirements.txt before db-to-sqlite[postgresql], and now it works as expected.
I tried to understand the actual problem with v2 but I'm not familiar enough with your codebase sorry :/ let me know if I can help somehow.
After digging, it seems to be linked to a specific table in my PostgreSQL db, namely the rails Schema migrations one that contains a single varchar column with 21 entries in my case (surprising). I've bissected it to db-to-sqlite --table schema_migrations "postgresql://localhost/greenferries_prod_tmp" greenferries.db triggering the error, I can skip it and it fixes the problem (with sqlite-utils v2+ that is).
I've uploaded a small SQL dump on gist so you can reproduce:
curl https://gist.githubusercontent.com/adipasquale/aae8ece24005f1a6e39b2b3bd529ce4f/raw/af456652df0618bd2b53b3676a9154644924a153/tmp.sql > tmp.sql
createdb brokendb
psql brokendb < tmp.sql
db-to-sqlite "postgresql://localhost/brokendb" greenferries.db
(btw I don't actually need this table in datasette, so I've only created this issue for reference).