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!

Basic JSON1 support should be coming in 1.3 for SQLite dialect. Your example will work as is with it. — Ilja Everilä
– Ilja Everilä, Commented Jul 16, 2018 at 18:03

Shawn · Accepted Answer · 2018-07-16 04:25:41Z

5

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

answered Jul 16, 2018 at 4:25

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related