I am wondering why a self referential relationship is failing to load in a
single query. First i would like to select all accounts which belong to a
specific branch in one query then select from all accounts in the other
query and do the mapping using sqlalchemy instead on emitting alot of sql
to database, which is having alot of latency
class BranchAccount(DeclarativeBaseGuid):
__tablename__ = 'account_branch'
account_id = Column( "account_id" , VARCHAR( length=32 ) , ForeignKey(
'accounts.guid' ) , index=True )
branch_id = Column( "branch_id" , VARCHAR( length=32 ) , ForeignKey(
'branch.guid' ) )
color = Column('color', VARCHAR(length=20))
notes = Column('notes', VARCHAR(length=200))
account = relationship("Account", back_populates="branches")
branch = relationship("Branch", back_populates="accounts")
def __init__(self , color="rgb(237,236,235)" , notes=""):
self.color = color
self.notes = notes
def __unirepr__(self):
return u"<BranchAccount <{}> <{}>".format(self.account.name,
self.branch_id)
class Branch(DeclarativeBaseGuid):
__tablename__ = 'branch'
# column definitions
name = Column('name', VARCHAR(length=25), nullable=False)
location = Column('location', VARCHAR(length=200), nullable=False)
book_guid = Column('book_id', VARCHAR(length=32),
ForeignKey('books.guid'), index=True)
book = relationship( 'Book' , back_populates='branches' )
users = relationship( 'User' ,
back_populates="branch" ,
cascade='all, delete-orphan' ,
collection_class=CallableList ,
)
accounts = relationship( "BranchAccount" , back_populates="branch" ,
cascade='all, delete-orphan' , lazy="subquery"
,
collection_class=CallableList)
def __init__(self, name="", location="", users=None):
self.name = name
self.location = location
if users:
self.users = users
def __unirepr__(self):
return u"Branch<{} {}>".format(self.name, self.location)
class Account(DeclarativeBaseGuid):
__tablename__ = 'accounts'
# column definitions
guid = Column( 'guid' , VARCHAR( 32 ) , primary_key=True ,
nullable=False , default=lambda: uuid.uuid4().hex )
name = Column('name', VARCHAR(length=48), nullable=False, unique=True)
_type = Column( 'account_type' , ChoiceType( AccountType ) ,
nullable=False )
commodity_guid = Column('commodity_guid', VARCHAR(length=32),
ForeignKey('commodities.guid'))
_commodity_scu = Column('commodity_scu', INTEGER(), nullable=False)
_non_std_scu = Column('non_std_scu', INTEGER(), nullable=False)
parent_guid = Column('parent_guid', VARCHAR(length=32),
ForeignKey('accounts.guid'))
code = Column('code', VARCHAR(length=20))
description = Column('description', VARCHAR(length=200))
hidden = Column('hidden', INTEGER())
placeholder = Column('placeholder', INTEGER())
# relation definitions
commodity = relationship( 'Commodity' , back_populates='accounts' )
children = relationship( 'Account' ,
back_populates='parent' ,
lazy="select" ,
cascade='all, delete-orphan' ,
collection_class=CallableList
)
parent = relationship( 'Account' ,
uselist=False ,
remote_side=[guid]
)
lots = relationship( 'Lot' ,
back_populates='account' ,
cascade='all, delete-orphan' ,
collection_class=CallableList
)
budget_amounts = relationship( 'BudgetAmount' ,
back_populates='account' ,
cascade='all, delete-orphan' ,
collection_class=CallableList ,
)
scheduled_transaction = relationship( 'ScheduledTransaction' ,
back_populates='template_account'
,
cascade='all, delete-orphan' ,
uselist=False ,
)
branches = relationship("BranchAccount", back_populates="account")
splits = relationship( 'Split' ,
back_populates='account' ,
cascade='all, delete-orphan',
passive_deletes=True ,
collection_class=CallableList
)
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
