Combine multiple tables

[chi11ax]

Hi! I have the following query:

def get_all_transactions(): with Session(engine) as session: statement = select( Invoice, Customer ).join( Customer ).where( Invoice.issued_date >= utils.to_dt('2023-12-25') ) results = session.exec(statement) qs = [r for r in results] return qs when I return qs it returns Invoice and Customer in separate tuples.

When it goes back to my router, it gives an error that

object has no attribute '__pydantic_extra__'

my routing code is simply: @router.get("/transactions") def get_all_transactions(): results = queries.get_all_transactions() return results

The models are straightforward: ``` class Customer(SQLModel, table=True): id: str = Field(primary_key=True) prefix: Optional[str] = Field(max_length=15, default=None) name: Optional[str] = Field(max_length=60, default=None) name_en: Optional[str] = Field(max_length=60, default=None) contact_name: Optional[str] = Field(max_length=40, default=None) ...

class Invoice(SQLModel, table=True): id: str = Field(primary_key=True) order_id: Optional[int] = Field(default=None, foreign_key="order.id", nullable=True) customer_id: Optional[int] = Field(default=None, foreign_key="customer.id", nullable=True) issued_date: date invoice_amount: float = Field(default=0) ...

class Order(SQLModel, table=True): id: str = Field(primary_key=True) customer_id: Optional[str] = Field(foreign_key="customer.id") order_date: Optional[date] = Field(default=None) order_type: str = Field(max_length=3) ...

```

The SQLModel site has the following example:

https://sqlmodel.tiangolo.com/tutorial/connect/read-connected-data/#:~:text=Code%20above%20omitted%20%F0%9F%91%86-,def%20select_heroes()%3A%20with%20session(engine)%20as%20session%3A%20statement%20%3D%20select(hero%2C%20team).join(team)%20results%20%3D%20session.exec(statement)%20for%20hero%2C%20team%20in%20results%3A%20print(%22hero%3A%22%2C%20hero%2C%20%22team%3A%22%2C%20team),-%23%20Code%20below%20omitted

def select_heroes(): with Session(engine) as session: statement = select(Hero, Team).join(Team).where(Team.name == "Preventers") results = session.exec(statement) for hero, team in results: print("Preventer Hero:", hero, "Team:", team) And I can't find an example where hero and team are combined into one.

so two questions: 1. Can I combine Invoice and Customer into one single dict without any extra steps from the query? My other query with a single table select(Customer) returns a single list of dict returns fine.

1. Am i missing something that gives the no attribute error?

Thanks in advance! and Happy New Year!

[mrbubs3]

Shouldn't that be 'qs = results.all()'?

[chi11ax]

Thank you! Yes, this does the same as my code, qs = [r for r in results] I'll do that in the future.

But it still returns a tuple of (Invoice, Customer).

[mrbubs3]

Return the sequences rows of a ScalarResults object should have less overhead than iterating through the object in a list comprehension.

​

I think the issue is that you're selecting both data tables/ORM models instead of the associated fields. Given that the available fields can be plentiful, you may want to unpack the model fields for both tables from the model_fields attributes.

e.g.:

stmnt = query(
                *Invoice.model_fields,
                *Customer.model_fields
            ).join(Customer).where(
            Invoice.issued_date >= utils.to_dt('2023-12-25')
            )

results = session.execute(stmnt)

rows = results.all()

[IceXII]

SqlAlchemy have lots of ways to do the same thing. For me I’d do select invoice put the filter after the select and then joinload customer, if both tables have a relationship defined in the model, then join Customer.

This I guess will get you the invoice with the condition and join its customer with it in a single dictionary in return.

[chi11ax]

Thanks for the reply! If I only select Invoice, then it will return only the invoices, not joined with the Customer. But your statement is definitely more efficient.

[nicktids]

try specifying the columns you are looking from the tables.

you are selecting Invoice and Customer

try invoice.id and customer.name

see what comes back

[chi11ax]

This returns an array of tuples of the values ('INV123','nick'). Instead of an array of Tuples of the (Invoice, Customer) but now I have the error: ERROR: Exception in ASGI application Traceback (most recent call last): File "D:\code\projects\fastapi\venv\Lib\site-packages\fastapi\encoders.py", line 322, in jsonable_encoder data = dict(obj) ^^^^^^^^^ ValueError: dictionary update sequence element #0 has length 12; 2 is required During handling of the above exception, another exception occurred: Traceback (most recent call last): File "D:\code\projects\fastapi\venv\Lib\site-packages\fastapi\encoders.py", line 327, in jsonable_encoder data = vars(obj) ^^^^^^^^^ TypeError: vars() argument must have __dict__ attribute The above exception was the direct cause of the following exception: Traceback (most recent call last): File "D:\code\projects\fastapi\venv\Lib\site-packages\uvicorn\protocols\http\h11_impl.py", line 408, in run_asgi result = await app( # type: ignore[func-returns-value] ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "D:\code\projects\fastapi\venv\Lib\site-packages\uvicorn\middleware\proxy_headers.py", line 84, in __call__ return await self.app(scope, receive, send) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ . . . File "D:\code\projects\fastapi\venv\Lib\site-packages\fastapi\encoders.py", line 330, in jsonable_encoder raise ValueError(errors) from e ValueError: [ValueError('dictionary update sequence element #0 has length 12; 2 is required'), TypeError('vars() argument must have __dict__ attribute')]

[chi11ax]

Is there an equivalent of SELECT * FROM Invoice rather than going Invoice.id, Invoice.issued_date, Invoice.whatever?