1. Filtering and targeting data
Now that we have the basics of connecting to the database and making a query down, it's time to dig deeper into select queries. So far, we've selected all the rows from a table. However, there is a way to filter rows by their values in particular columns using a where() clause on our select statements.
2. Where clauses
For example, let's say that we want to select all the records for the state of California. We start with the same select statement and add a where clause onto it that specifies that the value in the 'State' column must be equal to California. We then execute the statement and fetch all the results, as we have done before. We wrap up by looping over the results and print the state and age column from each row.
3. Where clauses
More generally, where clauses restrict data by performing an evaluation of a condition. Often this is a column that we want to compare with a value or another column. In the prior example, we tested equality of column values with 'California'. In addition to equality, with numerical values, there are also operators for greater than, less than and the other standard math comparison operators.
4. Expressions
In addition to such comparisons, there are also SQL expressions that provide more complex conditions than simple operators. A few common expression are in_() which match the column's value against a list, like() which matches the column's value against a partial value with wild cards, and between() which checks to see if the column's value is between two supplied values. There are many others as well. These expressions are available as methods on our Column objects. We can use such expressions to do things such as find all the state names that start with 'New'. In fact, Let's do just that.
5. Expressions
We start with the same basic select statement and add a where clause with the state column and use the startswith method on that column. We then loop over the ResultProxy and print the state and population in the year 2000. Note that we didn't call a fetch method in this example. This is a feature of SQLAlchemy to allow the ResultProxy to be used as the target of a loop.
6. Conjunctions
We can also use conjunctions which allow us to have multiple criteria in a where clause. The conjunctions are and_(), not_(), and or_() and they work just like they would in a sentence. Conjunctions can be useful for getting exactly the data we want. You might notice that these methods have an _ in their names. This is to avoid conflicting with the Python methods of the same name. It's also possible to nest multiple conjunctions to get extremely specific about the data we select, but that's not covered in this class. Let's see an example usage of conjunctions.
7. Conjunctions
For this example, we want to get all the records for California and New York. I could do this in a few different ways, but in this case I want to use the or_() conjunction. After we build our initial statement, we append a where clause with a single argument, which is the or_ conjunction; we also pass two arguments to or_, which match the states of California and New York, respectively, I then execute the statement and print the columns I want to see.
8. Let's practice!
Now it's your turn to filter your SQL queries in order to extract the specific information of interest. Get coding.