Convert a sqlalchemy query to a dictionary - Python

Asked

Viewed 61 times

0

I have this consultation made with SQLalchemy ORM, where table_schema contains an example of sqlalchemy column

nq = session.query(Base.table_schema).filter(Base.tablename == 'stores').scalar()

The result returns me this:

'{"id": Column("id", INTEGER(), table=<None>, primary_key=True, nullable=False, server_default=DefaultClause(<sqlalchemy.sql.elements.TextClause object at 0x07F7B190>, for_update=False)), "nk_store": Column("nk_store", VARCHAR(length=40), table=<None>), "store_code": Column("store_code", VARCHAR(length=40), table=<None>), "store_level": Column("store_level", VARCHAR(length=40), table=<None>), "ownership": Column("ownership", VARCHAR(length=40), table=<None>), "ownership_label": Column("ownership_label", VARCHAR(length=40), table=<None>),  "store_supplier": Column("store_supplier", VARCHAR(length=40), table=<None>),"n1_supplier": Column("n1_supplier", VARCHAR(length=40), table=<None>), "n2_supplier": Column("n2_supplier", VARCHAR(length=40), table=<None>), "status": Column("status", VARCHAR(length=40), table=<None>), "__tablename__": "stores"}'

I want to turn this query (which is a string) into a dict, thus:

{"id": Column("id", INTEGER(), table=<None>, primary_key=True, nullable=False, server_default=DefaultClause(<sqlalchemy.sql.elements.TextClause object at 0x07F7B190>, for_update=False)),
 "nk_store": Column("nk_store", VARCHAR(length=40), table=<None>),
 "store_code": Column("store_code", VARCHAR(length=40), table=<None>),
 "store_level": Column("store_level", VARCHAR(length=40), table=<None>),
 "ownership": Column("ownership", VARCHAR(length=40), table=<None>),
 "ownership_label": Column("ownership_label", VARCHAR(length=40), table=<None>),
 "store_supplier": Column("store_supplier", VARCHAR(length=40), table=<None>),
 "n1_supplier": Column("n1_supplier", VARCHAR(length=40), table=<None>),
 "n2_supplier": Column("n2_supplier", VARCHAR(length=40), table=<None>),
 "status": Column("status", VARCHAR(length=40), table=<None>),
 "__tablename__": "stores"}

I try to use json_dumps, but it doesn’t work, so I try ast, but I have this mistake:

import ast

result = ast.literal_eval(nq)
assert type(result) is dict

SyntaxError: invalid syntax

I think this error happens because when python tries to turn nq into one dict, it does not recognize the values as a string, but I’m still not sure. Someone can help?

database python-3.x string dictionary sqlalchemy

  • If you are using flask you can use the marshal to serialize your query result into a schema you prefer, if you are not using flask you can use dataclass or pydantic itself.

1 answer

0

Browser other questions tagged database python-3.x string dictionary sqlalchemy

You are not signed in. Login or sign up in order to post.