ipython-sql: PostgreSQL JSON operators break %sql line magic

[duncandewhurst]

Using the -> or ->> operators in a query breaks the sql line magic. For example (notebook), running a cell containing %sql select data -> 'ocid' from data limit 1 results in the following error:

UsageError: unrecognized arguments: -> 'ocid' from data limit 1

The same issue affects get_ipython_sql_resultset_from_query.

This is a problem because the line magic is useful for controlling the execution of queries using Python for loops, if statements, functions etc. and for creating template queries and substituting in table names etc.

I guess this is an upstream problem with ipython-sql but I'm wondering if there is a workaround I'm missing, or if there's something we can do in kingfisher-colab.

catherinedevlin/ipython-sql#190 looks like the same issue.

For context, I came across this issue when updating the templated queries in the structure and format section of the data feedback template to use the new version of kingfisher-colab. In that case I was able to author a query that did away with the need for the templates, but in other cases it could be blocker.

[Bjwebb]

The workaround is to remove the spaces either side of the operator, e.g.: data->'ocid' works

[duncandewhurst]

Ha, so simple! Can we document that somewhere in the kingfisher-colab docs?