SQL relationships

Introduction to Databases in Python

Jason Myers

Co-Author of Essential SQLAlchemy and Software Engineer

Relationships

Allow us to avoid duplicate data
Make it easy to change things in one place
Useful to break out information from a table we don't need very often

Relationships

Automatic joins

stmt = select([census.columns.pop2008, 
        state_fact.columns.abbreviation])

results = connection.execute(stmt).fetchall()
print(results)

[(95012, u'IL'),
 (95012, u'NJ'),
 (95012, u'ND'),
 (95012, u'OR'),
 (95012, u'DC'),
 (95012, u'WI'),
 ...

Join

Accepts a Table and an optional expression that explains how the two tables are related
The expression is not needed if the relationship is predefined and available via reflection
Comes immediately after the select() clause and prior to any where(), order_by() or group_by() clauses

select_from()

Used to replace the default, derived FROM clause with a join
Wraps the join() clause

select_from() example

stmt = select([func.sum(census.columns.pop2000)])

stmt = stmt.select_from(census.join(state_fact))

stmt = stmt.where(state_fact.columns.circuit_court == '10')

result = connection.execute(stmt).scalar()
print(result)

14945252

Joining tables without predefined relationship

Join accepts a Table and an optional expression that explains how the two tables are related
Will only join on data that match between the two columns
Avoid joining on columns of different types

select_from() example

stmt = select([func.sum(census.columns.pop2000)])

stmt = stmt.select_from(
        census.join(state_fact, census.columns.state
        == state_fact.columns.name))

stmt = stmt.where(
        state_fact.columns.census_division_name == 
        'East South Central')

result = connection.execute(stmt).scalar()
print(result)

16982311

Let's practice!

Introduction to Databases in Python