Join us and get access to thousands of tutorials and a community of expert Pythonistas.

Unlock This Lesson

This lesson is for members only. Join us and get access to thousands of tutorials and a community of expert Pythonistas.

Unlock This Lesson

Hint: You can adjust the default video playback speed in your account settings.
Hint: You can set the default subtitles language in your account settings.
Sorry! Looks like there’s an issue with video playback 🙁 This might be due to a temporary outage or because of a configuration issue with your browser. Please see our video player troubleshooting guide to resolve the issue.

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: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.

00:34 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:47 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 declarative_base() factory.

01:06 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.

01:24 You’ll notice you have everything here you need to create the table. The relationship() mechanism is a helper. I’ll come back to it in a second.

01:34 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.

01:57 Because this is a foreign key, SQLAlchemy provides you the ability to create queries linking the object both forwards and backwards inside of the Book objects.

02:09 Inside of the Book object, you can get at the book’s author. By specifying the relationship indicator on the Author object, you can also tie it backwards, as well.

02:20 The relationship declaration is saying that there is a foreign key in the Book object that points to the author. This allows you to get all of the books on an author by saying author.books().

02:33 It also adds some constraints indicating to the database that if the author is deleted, any associated books should go as well. That way you don’t end up with any authorless books by accident.

02:46 All right, let’s put this into the REPL.

02:58 Still need the engine … but instead of a connection, in ORM land, you’ll use a Session.

03:19 This is a similar concept to the connection. It just tells SQLAlchemy that you wish to use the ORM instead. Now I’ll import the Author and Book objects from the models file I just showed you.

03:36 And then our good friend select. Instead of selecting using a table object, like in the previous lesson, you can select using an ORM object.

04:00 Using the statement, I was able to query all the authors. This is done using the .scalars() method on the session instead of the .execute() method on the connection.

04:10 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 …

04:38 and there you have it. I can do more complex queries doing a .join().

04:53 Instead of using the tables directly, like in the raw example previously, I can use the ORM objects.

05:08 And there you go. The book with an author whose last name is greater than "B".

05:22 Selects with 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.

05:41 This is nice safety feature if you’re trying to get back exactly one row from your database. And there is Mr. King, Señor Spookypants to his friends. On the Author is the books relationship.

05:56 I can use that to add a book to king. Scary clowns for the win.

06:14 Notice the IDs on the Book. SQLAlchemy understand the relationships between the objects. It knows that that book belongs to Stephen, but it doesn’t update the info until after a commit.

06:27 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.

06:49 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.

07:35 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 Book object.

07:55 Printing it out, you can see two things. One, I got rid of Dead Zone. Two, because of the last commit, the IDs for It have been populated.

08:07 .remove() is used for related things, like I showed you here. .delete() is used for objects.

08:22 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.

08:42 And like with connections, sessions should also be closed when you’re done with them. All right. You’ve seen some ORM basics. Next up, I’ll show you more complicated interobject relationships.

Become a Member to join the conversation.