In this chapter, we will learn how to use Joins in SQLAlchemy.
Effect of joining is achieved by just placing two tables in either the columns clause or the where clause of the select() construct. Now we use the join() and outerjoin() methods.
The join() method returns a join object from one table object to another.
join(right, onclause = None, isouter = False, full = False)
The functions of the parameters mentioned in the above code are as follows −
right − the right side of the join; this is any Table object
onclause − a SQL expression representing the ON clause of the join. If left at None, it attempts to join the two tables based on a foreign key relationship
isouter − if True, renders a LEFT OUTER JOIN, instead of JOIN
full − if True, renders a FULL OUTER JOIN, instead of LEFT OUTER JOIN
For example, following use of join() method will automatically result in join based on the foreign key.
This is equivalent to following SQL expression −
students JOIN addresses ON students.id = addresses.st_id
You can explicitly mention joining criteria as follows −
j = students.join(addresses, students.c.id == addresses.c.st_id)
If we now build the below select construct using this join as −
stmt = select([students]).select_from(j)
This will result in following SQL expression −
SELECT students.id, students.name, students.lastname FROM students JOIN addresses ON students.id = addresses.st_id
If this statement is executed using the connection representing engine, data belonging to selected columns will be displayed. The complete code is as follows −
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey engine = create_engine('sqlite:///college.db', echo = True) meta = MetaData() conn = engine.connect() students = Table( 'students', meta, Column('id', Integer, primary_key = True), Column('name', String), Column('lastname', String), ) addresses = Table( 'addresses', meta, Column('id', Integer, primary_key = True), Column('st_id', Integer,ForeignKey('students.id')), Column('postal_add', String), Column('email_add', String) ) from sqlalchemy import join from sqlalchemy.sql import select j = students.join(addresses, students.c.id == addresses.c.st_id) stmt = select([students]).select_from(j) result = conn.execute(stmt) result.fetchall()
The following is the output of the above code −
[ (1, 'Ravi', 'Kapoor'), (1, 'Ravi', 'Kapoor'), (3, 'Komal', 'Bhandari'), (5, 'Priya', 'Rajhans'), (2, 'Rajiv', 'Khanna') ]