Using Python Dataclasses to output raw JSON

Question

I am using Python Dataclasses to aid in outputting my SQL Alchemy data models as JSON as outlined here. The problem is one of my class attributes is a string of serialized unstructured JSON data stored in the database and I would like to be able to output that as raw JSON. The problem is I cannot seem to find the correct data type to allow this. I have tried dict, json and object but all of these still result in the JSON field being populated with a delimited string as in the area marked "current output". Is there a way to do this with dataclasses?

class Event(db.Model):
    id: int
    data: ???

    __tablename__ = "data"
    id = db.Column('id', db.Integer, primary_key=True, autoincrement=True)
    data = db.Column('data', db.Text, nullable=True)

Current Output:

   {
     "id": 1,
     "data": "{\"data\": \"this is data\"}"
   }

Expected Output:

   {
     "id": 1,
     "data": {"data": "this is data"}
   }

score 1 · Accepted Answer · answered Mar 25 '21 at 23:31

1

Solved! For those finding this in the future if you are dead set on converting strings to JSON at this point my answer will not be useful but setting the datatype used by SQL alchemy to JSON resolved the problem as SQL Alchemy handles the string to JSON conversion when loading the object from the DB.

class Event(db.Model):
    id: int
    data: json

    __tablename__ = "data"
    id = db.Column('id', db.Integer, primary_key=True, autoincrement=True)
    data = db.Column('data', db.JSON, nullable=True)

answered Mar 25 '21 at 23:31

Astrid

31
4

I've never seen type annotations in sqlalchemy models before. As far as I know those shouldn't do anything, static type checkers use https://pypi.org/project/sqlalchemy-stubs/ to figure out the correct type, given the `Column`'s definition. Declaring types at another point might be misleading, since it's not supposed to be used at all. – Arne Mar 28 '21 at 18:41
Are you referring the the dataclasses feature introduced in Python 3.7 or the use of the db.JSON datatype for column definitions? regarding the column definition both db.Text and db.JSON are stored in the database the same way as a text blog depending on your db engine of choice. Changing to db.JSON seems to be required to load the JSON from the db without the contents being delimited at least while using MSSQL like I am. – Astrid Mar 29 '21 at 19:21
Yes, I don't mean the type definition in the Column constructor, I mean `id: int` and `data: json`. I might be wrong, but I'm pretty sure they don't do anything a static code analyzer can use, and are misleading for humans, since the type written in the Column is the actual source of truth. – Arne Mar 29 '21 at 21:43

Using Python Dataclasses to output raw JSON

1 Answers1