JOIN result as dict

[Joasia Truszkowska]

Hi!

There is any option to select JOIN result as dict?
For example I have:

table1

 t1c1| t1c2
-----+--------
   1 | one
   2 | two
   3 | three

table2

 t2c1 | t2c2 | t2c3
-----+-------+-------
   11 | qwe  | 1
   22 | rty  | 2
   33 | zxcvb| 1

where
t2c3 from table2 is a foreign key to table1.

I want to show JOIN result

q = db.session.query(table1.t1c2, table2).join(table2).all()

as one dict:

[{
  't1c2': 'one',
  't2c2': 'qwe',
  't2c1': '11',
  't2c3': '1'
},

{
  't1c2': 'two',
  't2c2': 'rty',
  't2c1': '22',
  't2c3': '2'
},
...]

But above query returns me result as tuple. I tried covert this result using asdict() method, but it can't make from this one dictionary. I can't create any query to resolve my problem.

I use latest SqlAlchemy with Postgres 9.5.

Thanks!

[Jonathan Vanasco]

Your query's underlying sql returns multiple rows.  You can cast those results into a dict in Python.

[Joasia]

q.all() returns me a result in format: 

[('one', {'t2c2': 'qwe',

  't2c1': '11',


  't2c3': '1'}),
('two', {'t2c2': 'rty',

  't2c1': '22',


  't2c3': '2'}),
..]

The result of

q.all()._asdict()

is

[{
  't1c2': 'one',
  'table2': {


    't2c2': 'qwe',
    't2c1': '11',
    't2c3': '1'
  }


},

{
  't1c2': 'two',
  'table2': {


    't2c2': 'rty',
    't2c1': '22',
    't2c3': '2'
  }
},
...]

Above solutions aren't result whose I excepted.

I try to avoid any casting in Python.

Is it possible?

[Jonathan Vanasco]

I'm sorry, I totally misunderstood your question. I thought you were trying to get all rows as a unified dict.  Your result clarified things.

It looks like you're using the ORM (and not core), right?

I don't think it is entirely possible to do what you want.  Several people have asked similar questions in the past...  you essentially want SqlAlchemy to return somewhat raw data.

The problem isn't with the `join`, it's with the `query`.  

This bit:

    query(table1.t1c2, table2)

Is instructing SqlAlchemy to return a tuple that contains those two discrete "objects" - the first being a column of `table1`, the second being a instance of `table2` (or a row).  The ORM is then mapping table2 onto an object.

To *somewhat* achieve your goal and avoid the object creation, you can explicitly enumerate your query:

    r = db.session.query(Table1.t1c2, Table2.t2c1, Table2.t2c2, Table2.t2c3).join(Table2).all()

That will return an iterator of 3 element tuples in that order:

    [(u'one', 11, u'qwe', 1),

     (u'two', 22, u'rty', 2),

     (u'one', 33, u'zxcvb', 1)

     ]

You can cast each one into a dict individually:

    for row in r:

       print row._as_dict()

will generate:

    {'t1c2': u'one', 't2c3': 1, 't2c2': u'qwe', 't2c1': 11}

    {'t1c2': u'two', 't2c3': 2, 't2c2': u'rty', 't2c1': 22}

    {'t1c2': u'one', 't2c3': 1, 't2c2': u'zxcvb', 't2c1': 33}

That brings me to this point -- can you share a self-contained example of your code?

I can't figure out how you're getting `q.all()._asdict()` to work.  It shouldn't work, because the result should be an iterable/list that does not have that method.

[Joasia]

q.all()._asdict()

It does not work. It is a kind of a mental shortcut (sorry for that!). It is only possible in (like you said):

 for r in q.all():
       r._as_dict()

Thank you for your reply! I had hope that I don't have to do any casting or explicity query enumerate.