3. Advanced SQLAlchemy Queries

1 Calculating Values in a Query

1.1 Math Operators

1

1.2 Calculating Difference

2

1.3 Case Statement

3

1.4 Case Example

4

1.5 Cast Statement

5

1.6 Percentage Example

6

1.7 Example : Determining the Overall Percentage of Females

Determining the Overall Percentage of Females

2 SQL Relationships

2.1 Relationshps

7 8

2.2 Automatic Joins (with an Established Relationship)

If you have two tables that already have an established relationship, you can automatically use that relationship by just adding the columns we want from each table to the select statement.

9

2.3 Join

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)

10

2.4 Select_form

11

2.5 Select_form Example

12

2.6 Joining Tables without Predefined Relationship

13

2.7 Select_form Example

14

3 Working with Hierarchical Tables

3.1 Hierarchical Tables - Example

15

3.2 Hierarchical Tables - alias()

16

3.3 Querying Hierarchical Data

Often, you'll have tables that contain hierarchical data, such as employees and managers who are also employees. For this reason, you may wish to join a table to itself on different columns. The .alias() method, which creates a copy of a table, helps accomplish this task. Because it's the same table, you only need a where clause to specify the join condition. 17

3.4 Group_by and Func

18

3.5 Querying Hierarchical Data

19

4 Handling Large ResultSets

4.1 Dealing with Large ResultSets

Fantastic work so far! As Jason discussed in the video, sometimes you may have the need to work on a large ResultProxy, and you may not have the memory to load all the results at once. To work around that issue, you can get blocks of rows from the ResultProxy by using the .fetchmany() method inside a loop. With .fetchmany(), give it an argument of the number of records you want. When you reach an empty list, there are no more rows left to fetch, and you have processed all the results of the query. Then you need to use the .close() method to close out the connection to the database.

20

4.2 Fetching Many Rows

21