Issue 1:
Migrated issue, originally created by Sebastian Rittau (@srittau)

Please consider the following code:

from sqlalchemy import create_engine, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.types import Integer, JSON

DeclarativeBase = declarative_base()

class Foo(DeclarativeBase):

__tablename__ = "foo"

id = Column(Integer, primary_key=True)
json = Column(JSON)

engine = create_engine("sqlite:///")
engine.execute("CREATE TABLE foo(id INTEGER PRIMARY KEY, json JSON)")
engine.execute("INSERT INTO foo(id, json) VALUES(123, '\"bar\"')")
session = sessionmaker(bind=engine)()
session.query(Foo).first()

Running this with SQLAlchemy 1.2.5 and Python 3.6.5rc1, I get the following traceback:

Traceback (most recent call last):
File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/sql/type_api.py", line 471, in _cached_result_processor
return dialect._type_memos[self][coltype]
File "/usr/lib/python3.6/weakref.py", line 394, in __getitem__
return self.data[ref(key)]
KeyError: <weakref at 0x7f7466d6f688; to 'JSON' at 0x7f746a797588>

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/home/srittau/.PyCharm2017.3/config/scratches/scratch_13.py", line 22, in <module>
session.query(Foo).first()
File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2789, in first
ret = list(self[0:1])
File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2581, in __getitem__
return list(res)
File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2889, in __iter__
return self._execute_and_instances(context)
File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2912, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
return meth(self, multiparams, params)
File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
compiled_sql, distilled_params
File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1215, in _execute_context
result = context.get_result_proxy()
File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 1008, in get_result_proxy
return result.ResultProxy(self)
File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 649, in __init__
self._init_metadata()
File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 678, in _init_metadata
self._metadata = ResultMetaData(self, cursor_description)
File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 215, in __init__
num_ctx_cols, cols_are_ordered, textual_ordered)
File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 379, in _merge_cursor_description
in enumerate(result_columns)
File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 378, in <listcomp>
) for idx, (key, name, obj, type_)
File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 971, in get_result_processor
return type_._cached_result_processor(self.dialect, coltype)
File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/sql/type_api.py", line 477, in _cached_result_processor
d[coltype] = rp = d['impl'].result_processor(dialect, coltype)
File "/home/srittau/.virtualenvs/zc-api-3.6/lib/python3.6/site-packages/sqlalchemy/sql/sqltypes.py", line 2148, in result_processor
json_deserializer = dialect._json_deserializer or json.loads
AttributeError: 'SQLiteDialect_pysqlite' object has no attribute '_json_deserializer'

Line 2148 of sqlalchemy/sql/sqltypes.py reads:

json_deserializer = dialect._json_deserializer or json.loads

I suspect this is supposed to read

json_deserializer = dialect._json_deserializer if hasattr(dialect, "_json_deserializer") else json.loads

to support dialects without native JSON support.

Issue 2:
Migrated issue, originally created by Shengjing Zhu (@zhsj)

sqlalchemy.types.JSON only supports PostgreSQL and MySQL. But since SQLite 3.9, it has json support, https://sqlite.org/json1.html

I hope sqlalchemy can support json1 too.