SQL Alchemy ORM returns a single column, how to avoid normal message processing

Question

I use SQL Alchemy ORM and I find when I return a single column, I get the following results:

[(result,), (result_2,)] # etc... 

With this set, I believe that I should do this often:

 results = [r[0] for r in results] # So that I just have a list of result values 

This is not that bad, because my result sets are usually small, but if it weren’t, it could add significant overhead. Most importantly, I feel this clutters the source, and skipping this step is a fairly common mistake that I encounter.

Is there any way to avoid this extra step?

Relatively aside: this behavior of the form seems inconvenient in this case, but in another case, when my result set was, [(id, value)], it ends as follows:

 [(result_1_id, result_1_val), (result_2_id, result_2_val)] 

Then I can just do:

 results = dict(results) # so I have a map of id to value 

This has the advantage of making sense a useful step after returning the results.

Is this really a problem or am I just nitpick, and post-processing after getting a result set makes sense for both cases? I am sure that we can think of some other common post-processing operations to make the result set more useful in the application code. Is there high performance and convenient solutions in all directions or unsatisfactory post-processing and is simply required for different applications?

When my application can really use the objects returned by SQL Alchemy ORM, it seems very useful, but in cases where I can not or cannot, not so much. Is this just a common ORM problem in general? Am I better off not using the ORM layer in such cases?

I suppose I should show an example of the real orm queries I'm talking about:

 session.query(OrmObj.column_name).all() 

or

 session.query(OrmObj.id_column_name, OrmObj.value_column_name).all() 

Of course, in a real query there are usually some filters, etc.

Answer 1

Python zip combined with the inline extension operator is a pretty convenient solution for this:

 >>> results = [('result',), ('result_2',), ('result_3',)] >>> zip(*results) [('result', 'result_2', 'result_3')] 

Then you need to specify the index [0] only once. For such a short list, your understanding is faster:

 >>> timeit('result = zip(*[("result",), ("result_2",), ("result_3",)])', number=10000) 0.010490894317626953 >>> timeit('result = [ result[0] for result in [("result",), ("result_2",), ("result_3",)] ]', number=10000) 0.0028390884399414062 

However, for longer lists, zip should be faster:

 >>> timeit('result = zip(*[(1,)]*100)', number=10000) 0.049577951431274414 >>> timeit('result = [ result[0] for result in [(1,)]*100 ]', number=10000) 0.11178708076477051 

So, it is up to you to determine what is best for your situation.

Answer 2

One way to reduce clutter in the source is to repeat it like this:

 results = [r for (r, ) in results] 

Although this solution is one character longer than when using the [] operator, I think it is easier on the eyes.

For even less interference, remove the brackets. This complicates the reading of the code, noticing that you are actually processing tuples:

 results = [r for r, in results] 

Answer 3

I also struggled with this until I understood it just like any other request:

 for result in results: print result.column_name 

Answer 4

I found the following more readable , also includes an answer for dict (in Python 2.7):

 d = {id_: name for id_, name in session.query(Customer.id, Customer.name).all()} l = [r.id for r in session.query(Customer).all()] 

For a single value borrowing from another answer:

 l = [name for (name, ) in session.query(Customer.name).all()] 

Compare with the list-specific zip built in:

 l = list(zip(*session.query(Customer.id).all())[0]) 

which in my time only provides a 4% speed improvement.

Answer 5

My solution looks like this :)

 def column(self): for column, *_ in Model.query.with_entities(Model.column).all(): yield column 

NOTE: py3 only.