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:17 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.
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. In SQLAlchemy, you construct a class dynamically through the
Each class then specifies the name of a table it corresponds to using the
.__tablename__ special attribute, and then declares the attributes on the object. Here, the
Column object indicates the attributes that map to a column in the table.
Let me scroll down so you can see the
Book() declaration—similar stuff going on here with both an ID and a title. Notice that the
author_id attribute uses a
ForeignKey object to indicate that it is mapping to the author. You pass in a string specifying the table and column name of what it’s relating to.
The data that comes back is made up of
Author objects instead of tuples. Because I wrote a nice pretty dunder method on the
Author object, you get a very readable bit of output here for the seven authors in the database. Let’s try the same thing with the books …
WHERE clasues are similar to before. And because in this case, I was only expecting one item in the result, I can use the
.one() method. If more than one piece of data were returned, this would throw an exception.
Let’s muck around some more before I do that. You can also use the relationship directly as a
SELECT clause, even skipping the selecting, showing just the books related to this
Author object like I’ve done here.
This is another way of getting a single object. Here, I’ve asked the session to give me an author who has a primary key of
4. This is one less function called than the previous way of accomplishing the same thing. The contents of
king is going to be the same as before I did this, because I’m getting it the same place. Let’s add another book.
07:19 And look at that! More stuff for our library. The shelves must be getting full—or they would be if I ever finished that transaction off. The IDs will be correct now. I’ll show you that in a second.
I’m worried about our shelves getting overloaded. Let me get rid of something first. Using the
.remove() call on the
.books attribute allows me to get rid of a book, specifying which one with an instance of a
Because I removed
king from the database, not only is he gone, but so are his books. This is all 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 this back.
Become a Member to join the conversation.