How to intersect two raw SQL queries using SQLAlchemy?

Question

I have two raw Postgresql statements.

q1 = "SELECT name FROM table_a"
q2 = "SELECT name FROM table_b"

How can I query the intersection of them using SQLAlchemy.

Alexander Ejbekov · Accepted Answer · 2016-02-09 22:50:54Z

1

This is more or less what you could do:

from sqlalchemy import create_engine, text
engine = create_engine('postgresql://user:password@localhost/somedb')
q1 = "SELECT name FROM table_a intersect SELECT name FROM table_b"
q1res = engine.execute(text(q1)).fetchall()
# or
q2res = engine.execute(text(q1)).fetchone()

Depending on your needs. Ditch fetchone and fetchall if you intend to make inserts or updates.

edited Feb 9, 2016 at 22:50

answered Feb 9, 2016 at 22:42

Alexander Ejbekov

6,0501 gold badge28 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

George L Over a year ago

I'd like to be able to generate the SQL statement that includes the INTERSECT inside the query, and execute it that way, rather than fetching all the results from both and having python deal with intersects.

Alexander Ejbekov Over a year ago

Well since you are performing raw queries, simply add an intersection. I've updated the answer. Also you could have a look at sqlalchemy's orm which is extremely powerful: here

Collectives™ on Stack Overflow

How to intersect two raw SQL queries using SQLAlchemy?

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related