Sql Alchemy : using session.query()

Introduction

SqlAlchemy provides developers with a tool that gives the full power and flexibility of SQL. One particularly useful tool is session.query, but first let's quickly refresh on some terminology we use when referencing data in or database. A table refers to the structure in the database(db) pertaining to a particular class and is the means for storing instances of a class and their attributes. A record refers to one row of the table and is also representative of one instance of the class the table is for. Finally, the table's column's represent attributes for the class the table is for, wherein each record has its own column entry pertaining to attributes for that class instance.

In addition, to interact with the database, we create a session object to form a connection between our code and our database. We use sessionmaker to define our Session class Session = sessionmaker(bind=engine), and then create an instance of that Session() by session = Session(). This allows us to then reference session throughout our application to manipulate data in our database.

Using session.query

session.query serves as our template for writing sql statements to communicate with our database. Let's first breakdown a common sqlalchemy query statement that looks like this query=session.query(Band). Here we are storing our query in the query variable, session.query acts as our mechanism for querying our database, and, (Band) defines the class that we want to access. Together they essentially allow sqlalchemy to construct sql statements for us to send to our db. As can be seen in Ex. 1, when we print the query, we get the actual sql statement that we would use to retrieve the data we want. Printing the actual query you construct with session.query can be a quick way to gain some understanding of what is goin on under the hood.

Ex. 1
Next, to access the actual data from our query we use the .all() method on our query. A sidenote, the .all() method returns a list of Band instances since our query(Band) grabs the whole record (e.g. row of each band). We can also write our queries, to grab certain attributes (e.g. columns) from our database. As shown in Ex. 2, we can query(Band.name, Band.website) to only grab the columns for name and website of our Band records.

Ex. 2
This example also shows that when grabbing multiple different pieces in our query, we get a list of tuples where each tuple references a individual record, and each element of the tuple is info we wanted to grab from that record.

Finally, there are some additional methods such as .first(), .one(), and .scalar(). Each of these is applicable in certain cases where we only want a single record or element. There are also many other methods that can be called on a query that will not be touched on here, but more info can be found at www.tutorialspoint.com/sqlalchemy/sqlalchemy_orm_using_query or www.tutorialspoint.com/sqlalchemy/sqlalchemy_orm_returning_list_and_scalars

using filter() to grab certain records

Now that we can use query to grab data from our tables, it would be helpful to have some way to further select portions of our data as needed. This can be done by applying a .filter() method to our query. Then in our .filter() we supply our criteria for how to limit the query. Ex. 3a and 3b provides a common example when we want to find a particular record within our table.
Ex. 3a Ex. 3b
In Ex. 3a, query is being made as part of an instance method within the Musician class, so we use self.id to select our Musician. Ex. 3b simply shows how that would like if the Musician we wanted had an id = 5.

Ex. 4 show another use of filter to grab a subset of records. The query includes both the Band instance, and the is_looking attribute to show that only bands where is_looking == True were grabbed.

Ex. 4 In ex 5, we show how the filter can take multiple criteria to further narrow our query. The query grabs auditions whose is_accepted attribute == False and that have a musician who's id is less than or equal to 10.

Ex. 5

using func.count() and group_by()

Sql also provides a number of sql functions that can be used as part of our query to further enhance how we can interact with data in our database. There are a number of different functions e.g. (max, min, mode, sum, count, etc...), and here we will focus on count in combination with the group_by() method as a way to analyze the data in our db.

In ex. 6, we are returning a list of tuples, that include both the skill level and number of musicians at that skill level in our db.
Ex. 6 Let's go through the code and see what's going on here. First, the query() establishes the two pieces of info we are goin to have in our list. The first is straightforward, the skill_level attribute (column); however, the second is much more complex. func.count refers to the sql function, and it will simply count what it is given, here the id's of musicians. The .label('skill_count') is a descriptive method that allows as to assign a label/variable for the count so that we can then access this data (e.g. maybe sort by the count). Before we talk about the last part of the code, let's see what happens when we query using session.query(Musician.skill_level, func.count(Musician.id).label('skill_count')).

In Ex. 7, we see the result, it only gives us one skill level 2, and one count 101 (this is the total # of musicians in our db).
Ex. 7 This query provides some good context because what happens is it grabs the first musician record's skill_level, which is 2, and then pairs with our count, which counts every individual's musician id. The problem is we don't want to count all the id's at once, and pair it with the first skill_level we pull from the db. This is why .group_by() is included in our query. This method groups all the records we grab based on Musician.skill_level, so that in this example we have five groups (1, 2, 3, 4 ,5). It then works in tandem with our func.count to then count the Musician.id for each musician that resides in our different groups. This provides the ability to analyze the data in our db using the tools of sqlAlchemy.

Using Join in query

Another useful method is .join() because it allows us access data from more than one table, especially when there exists a relationship between these tables. Ex. 8 shows what happens when we try to query two different tables, we get a SAWarning about a cartesian product between our two tables. While not provided in the example, the resulting list essentially returns a cross product between our tables, which means it creates an entry for each pair of (Band, Audition). In other words, if we have 40 Bands, and 55 Auditions, our resulting list will have 2200 entries, Yikes!!.
Ex. 8 This is why the warning also tells us to apply join conditions. The combination of tables needs to be given instructions on how to associate the records from each table with one another. If there is a foreign key between the tables, than join will be able recognize this relationship. As can be see in Ex. 9, we can return a list of (Band,Audition) pairs where the band.id == Audition.band_id. Because we query both Band and Audition, we get back 55 entries, each representing a different Audition and the Band for that Audition.
Ex. 9 Ex. 10, provides a different list where we grab the only the Bands that have and audition associated with them. In both these cases, the .join() method acts similarly to using .filter(Band.id == Audition.band_id), as can be seen by comparing Ex. 11 to Ex. 10, but allows us to do so without having to right out the explicit association because our models were designed having a relationship.
Ex. 10 Ex. 11 However, if there are more than one or no foreign keys, using an explicit from as shown below is a better choice. From tutorialspoint.com/sqlalchemy/sqlalchemy_orm_working_with_joins

Conclusion

sqlAlchemy provides developers with a set of tools to inquire, manipulate, and even analyze the data in our database. session.query provides a quicker means for execution of sql statements, that allows to develop more complex sql statements in a readable manner. It can be especially beneficial when chaining multiple query methods together along with sql functions in order to provide results of analysis about the data in the database.