SQLAlchemy: ORM

00:00 In the previous lesson, I finished up the core part of SQLAlchemy. In this lesson, I’ll show you how to use the ORM. An ORM or object-relational mapping is an abstraction where Python objects map to tables in the database.

00:16 Any query you make will result in objects instead of lists of tuples, and the properties on the objects reflect the column data from the table. Objects can also have references to other objects abstracting the concept of foreign keys.

00:33 All of this is built on top of the core part of the SQLAlchemy library, meaning they didn’t have to reinvent the wheel here. Let’s play with some code to get some better understanding.

00:46 The key to any ORM is declaring your objects to map them into your database. In most ORMs, you have to inherit from some base class that helps give the library information about what you’re abstracting.

01:00 In SQLAlchemy, you kind of have to do this twice. The declarative base class is meant as a grandparent class you use to create a base class for your model objects.

01:11 As far as I can tell, this has something to do with how the underlying table constructs are generated. In older versions of SQLAlchemy, you used a function to generate this class, so I suspect for the most part it’s done this way for historical reasons.

01:27 Once you’ve got that base class, you declare a model class that inherits from it. Your model corresponds to a table in the database. This would be the author table.

01:37 You indicate that using the __table__ attribute, you then declare attributes on the class that define the columns. This is one of the changes in SQLAlchemy 2.

01:50 Formally, you used a column object to do this, but the latest version takes advantage of type hints to reduce the amount of code you have to write. Let me start with one of the simpler ones as the first name field is a string and doesn’t need any other info for its declaration.

02:06 You define it using the mapped type object and pass in string. In more complex cases where you need to give the database more information, you add to the mapped type hint by calling the mapped_column() function.

02:20 The extra stuff you need to tell SQLAlchemy about goes in the call to the function. In this case, I’m telling the ORM that not only is author_id an integer, but it’s also the primary key.

02:34 Below those first three fields is books. I’ll come back to that in a second after I’ve shown you what a book looks like and then I can discuss what a relationship means.

02:44 Let me scroll down. Here inside of the book declaration, you can see similar kinds of declarations happening with a book_id and a title. Now let’s talk about relationships between objects.

02:59 Here the book declares an author_id field to store the primary key of the foreign key author to indicate that this is in fact a foreign key.

03:09 You once more use the mapped column function this time passing in a foreign key object. The foreign key object takes an argument, which is a string giving the table and column name of the relationship.

03:22 In this case, that’s the author table and the author_id column inside of it. I’m also stating that this column can’t be null. A book has to have an author. Because this is a foreign key, SQLAlchemy provides you the ability to create queries linking the objects both forwards and backwards.

03:43 Unlike the author_id attribute, the author attribute doesn’t correspond to a column in the database. It’s simply a relationship on the object.

03:53 You declare that with the relationship() function, this function needs to know what to call the backwards relationship so that it back populates the attribute which accesses an author.

04:05 In this case is going to be books. So author.books will point to book objects. Let me go back up to the author now. Here I’ve done something similar telling SQLAlchemy that the book class has a foreign key pointing to the author class.

04:23 Again, this is just a relationship. It’s not a new column. Like with books, I set up a back population shortcut, and because this is a foreign key relationship, I also tell the database what to do with the books if I delete the author.

04:37 The cascade value says to remove any corresponding books, since more than one book can have the same author, the type hint for this relationship is a list of books rather than a single one.

04:50 You have to be careful with these when writing the code. In the next lesson, I accidentally forgot to indicate list and got some very weird and very hard to debug behavior.

05:00 With both the author and book relationships in place, I can now say author.books or book.author to run a query to get the related objects.

05:11 Let’s go use this in the REPL.

05:21 I still need the engine,

05:36 but instead of a connection, I’m going to use a session.

05:42 It’s a similar concept, but it indicates that you’re in ORM land. Now, I’ll import the author and books

05:54 and our good friends, select, and instead of selecting using a table object like in the previous lesson, you select using an ORM object. Using this statement, I can now query all the authors.

06:12 This is done using the scalars() method of the session.

06:23 The data that comes back is made up of author objects because I wrote a nice __repr__ method. On the objects, you’ll get a very readable bit of output for our seven authors.

06:36 Let’s do the same with our books,

06:48 and there you have it. I can do more complex queries using a join instead of the tables. Once again, I’m going to use objects

07:13 and there you go. The book with an author whose last name is greater than B, as I’m only expecting one item. In the result. Instead of using scalars(), I can use the one() method

07:37 and there is Mr. King, senior Spooky pants to his friends. If more than one item had come back, this piece of code would throw an exception. Instead, I can use the books relationship on the king value to add a new book.

07:59 Scary Clowns for the Win.

08:08 Did you notice the IDs on that book? SQLAlchemy understands the relationships between the objects. It knows that book belongs to Steven, but it doesn’t update the info until after a commit.

08:20 Let’s muck around a bit more. Before doing that. You can also use the relationship directly as a SELECT clause, showing just the books related to this Author object.

08:29 There is another way of getting a single object.

08:39 Here, I’ve asked the session to give me an author who has a primary key of 4. This is one less function call than the previous way of accomplishing the same thing.

08:47 Let me add another book.

08:55 And look at that! More stuff for our library. The shelves must be getting full—or they would be if I finished the transaction off. And there it is. The IDs will be correct now. I’ll show you that in a second.

09:15 I’m worried the shelves are getting overloaded. Let me get rid of something first.

09:25 Using the .remove() call on the .books attribute allows me to get rid of a book, specifying which one with an instance of a Book object.

09:34 Printing out (kings-books) you can see two things. One, I got rid of Dead Zone. Two, because of that last commit, the IDs have been populated.

09:49 .remove() is used for related things, .delete() is used for objects. Because I removed king from the database, not only is he gone, but so are all of his books.

10:06 This is due to that relationship declaration with the cascade clause in the model definition. Of course I want Stephen in my collection, so let me roll all that back.

10:20 And like with connections, sessions should be closed.

10:28 All right. You’ve seen some ORM basics. Next up, I’ll show you more complicated interobject relationships.