Calculating a difference between two columns
Often, you'll need to perform math operations as part of a query, such as if you wanted to calculate the change in population from 2000 to 2008. For math operations on numbers, the operators in SQLAlchemy work the same way as they do in Python.
You can use these operators to perform addition (+
), subtraction (-
), multiplication (*
), division (/
), and modulus (%
) operations. Note: They behave differently when used with non-numeric column types.
Let's now find the top 5 states by population growth between 2000 and 2008.
This exercise is part of the course
Introduction to Databases in Python
Exercise instructions
- Define a select statement called
stmt
to return:- i) The state column of the
census
table (census.columns.state
). - ii) The difference in population count between 2008 (
census.columns.pop2008
) and 2000 (census.columns.pop2000
) labeled as'pop_change'
.
- i) The state column of the
- Group the statement by
census.columns.state
. - Order the statement by population change (
'pop_change'
) in descending order. Do so by passing itdesc('pop_change')
. - Use the
.limit()
method on the previous statement to return only 5 records. - Execute the statement and
fetchall()
the records. - The print statement has already been written for you. Submit the answer to view the results!
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
# Build query to return state names by population difference from 2008 to 2000: stmt
stmt = select([____, (____-____).label(____)])
# Append group by for the state: stmt_grouped
stmt_grouped = stmt.group_by(____)
# Append order by for pop_change descendingly: stmt_ordered
stmt_ordered = stmt_grouped.order_by(____)
# Return only 5 results: stmt_top5
stmt_top5 = ____
# Use connection to execute stmt_top5 and fetch all results
results = connection.execute(____).fetchall()
# Print the state and population change for each record
for result in results:
print('{}:{}'.format(result.state, result.pop_change))