Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

convert sqlalchemy query result to a list of dicts

I want to have the result of my query converted to a list of dicts like this :

result_dict = [{'category': 'failure', 'week': '1209', 'stat': 'tdc_ok', 'severityDue': '2_critic'}, {'category': 'failure', 'week': '1210', 'stat': 'tdc_nok', 'severityDue': '2_critic'}]

But instead I get it as a dict, thus with repeated keys:

result_dict = {'category': 'failure', 'week': '1209', 'stat': 'tdc_ok', 'severityDue': '2_critic', 'category': 'failure', 'week': '1210', 'stat': 'tdc_nok', 'severityDue': '2_critic'}

I get this result by doing this :

for u in my_query.all():
     result_dict = u.__dict__

How can I convert sqlAlchemy query result to a list of dicts (each row would be a dict) ?

Help please

like image 469
salamey Avatar asked Jul 18 '13 08:07

salamey


People also ask

What does SQLAlchemy query all return?

All SELECT statements generated by SQLAlchemy ORM are constructed by Query object. It provides a generative interface, hence successive calls return a new Query object, a copy of the former with additional criteria and options associated with it.

How do I get column names in SQLAlchemy?

To access the column names we can use the method keys() on the result. It returns a list of column names. Since, we queried only three columns, we can view the same columns on the output as well.

What is all () in SQLAlchemy?

method sqlalchemy.orm.Query. all() Return the results represented by this Query as a list. This results in an execution of the underlying SQL statement. The Query object, when asked to return either a sequence or iterator that consists of full ORM-mapped entities, will deduplicate entries based on primary key.

What is _sa_instance_state?

_sa_instance_state is a non-database-persisted value used by SQLAlchemy internally (it refers to the InstanceState for the instance. While not directly relevant to this section, if we want to get at it, we should use the inspect() function to access it).


2 Answers

Try

result_dict = [u.__dict__ for u in my_query.all()]

Besides what is the type of your result_dict before the for loop? Its behavior is rather strange.

like image 108
twil Avatar answered Oct 29 '22 03:10

twil


This works now

result_dict = [u._asdict() for u in my_query.all()]

The reason is that u is not actually a tuple but a KeyedTuple.

The correct answer on this thread also would be helpful

like image 38
Abhishek Jebaraj Avatar answered Oct 29 '22 05:10

Abhishek Jebaraj