How to create a SQLite Table with a JSON column using SQLAlchemy?

Question

According to this answer SQLite supports JSON data since version 3.9. I use version 3.24 in combination with SQLALchemy (1.2.8) and Python 3.6, but I cannot create any tables containing JSON columns.

What am I missing or doing wrong? A minimal (not) working example is given below:

import sqlalchemy as sa
import os
import tempfile

metadata = sa.MetaData()

foo = sa.Table(
    'foo',
    metadata,
    sa.Column('bar', sa.JSON)
)

tmp_dir = tempfile.mkdtemp()
dbname = os.path.join(tmp_dir, 'foo.db')

engine = sa.create_engine('sqlite:////' + dbname)

metadata.bind = engine

metadata.create_all()

This fails giving the following error:

sqlalchemy.exc.CompileError: (in table 'foo', column 'bar'): Compiler <sqlalchemy.dialects.sqlite.base.SQLiteTypeCompiler object at 0x7f1eae1dab70> can't render element of type <class 'sqlalchemy.sql.sqltypes.JSON'>

Thanks!

Answer 1

Use a TEXT column. Sqlite has a JSON extension with some functions for working with JSON data, but no dedicated JSON type.