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
Here's how you would do it using the Expression API
http://docs.sqlalchemy.org/en/latest/core/expression_api.html
Define your table, for example:
table1 = table('table1',
column('name', String())
)
table2 = table('table2',
column('ip', String()),
column('info', String()),
column('hostname', String()),
)
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)
Execute your query, like:
db.execute(my_query)
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)
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!
