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.

Dirk on Aug. 31, 2022

I get an import error when I want to import models. Anyone else?

Christopher Trudeau RP Team on Sept. 1, 2022

Hi Dirk,

I hate giving this response, but it does work for me. I double checked with:

>>> import models

Then also tried in a different session:

>>> from models import Author

Any troubles importing any of the other files? What version of Python are you running? What version of SQLAlchemy?

ihdrossos on Sept. 4, 2022

Hi. Can you please elaborate more on the following command:

king.books.append(Book(title="It"))

Because I get following message:

king.books.append(Book(title="It"))
AttributeError: 'list' object has no attribute 'books'

Thank you in advanced.

Bartosz Zaczyński RP Team on Sept. 5, 2022

@ihdrossos It looks like your king variable is already a list.

Christopher Trudeau RP Team on Sept. 5, 2022

Hi @ihdrossos,

Any chance you missed the “.one()” part on the end of this statement:

>>> king = session.scalars(stmt).one()

Without it, you’ll get more than one thing back.

Dirk on Sept. 7, 2022

@ Christopher Trudeau I’m so sorry. I did not noticed that the import models means importing models.py I was looking for not installed packages in my virtual environment. Thanks a lot for answering Mr. Trudeau!!!

Christopher Trudeau RP Team on Sept. 8, 2022

No worries Dirk, glad you figured it out. Happy coding.

ihdrossos on Sept. 10, 2022

Thank you all for your answers.

Mark on Sept. 29, 2022

Great tutorial, as usual. Your articles/tutorials and podcast appearances are among my favorite RP content.

This isn’t really a coding question as much as it is a design question from an inexperienced hobbyist, so I hope it’s not too out of place here. Can you envision projects for which something like SQLAlchemy is overkill? Sometime back, while learning sqlite, I wrote a little terminal based program to search and retrieve info from a simple 2 table database I keep locally (I wrote another that I can use to insert new entries in the db using basic prompts). In thinking of how I would implement it using SQLA, it feels like I would just be trading one syntax for another without gaining a lot in the bargain. I assume it’s a matter of picking the right tool for the job. I guess my question is, what is the lowest level of complexity you think warrants using something like SQLAlchemy? Or do you find it suitable for all things database related (and maybe I would too, if I got to know it a little better)?

Mark on Sept. 30, 2022

OK, an actual coding question. After defining tables using ORM, whenever I add a record to a table, the Primary Key doesn’t autoincrement. Rather, it returns None (what’s returning None is Companies(company_id)). What am I missing? Here’s the code:

>>from sqlalchemy import create_engine
>>engine = create_engine("sqlite+pysqlite:///:memory:", echo=True, future=True)
>>from sqlalchemy import MetaData
>>metadata = MetaData()
>>from sqlalchemy import Table, Column, Integer, String, ForeignKey
>>from sqlalchemy.orm import declarative_base, relationship
>>Base = declarative_base()
>>class Companies(Base):
    __tablename__ = 'companies'

    company_id = Column(Integer, primary_key=True)
    name = Column(String)

    contact = relationship("Contacts", back_populates="company")

    def __repr__(self):
        return f"{self.name}, {self.company_id}"
>>class Contacts(Base):
    __tablename__ = "contacts"

    contact_id = Column(Integer, primary_key=True)
    fname = Column(String)
    lname = Column(String)
    email = Column(String)
    phone = Column(String)
    company_id = Column(Integer, ForeignKey('companies.company_id'))

    company = relationship("Companies", back_populates="contact")

    def __repr(self):
        return f"{self.fname} {self.lname}, {self.company}"
>>Base.metadata.create_all(engine)
2022-09-30 09:50:30,489 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-30 09:50:30,490 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("companies")
2022-09-30 09:50:30,491 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-09-30 09:50:30,492 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("companies")
2022-09-30 09:50:30,493 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-09-30 09:50:30,494 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("contacts")
2022-09-30 09:50:30,494 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-09-30 09:50:30,495 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("contacts")
2022-09-30 09:50:30,496 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-09-30 09:50:30,498 INFO sqlalchemy.engine.Engine 
CREATE TABLE companies (
    company_id INTEGER NOT NULL, 
    name VARCHAR, 
    PRIMARY KEY (company_id)
)


2022-09-30 09:50:30,498 INFO sqlalchemy.engine.Engine [no key 0.00055s] ()
2022-09-30 09:50:30,499 INFO sqlalchemy.engine.Engine 
CREATE TABLE contacts (
    contact_id INTEGER NOT NULL, 
    fname VARCHAR, 
    lname VARCHAR, 
    email VARCHAR, 
    phone VARCHAR, 
    company_id INTEGER, 
    PRIMARY KEY (contact_id), 
    FOREIGN KEY(company_id) REFERENCES companies (company_id)
)


2022-09-30 09:50:30,500 INFO sqlalchemy.engine.Engine [no key 0.00060s] ()
2022-09-30 09:50:30,501 INFO sqlalchemy.engine.Engine COMMIT
>>mortgage_co = Companies(name="Mortgage Company")
>>mortgage_co
Mortgage Company, None

Christopher Trudeau RP Team on Sept. 30, 2022

Interesting question Mark, glad you’re enjoying the content!

Coding is always about the trade-offs. With modern computing you can get away with a lot before having to go into relational mode. I think the two things that would drive my thinking here would be: 1) how much data, and 2) what are the relationships between data items like?

If it isn’t a lot of data, you may be able to get away with serializing a list to and from a flat file. You can get a bit of structure out of things like CSV, XML, or the ubiquitous JSON. This approach means all of it fitting in memory though, so at some point data size predominates.

The data-relationship question is a bit trickier. If you’ve only got two tables, you might be able to normalize that into a single table and then it all becomes a flat list. You typically can do this with redundancy. Think of the Book/Author example, I can duplicate the Author info inside each Book and maybe get away with it. Makes updating the Author painful, but it might be good enough for what you’re building.

If on the other hand, you’re constantly editing both Books and Authors (bad example, you probably wouldn’t be in that case), then keeping that data as relations becomes more important.

The other thing you could check out is pickling. It is a bit of an old-school approach, but it allows you to serialize Python objects directly. Again, if you don’t have huge amounts of data, you can get away with relationships between class objects or dictionaries and just write it down to disk.

Here is an article on the pickle module if you want to learn more about it:

realpython.com/python-pickle-module/

Mark on Sept. 30, 2022

So … figured out the answer to my last question as I dived deeper into the documentation. Session.add() and Session.commit() work wonders.

Become a Member to join the conversation.