1 Filtering and Targeting Data

1.1 Where clauses

1 2

1.2 Expressions

3 4

Note that we didn't call a fetch() method in this example. this is a feature of SQLAlchame 2 allow the result proxy to be used as the target of a loop.

1.3 Conjuctions

5

1.4 Example: Filter data selected from a Table - Expressions

In addition to standard Python comparators, we can also use methods such as in_() to create more powerful where() clauses. You can see a full list of expressions in the SQLAlchemy Documentation.

We've already created a list of some of the most densely populated states.

# Create a query for the census table: stmt
stmt = select([census])

# Append a where clause to match all the states in_ the list states
stmt = stmt.where(census.columns.state .in_(states))

# Loop over the ResultProxy and print the state and its population in 2000
for result in connection.execute(stmt):
    print(result.state, result.pop2000)

2 Overview of Ordering

2.1 Order by clauses

To sort the result output by a field, we use the .order_by() method. By default, the .order_by() method sorts from lowest to highest on the supplied column. You just have to pass in the name of the column you want sorted to .order_by().

7

2.2 Order by Ascending

8

2.3 Order by Descendng

9 All you have to just pass in desc() inside an .order_by() with the name of the column you want to sort by. For instance, stmt.order_by(desc(table.columns.column_name)) sorts column_name in descending order.

2.4 Order by Multiple

10 11

3 Counting, Summing and Grouping Data

3.1 SQL Functions

12

3.2 Sum Example

13

3.3 Group by

14 15 16

3.4 Group by Multiple

17

3.5 Handling ResultSets from Functions

18

3.6 Using lable()

19

3.7 Example : Counting Distinct Data + .fetchall() , .first() and .scalar()

Counting Distinct Data

3.8 Example: Count of Records by State (if you want ot play with groupby)

Count of Records by State

4 Let's use Pandas and Matplotlib to visualize our Data

4.1 SQLAlchemy and pandas

20

4.2 Dataframe Example

21

4.3 Graphing

22

4.4 Graphing Example

23

4.5 Graphing output

24