sqlalchemy: AttributeError: type object 'customer' has no attribute 'invoices'

I am new to sqlalchemy. I can create database tables by declarative mapping like this:

engine = create_engine("--engine works---")

Base = declarative_base()

class Customer(Base):
    __tablename__ = 'customer'

    customer_id = Column(Integer, primary_key=True)
    name = Column(String(30))
    email = Column(String(30))

    invoices = relationship(
        'Invoice',
        order_by="Invoice.inv_id",
        back_populates='customer',
        cascade="all, delete, delete-orphan"
    )


class Invoice(Base):
    __tablename__ = 'invoice'

    inv_id = Column(Integer, primary_key=True)
    name = Column(String(30))
    created = Column(Date)

    customer_id = Column(ForeignKey('customer.customer_id'))

    customer = relationship('Customer', back_populates='invoices')

Base.metadata.create_all(engine)

This is fine. I added some data into both customer and invoice tables.

So far so good. Next, I would try out automap_base on this existing database like this:

from sqlalchemy import select, text
from sqlalchemy.orm import declarative_base, sessionmaker
from sqlalchemy.ext.automap import automap_base


engine = create_engine('--engine works---')
Base = automap_base()
# reflect
Base.prepare(engine, reflect=True)

Customer = Base.classes.customer
Invoice = Base.classes.invoice

Session = sessionmaker(bind=engine, future=True)
session = Session()

# query invoice
stmt = select(Customer, Invoice).join(Customer.invoices).order_by(Customer.customer_id, Invoice.inv_id)
res = session.execute(stmt)

for c in res:
    print(c.customer_id)

When I ran the code, I got:

AttributeError: type object 'customer' has no attribute 'invoices'

What did I miss for the relationship on the Customer (one side) or Invoice (many side) in this case so that when I query for customers with its invoices attibute and for invoices with customer attribute? Thanks for any help.

Solution

By default, automap will create the relation in the parent by appending "_collection" the lower-cased classname, so the name will be Customer.invoice_collection.

While answering this, I found that the join would raise an AttributeError on Customer.invoice_collection unless I performed a query on Customer beforehand, for example

session.execute(sa.select(Customer).where(False))

I'm not sure why that happens, however you don't necessarily need the join as you can iterate over Customer.invoice_collection directly, or join against the invoice table:

stmt = sa.select(Customer, Invoice).join(Invoice)
res = session.execute(stmt)
for c, i in res:
    print(c.customer_id, i.inv_id)