CODEWITHSUNDEEP
pythonsqlalchemymarshmallow
Laser
Laser

Reputation: 6960

How to compare sql vs json in python

I have the following problem. I have a class User simplified example:

class User:
    def __init__(self, name, lastname, status, id=None):
        self.id = id
        self.name = name
        self.lastname = lastname
        self.status = status
    def set_status(self,status)
         # call to the api to change status

    def get_data_from_db_by_id(self)
         # select data from db where id = self.id
    def __eq__(self, other):
        if not isinstance(other, User):
            return NotImplemented
        return (self.id, self.name, self.lastname, self.status) == \
           (other.id, other.name, other.lastname, other.status)

And I have a database structure like:

id, name, lastname, status 
1, Alex, Brown, free

And json response from an API:

{
    "id": 1,
    "name": "Alex",
    "lastname": "Brown",
    "status": "Sleeping"
}

My question is:

What the best way to compare json vs sql responses?
What for? - it's only for testing purposes - I have to check that API has changed the DB correctly.

How can I deserialize Json and DB resul to the same class? Is there any common /best practices ?

For now, I'm trying to use marshmallow for json and sqlalchemy for DB, but have no luck with it.

Upvotes: 2

Views: 1551

Answers (2)

Chris Modzelewski
Chris Modzelewski

Reputation: 1648

If you are using SQLAlchemy for the database, then I would recommend using SQLAthanor (full disclosure: I am the library’s author).

SQLAthanor is a serialization and de-serialization library for SQLAlchemy that lets you configure robust rules for how to serialize / de-serialize your model instances to JSON. One way of checking your instance and JSON for equivalence is to execute the following logic in your Python code:

First, serialize your DB instance to JSON. Using SQLAthanor you can do that as simply as:

instance_as_json = my_instance.dump_to_json()

This will take your instance and dump all of its attributes to a JSON string. If you want more fine-grained control over which model attributes end up on your JSON, you can also use my_instance.to_json() which respects the configuration rules applied to your model.

Once you have your serialized JSON string, you can use the Validator-Collection to convert your JSON strings to dicts, and then check if your instance dict (from your instance JSON string) is equivalent to the JSON from the API (full disclosure: I’m also the author of the Validator-Collection library):

from validator_collection import checkers, validators

api_json_as_dict = validators.dict(api_json_as_string)
instance_json_as_dict = validators.dict(instance_as_json)

are_equivalent = checkers.are_dicts_equivalent(instance_json_as_dict, api_json_as_dict)

Depending on your specific situation and objectives, you can construct even more elaborate checks and validations as well, using SQLAthanor’s rich serialization and deserialization options.

Here are some links that you might find helpful:

Hope this helps!

Upvotes: 1

Booboo
Booboo

Reputation: 44283

Convert the database row to a dictionary:

    def row2dict(row):
        d = {}
        for column in row.__table__.columns:
            d[column.name] = str(getattr(row, column.name))

        return d

Then convert json string to a dictionary:

    d2 = json.loads(json_response)

And finally compare:

    d2 == d

Upvotes: 1

Related Questions