sqlalchemy double kabooms: AttributeError: 'SQLiteTypeCompiler' object has no attribute 'visit_JSON'

[yarikoptic]

Initially tried to follow README.md on python2, kaboomed, tried python3 with the same effect:

gory details

(git)hopa:~/proj/bids/pybids[bf-extension]
$> python -c 'from bids.layout import BIDSLayout; layout = BIDSLayout("bids/tests/data/ds000117/")'
Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/visitors.py", line 86, in _compiler_dispatch
    meth = getter(visitor)
AttributeError: 'SQLiteTypeCompiler' object has no attribute 'visit_JSON'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 2790, in visit_create_table
    create_column, first_pk=column.primary_key and not first_pk
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 341, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/visitors.py", line 90, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 2822, in visit_create_column
    text = self.get_column_specification(column, first_pk=first_pk)
  File "/usr/lib/python3/dist-packages/sqlalchemy/dialects/sqlite/base.py", line 907, in get_column_specification
    column.type, type_expression=column
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 391, in process
    return type_._compiler_dispatch(self, **kw)
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/visitors.py", line 88, in _compiler_dispatch
    raise exc.UnsupportedCompilationError(visitor, cls)
sqlalchemy.exc.UnsupportedCompilationError: Compiler <sqlalchemy.dialects.sqlite.base.SQLiteTypeCompiler object at 0x7f7259e33d68> can't render element of type <class 'sqlalchemy.sql.sqltypes.JSON'>

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "/home/yoh/proj/bids/pybids/bids/layout/layout.py", line 197, in __init__
    index_dataset = self._init_db(database_file, reset_database)
  File "/home/yoh/proj/bids/pybids/bids/layout/layout.py", line 296, in _init_db
    Base.metadata.create_all(engine)
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/schema.py", line 4200, in create_all
    ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 2011, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1599, in _run_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/visitors.py", line 130, in traverse_single
    return meth(obj, **kw)
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/ddl.py", line 779, in visit_metadata
    _is_metadata_operation=True,
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/visitors.py", line 130, in traverse_single
    return meth(obj, **kw)
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/ddl.py", line 824, in visit_table
    include_foreign_key_constraints,
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 980, in execute
    return meth(self, multiparams, params)
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1035, in _execute_ddl
    else None,
  File "<string>", line 1, in <lambda>
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/elements.py", line 448, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/ddl.py", line 29, in _compiler
    return dialect.ddl_compiler(dialect, self, **kw)
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 310, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 341, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/visitors.py", line 90, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 2802, in visit_create_table
    % (table.description, column.name, ce.args[0])
  File "/usr/lib/python3/dist-packages/sqlalchemy/util/compat.py", line 296, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/lib/python3/dist-packages/sqlalchemy/util/compat.py", line 276, in reraise
    raise value.with_traceback(tb)
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 2790, in visit_create_table
    create_column, first_pk=column.primary_key and not first_pk
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 341, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/visitors.py", line 90, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 2822, in visit_create_column
    text = self.get_column_specification(column, first_pk=first_pk)
  File "/usr/lib/python3/dist-packages/sqlalchemy/dialects/sqlite/base.py", line 907, in get_column_specification
    column.type, type_expression=column
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/compiler.py", line 391, in process
    return type_._compiler_dispatch(self, **kw)
  File "/usr/lib/python3/dist-packages/sqlalchemy/sql/visitors.py", line 88, in _compiler_dispatch
    raise exc.UnsupportedCompilationError(visitor, cls)
sqlalchemy.exc.CompileError: (in table 'configs', column '_default_path_patterns'): Compiler <sqlalchemy.dialects.sqlite.base.SQLiteTypeCompiler object at 0x7f7259e33d68> can't render element of type <class 'sqlalchemy.sql.sqltypes.JSON'>
(dev3) 1 11570 ->1.....................................:Fri 10 May 2019 09:02:15 AM EDT:.
(git)hopa:~/proj/bids/pybids[bf-extension]
$> welp sqlalchemy
PATH       : /usr/lib/python3/dist-packages/sqlalchemy/__init__.py
SRC PATH   : /usr/lib/python3/dist-packages/sqlalchemy/__init__.py
VERSION    : 1.2.18
__version__: '1.2.18'
PACKAGE    : python3-sqlalchemy
ii  python3-sqlalchemy 1.2.18+ds1-1 all          SQL toolkit and Object Relational Mapper for Python 3
python3-sqlalchemy:
  Installed: 1.2.18+ds1-1
  Candidate: 1.2.18+ds1-1
  Version table:
     1.3.1+ds1-1 300
        300 http://http.debian.net/debian experimental/main amd64 Packages
        300 http://http.debian.net/debian experimental/main i386 Packages
 *** 1.2.18+ds1-1 900
        900 http://http.debian.net/debian buster/main amd64 Packages
        900 http://http.debian.net/debian buster/main i386 Packages
        600 http://http.debian.net/debian sid/main amd64 Packages
        600 http://http.debian.net/debian sid/main i386 Packages
        100 /var/lib/dpkg/status
(dev3) 1 11571.....................................:Fri 10 May 2019 09:02:21 AM EDT:.
(git)hopa:~/proj/bids/pybids[bf-extension]
$> git describe
0.7.1-256-g52c6a7f

[tyarkoni]

You probably need to upgrade SQLite; only more recent builds (~2015) handle JSON columns. We should probably document this somewhere. Suggestions?

[tyarkoni]

It also wouldn't be crazy to replace the JSON columns with text, I suppose. It's mostly a convenience to avoid having to explicitly serialize and deserialize.

Edit: I thought I was using JSON columns more widely, but it looks like it's only one field. Definitely not worth forcing everyone to upgrade SQLite just for that. Will patch this to use text.

Edit 2: Turns out I wasn't even making any use of the JSON features, so there was literally no reason not to use a String there. :p

[tyarkoni]

@yarikoptic try now?

[yarikoptic]

I think it is gone, thanks!