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.

本练习是课程的一部分

Introduction to Databases in Python

查看课程

练习说明

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'.
Group the statement by census.columns.state.
Order the statement by population change ('pop_change') in descending order. Do so by passing it desc('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!

交互式实操练习

通过完成这段示例代码来试试这个练习。

# 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))

编辑并运行代码