Convert PostgreSQL to SqlAlchemy query

Question

I have a simple query in PostgreSQL which I need to convert to SQLAlchemy.

SELECT table1.name,table2.ip,table2.info 
FROM table1 LEFT OUTER JOIN table2 
ON(table1.name=table2.hostname) 
GROUP BY table1.name;

I have tried using this with no success:

session.query(table1.name
 ,table2.ip
 ,table2.info).distinct().join(table2).group_by(table1.name)

Can someone please help me with this?

Thanks in advance Ishwar

Answer 1

Here's how you would do it using the Expression API

http://docs.sqlalchemy.org/en/latest/core/expression_api.html

1. Define your table, for example:

   table1 = table('table1',
    column('name', String())
   )
   table2 = table('table2',
    column('ip', String()),
    column('info', String()),
    column('hostname', String()),
   )
   

2. Compose your query, like:

   my_query = select([table1.c.name,
    table2.c.ip,
    table2.c.info]
    ).select_from(
    table1.outerjoin(table2, table1.c.name == table2.c.hostname)
    ).group_by(table1.c.name)
   

3. Execute your query, like:

   db.execute(my_query)
   

• Ishwar: Though it doesn't answer the question exactly, I got a direction to move ahead. Thanks

Answer 2

It's not clear from your answer what the problem is, but my shoot at this query would be:

session.query(table1.name
 ,table2.ip
 ,table2.info)\
 .outerjoin(table2)\
 .group_by(table1.name)

Answer 3

I figured out the answer. I was actually missing the ON condition in query:

session.query(table1.name ,table2.ip ,table2.info ).distinct().outerjoin(table2 , table1.name=table2.hostname ).group_by(table1.name)

Thanks for help!