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.
Advertisement
Answer
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)