Joins
If you aren't selecting columns from both tables or the two tables don't have a defined relationship, you can still use the .join()
method on a table to join it with another table and get
extra data related to our query. The join()
takes the table object you want to join in as the first argument and a condition that
indicates how the tables are related to the second argument. Finally,
you use the .select_from()
method on the select statement to wrap the join
clause. For example, in the video, Jason executed the following code to join the census
table to the state_fact
table such that the state
column of the census
table corresponded to the name
column of the state_fact
table.
stmt = stmt.select_from(
census.join(
state_fact, census.columns.state ==
state_fact.columns.name)
This exercise is part of the course
Introduction to Databases in Python
Exercise instructions
- Build a statement to select ALL the columns from the
census
andstate_fact
tables. To select ALL the columns from two tablesemployees
andsales
, for example, you would usestmt = select([employees, sales])
. - Append a
select_from
tostmt
to join thecensus
table to thestate_fact
table by thestate
column incensus
and thename
column in thestate_fact
table. - Execute the statement to get the first result and save it as
result
. This code is already written. - Submit the answer to loop over the keys of the result object, and print the key and value for each!
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
# Build a statement to select the census and state_fact tables: stmt
stmt = select([____, ____])
# Add a select_from clause that wraps a join for the census and state_fact
# tables where the census state column and state_fact name column match
stmt_join = stmt.select_from(
____(____, census.columns.____ == state_fact.columns.____))
# Execute the statement and get the first result: result
result = connection.execute(stmt_join).first()
# Loop over the keys in the result object and print the key and value
for key in result.keys():
print(key, getattr(result, key))