Search code examples
pythonsqlalchemypymysql

distinguishing json column of RowProxy in SQLAlchemy?

For example, when I execute following code, resulting data type is str:

result = engine.execute('''
  SELECT CAST('{"foo": "bar"}' as JSON) as `json`
''')
row = result.fetchone()
json = row[0]
type(json)

An json column value having type of str is not so much meta-programming friendly.

Question

Is there any way to fetch information from the result (or, an instance of ResultProxy) what each column's type was?

env

  • MySQL: 8.0.11
  • SQLAlchemy: 1.3.0
  • pymysql: 0.9.3
Solution

  • You can at least achieve it by explicitly telling SQLAlchemy that the result is JSON:

    from sqlalchemy.types import JSON

stmt = text('''SELECT CAST('{"foo": "bar"}' as JSON) as `json`''')
stmt = stmt.columns(json=JSON)

row = engine.execute(stmt).fetchone()
type(row.json)