CODEWITHSUNDEEP
pythonjoinormsqlitesqlalchemy
user7675859
user7675859

Reputation: 11

SQLAlchemy ORM dynamic join not returning all columns from all joined tables

I am new to sqlalchemy. So any help is appreciated. I have a function that constructs my queries for my application. I pass it a list of tables to join.

Here are the relevant code snippets.

class Scope(Base):
    entry = Column(String(512))
    location_id = Column(Integer, ForeignKey('location_id'))
    type = Column(String(128))

class Location(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String(512)
    modified_by = Column(String(128))

instances = [Scope, Location]
join_classes = [Location]

queryset = session.query(*instances).join(*join_classes).all()

Here is the SQL query that runs (when I print queryset to screen before the .all()):

queryset: SELECT scope.id AS scope_id, scope.location_id AS scope_location_id, scope.entry AS scope_entry, scope.type AS scope_type, location.name AS location_name, location.modified_by AS location_modified_by,
FROM scope JOIN location ON location.id = scope.location_id

My end result I want is: a list of dictionaries for all columns (from both tables - like regular inner join gives a single table).

However, I am getting the a list when I type(queryset) and when I just try to do [u._asdict() for u in queryset] which is how I return a list of dictionaries in queries that don't have a join, it only returns a list of dictionaries for 1 column from each table (the column in the __repr__.

I need all columns from both tables to be returned.

Right now this is how what is what I get: [{'Scope': 192.168.0.0/24, 'Location': main}, ...]

I need something like, where all columns from the join are returned in a list of dictionaries: [{'Scope.entry': 192.168.0.0/24, 'Scope.type': 'virtual', 'Location.name': main, 'Location.modified_by': 'jim'}, ...]

In my code the instances & join_classes are dynamically passed and not hard coded as different functions pass the table models to join on (with the 1st model table being the table that all proceeding join on). I need this to work with a join on multiple tables (but all tables will be joined to the 1st model table, Scope in this example.)

Edit: I finally realized I was getting a list of sqlalchemy table objects back. That is why I was getting the __repr__ values when displaying.

Upvotes: 1

Views: 1502

Answers (2)

Tanjin Alam
Tanjin Alam

Reputation: 2456

records = DBSession.query(GeneralLedger, ConsolidatedLedger).join(ConsolidatedLedger, GeneralLedger.invoiceId == ConsolidatedLedger.invoiceId).all()

this sort of query return both table data

Upvotes: 1

user7675859
user7675859

Reputation: 11

Well, writing something done definitely helps you figure out the answer.

For anyone that might benefit this is what I did. I am sure there is a more eloquent way to do this so please let me know if so.

I finally read my output correctly and realized that it was giving me 2 table model objects (1 per table joined). I then iterated over each, converted each iteration to a list of dictionaries and then merged those dictionaries appropriately so that i had one list of dictionaries like a inner join table would give me.

Here is some of my code:

for obj in queryset:
    result.append(queryset_to_dict(obj))

for r in result:
    new_dict = {}
    for inner in r:
        new_dict = {**new_dict, **inner}
    new_result.append(new_dict)
  • Note the queryset_to_dict is a function I created for converting sqlalchemy table model objects to list of dictionaries.

Upvotes: 0

Related Questions