Locked learning resources

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

Unlock This Lesson

Locked learning resources

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

Unlock This Lesson

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.

Avatar image for Dirk

Dirk on Aug. 31, 2022

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

Avatar image for Christopher Trudeau

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?

Avatar image for ihdrossos

ihdrossos on Sept. 4, 2022

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


Because I get following message:

AttributeError: 'list' object has no attribute 'books'

Thank you in advanced.

Avatar image for Bartosz Zaczyński

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

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

Avatar image for Christopher Trudeau

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.

Avatar image for Dirk

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!!!

Avatar image for Christopher Trudeau

Christopher Trudeau RP Team on Sept. 8, 2022

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

Avatar image for ihdrossos

ihdrossos on Sept. 10, 2022

Thank you all for your answers.

Avatar image for Mark

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)?

Avatar image for Mark

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}"
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 Company, None

Avatar image for Christopher Trudeau

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:


Avatar image for Mark

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.

Avatar image for ballingj

ballingj on Dec. 4, 2022

This is puzzling me. It does not retract form the functioning of the code, but it bothers me that I can’t find any information on it. In the models.py, where we define the repr method, there is this strange !r appended to the variables of the fstring. I’ve never seen this before, and can’t find any doc why those are there. Can some one explain?

class Book(Base):
    __tablename__ = "book"

    book_id = Column(Integer, primary_key=True)
    title = Column(String)
    author_id = Column(Integer, ForeignKey("author.author_id"), nullable=False)

    def __repr__(self):
        return (
            f"Book(book_id={self.book_id!r}, "
            f"title={self.title!r}, author_id={self.author_id!r})"
Avatar image for Christopher Trudeau

Christopher Trudeau RP Team on Dec. 4, 2022

Hi @ballingj,

The “!r” modifier in an f-string tells the formatter to use the __repr__ version of a value rather than the __str__ version of the value. It is the equivalent of calling repr(self.title) instead of str(self.title).

Since I’m using f-strings in Book’s __repr__ method, it is best practice to have any referenced values also use the __repr__ version.

Avatar image for Christopher Trudeau

Christopher Trudeau RP Team on Dec. 4, 2022

PS: you can see either the string docs:


or the RP article for more info on f-strings:


Avatar image for ballingj

ballingj on Dec. 5, 2022

Hi @Christopher Trudeau, Thank you. The links were very helpful; the article sent me on another link which explained the difference of str() and repr() and their implications very well. dbader.org/blog/python-repr-vs-str

Avatar image for Coxy

Coxy on Nov. 19, 2023

Please could I ask for assistance? (Very green)

I am having trouble with this statement:

stmt = select(Book).join(Book.author).where(Author.last_name > "B")
for book in session.scalars(stmt):

I am getting and the following error:

stmt = select(Book).join(Book.author).where(Author.last_name > "B")
AttributeError: type object 'Book' has no attribute 'author'. Did you mean: 'author_id'?

I have changed the .join(Book.author) to .join(Book.author_id) then I get even more errors? I know I am missing something silly but I can not figure it out. Can someone point me in the right direction.

Thank you,


Avatar image for Christopher Trudeau

Christopher Trudeau RP Team on Nov. 19, 2023

Hi Coxy,

SQLAlchemy is ever evolving and this course was built using SQLAlchemy 1.4.36. I double checked the code with that version and it worked.

I then upgraded to the most recent version of SQLAlchemy, which is 2.0.23 and was able to recreate the error you are asking about.

The 1.4 version was meant as a bridge between the old 1.0 world and the 2.0 re-write. I haven’t played with the 2.0 version yet (I don’t use SQLAlchemy a lot in my work), and so can’t speak to what you have to do differently. Something they’ve done in the upgrade is what is causing your problem. I suspect it is related to how the model files are built, and how you specify the relationship between Author and Book, but that’s only a guess.

I’d suggest, for the duration of the course, downgrade to 1.4 so you can get the idea of how everything works. Once you’re comfortable, hit the docs and figure out what the syntax difference is. You might start here: docs.sqlalchemy.org/en/20/changelog/migration_20.html

Good luck with it. …ct

Avatar image for Coxy

Coxy on Nov. 19, 2023

Thank you Christopher for the detail you put in, very helpful and appreciated.

Seemed to have trouble downgrading? I will have to look further into that.

But I took up your challenge and for those like me who are unable to follow instructions such as download version.......

The following code seems to be working for at least this part of the course:

from sqlalchemy import ForeignKey, Integer, String
from sqlalchemy.orm import DeclarativeBase, relationship
from sqlalchemy.orm import mapped_column

class Base(DeclarativeBase):

class Author(Base):
    __tablename__ = "author"

    author_id = mapped_column(Integer, primary_key=True)
    first_name = mapped_column(String)
    last_name = mapped_column(String)

    books = relationship("Book", back_populates="author")

    def __repr__(self):
        return (
            f"Author(author_id={self.author_id!r}, "
            f"first_name={self.first_name!r}, last_name={self.last_name!r})"

class Book(Base):
    __tablename__ = "book"

    book_id = mapped_column(Integer, primary_key=True)
    title = mapped_column(String)
    author_id = mapped_column(Integer, ForeignKey("author.author_id"), nullable=False)

    author = relationship("Author", back_populates="books")

    def __repr__(self):
        return (
            f"Book(book_id={self.book_id!r}, "
            f"title={self.title!r}, author_id={self.author_id!r})"

Thank you once again.


Avatar image for Christopher Trudeau

Christopher Trudeau RP Team on Nov. 20, 2023

No worries Coxy.

As for “downgrading”, I probably should have been more explicit. You’ll need to do a python -m pip uninstall sqlalchemy and then do a python -m pip install sqlalchemy==1.4.36.

There might be other complications due to how many other libraries you have in your virtual environment, but of course one of the beautiful things about virtual environments is you could also just start with a clean one.

Become a Member to join the conversation.