incompatibility with sqlite-utils v2+

[adipasquale]

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

[simonw]

Thanks for the detailed bug report! This is definitely a compatibility problem with sqlite-utils 2.0, looking into it now.

[simonw]

OK, I think I have a fix for this in https://github.com/simonw/db-to-sqlite/pull/21

@adipasquale would you mind testing this for me? You can install the fix directly from the branch like this:

pip install https://github.com/simonw/db-to-sqlite/archive/sqlite-utils-2.zip

[simonw]

I just tested it against your example from above and it works (whereas older versions fail).

[simonw]

Fixed in https://github.com/simonw/db-to-sqlite/commit/80c064feec51853dadeaffb7205a5dfd4aa32ad4 - I will release this shortly as db-to-sqlite 1.1.

[adipasquale]

hi @simonw thanks for the incredibly fast answer and fix :o

I tried to test it with your git branch but I think it still installed and used the master version when pip installs the db-to-sqlite package and its dependencies .. my pip skills are quite restricted, sorry.

If it works for you with the example I gave it should be ok! Thanks 🙇

[simonw]

This is now available to install from PyPI directly: pip install db-to-sqlite~=1.1.1