2

I have two classes

class PersonName:
   Salutation: String
   FirstName : String
   LastName : String

and

class Person:
  id : Integer
  Name : PersonName   
  ...other props...

Rather than generating a table for PersonName I would like SQLAlchemy to simply use a string column and serialize the instance to JSON (and deserialize it when it is fetched). I don't need deep queries or anything, just basic serialization.

Is this possible?

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
George Mauer
  • 117,483
  • 131
  • 382
  • 612
  • What DBMS are you using? Some support JSON natively. Nothing wrong with your current approach, though. – Ilja Everilä Oct 20 '18 at 07:43
  • 1
    See https://stackoverflow.com/questions/33160762/how-to-use-marshmallow-to-serialize-a-custom-sqlalchemy-field – Jim Stewart Oct 20 '18 at 11:56
  • @IljaEverilä in my case I'm using sql server, as I said in the question I want to serialize to a string (so lets say an `NVarchar` column). Sql server doesn't have an actual json type, but if you store json inside of a column it has querying extensions for it. Either way, this is *not* something I need database support for, I'm never going to be querying on firstnames or salutations. This is something I just need ORM support for – George Mauer Oct 20 '18 at 14:20
  • 1
    Then I think this will be of help to you: https://docs.sqlalchemy.org/en/latest/core/custom_types.html#marshal-json-strings – Ilja Everilä Oct 20 '18 at 15:42

1 Answers1

0

Here is a nice solution.

Define the class like this:

class Person(db.Model):
    __tablename__ = 'persons'
    id = db.Column(db.Integer, primary_key=True)
    fancy_name = db.Column(JsonEncodedDict)

And use it like this: 

person = Person(fancy_name={'Salutation': 'Mr.', 'FirstName': 'Sergey', 'FullMiddleName': 'Vladimirovich'})

To make it work, you need to define a custom JSON-type decorator somewhere.

import json
from sqlalchemy.ext import mutable

db = SQLAlchemy()

class JsonEncodedDict(db.TypeDecorator):
    """Enables JSON storage by encoding and decoding on the fly."""
    impl = db.Text

    def process_bind_param(self, value, dialect):
        if value is None:
            return '{}'
        else:
            return json.dumps(value)

    def process_result_value(self, value, dialect):
        if value is None:
            return {}
        else:
            return json.loads(value)


mutable.MutableDict.associate_with(JsonEncodedDict)
SergeyR
  • 468
  • 5
  • 10