SQLAlchemy: ORM

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

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?

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.

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

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.

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

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

Thank you all for your answers.

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

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

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/

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

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})"
        )

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.

PS: you can see either the string docs:

docs.python.org/3/library/string.html#format-specification-mini-language

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

realpython.com/python-f-strings/

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

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):
    print(book)

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,

Coxy

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

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):
    pass


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.

Coxy

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.