0

I have this set of code which queries the db.

get_user_details = (
                self.db_session.query(
                    users_table.firstname,
                    users_table.lastname,
                    users_table.profile_pic,
                    users_table.email,
                )
                .filter(users_table.userid == user_payload["userid"])
                .first()
            )

when I print get_user_details I am getting a tuple with values. But I need a to get a JSON output with key and value pair. key as the column name.

How can I achieve this?

Currently I am using 

def alchemyencoder(obj):
    """JSON encoder function for SQLAlchemy special classes."""
    if isinstance(obj, datetime.date):
        return obj.isoformat()
    elif isinstance(obj, decimal.Decimal):
        return float(obj)
    elif isinstance(obj, datetime.time):
        return obj.strftime("%H:%M")

to serialize the code.

SuperShoot
  • 9,880
  • 2
  • 38
  • 55
Jithin Zacharia
  • 341
  • 1
  • 3
  • 17
  • Does what you are doing work? – SuperShoot Aug 30 '19 at 06:43
  • It does work, but it returns a tuple instead of a json values with column names. I could just do `json.dumps(get_user_details, default=alchemyencoder)` – Jithin Zacharia Aug 30 '19 at 06:49
  • Well if that works that seems like a perfectly fine solution. – SuperShoot Aug 30 '19 at 06:52
  • No, my requirement is to get a JSON output with key as column names and values as column values. This method only returns value as a tuple like this `( "Jithin", "Za", "sdfsdf", "Jithin@demo.com" )` instead i want like this `{"firstname":"sdfsdf","lastname":"sdfsdf",email:"sdfsdf@dfsdf.com"}` – Jithin Zacharia Aug 30 '19 at 06:55
  • 1
    Pass `get_user_details._asdict()` to `json.dumps()` with your encoder. The `tuple` result is actally a `namedtuple` which has an `_asdict()` method: https://docs.python.org/3.7/library/collections.html#collections.somenamedtuple._asdict – SuperShoot Aug 30 '19 at 07:04
  • 1
    @supershoot It's a [`KeyedTuple`](https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.util.KeyedTuple) to be precise, but it acts the same. – Ilja Everilä Aug 30 '19 at 08:10
  • @IljaEverilä I assumed it inherited from namedtuple but indeed it just [mirrors the api](https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/util/_collections.py#L99). – SuperShoot Aug 30 '19 at 08:16
  • But what happens if the result is a array of tuples – Jithin Zacharia Aug 30 '19 at 08:58
  • Well that's a different question, but not that different: https://stackoverflow.com/q/19110164/6560549. What if the query returns ORM class instances? https://stackoverflow.com/questions/5022066/how-to-serialize-sqlalchemy-result-to-json – SuperShoot Aug 30 '19 at 09:44

0 Answers0