Hi,
I am wondering why a self referential query wont load in one query to avoid
round trips to the database, i have tried all the lazy loading options but
failed
--
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.
from __future__ import unicode_literals
import uuid
from enum import Enum
from sqlalchemy import Column , VARCHAR , ForeignKey , INTEGER
from sqlalchemy.orm import validates , relationship
from sqlalchemy_utils import ChoiceType
from .._common import CallableList
from .._declbase import DeclarativeBaseGuid
root_types = {"Root"}
asset_types = {'AReceivable' , 'Mutual' , 'Cash' , 'Asset' , 'Bank' , 'Stock' , 'Purchase'}
liability_types = {'Credit' , 'Liability' , 'APayable'}
income_types = {"Income" , "Sale"}
expense_types = {"Expense"}
trading_types = {'Trading'}
equity_types = {"Equity"}
# : the different types of accounts
ACCOUNT_TYPES = equity_types | income_types | expense_types | asset_types | liability_types | root_types | trading_types
class AccountType(Enum):
Root = "ROOT"
AReceivable = "RECEIVABLE"
Mutual = "MUTUAL"
Cash = "CASH"
Asset = "ASSET"
Bank = "BANK"
Stock = "STOCK"
Credit = "CREDIT"
Liability = "LIABILITY"
APayable = "PAYABLE"
Income = "INCOME"
Expense = "EXPENSE"
Trading = "TRADING"
Equity = "EQUITY"
Sale = "SALE"
Purcharse = "PURCHASE"
# types that are compatible with other types
incexp_types = income_types | expense_types
assetliab_types = asset_types | liability_types
# types according to the sign of their balance
positive_types = asset_types | expense_types | trading_types
negative_types = liability_types | income_types | equity_types
def get_account_sign_from_type(t):
return 1 if (t in positive_types) else -1
def type_checker(t):
if t in incexp_types:
return incexp_types
elif t in assetliab_types:
return assetliab_types
else:
return {t}
def _is_parent_child_types_consistent(type_parent, type_child, control_mode):
"""
Return True if the child account is consistent with the parent account in terms of types, i.e.:
1) if the parent is a root account, child can be anything but a root account
2) if the child is a root account, it must have no parent account
3) both parent and child are of the same family (asset, equity, income&expense, trading)
Arguments
type_parent(str): the type of the parent account
type_child(str): the type of the child account
Returns
True if both accounts are consistent, False otherwise
"""
if type_parent in root_types:
if "allow-root-subaccounts" in control_mode:
return type_child in ACCOUNT_TYPES
else:
return type_child in (ACCOUNT_TYPES - root_types)
if type_child in root_types:
return (type_parent is None) or ("allow-root-subaccounts" in control_mode)
if type_parent in type_checker( type_child ):
return True
return False
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
)
@property
def non_std_scu(self):
return self._non_std_scu
@property
def commodity_scu(self):
return self._commodity_scu
@property
def type(self):
return self._type.name
@type.setter
def type(self, value):
self._type = AccountType.__getitem__( value )
@commodity_scu.setter
def commodity_scu(self, value):
if value is None:
self._non_std_scu = 0
if self.commodity:
value = self.commodity.fraction
else:
value = 0
else:
self._non_std_scu = 1
self._commodity_scu = value
def __init__(self ,
name ,
type ,
commodity ,
parent=None ,
description='' ,
commodity_scu=0 ,
hidden=0 ,
placeholder=0 ,
code='' ,
book=None ,
children=None):
book = book or (commodity and commodity.book) or (parent and parent.book)
if not book:
raise ValueError("Could not find a book to attach the account to")
self.name = name
self.commodity = commodity
self.type = type
self.parent = parent
self.description = description
self.hidden = hidden
self.placeholder = placeholder
self.code = code
self.commodity_scu = commodity_scu
if children:
self.children[:] = children
def object_to_validate(self, change):
if change[-1] != "deleted":
yield self
def validate(self):
if self.type not in ACCOUNT_TYPES:
raise ValueError("Account_type '{}' is not in {}".format(self.type, ACCOUNT_TYPES))
if self.parent:
if not _is_parent_child_types_consistent(self.parent.type, self.type, self.book.control_mode):
raise ValueError("Child type '{}' is not consistent with parent type {}".format(
self.type, self.parent.type))
for acc in self.parent.children:
if acc.name == self.name and acc != self:
raise ValueError(
"{} has two children with the same name {} : {} and {}".format(self.parent, self.name,
self, acc))
else:
if self.type in root_types:
if self.name not in ['Template Root', 'Root Account']:
raise ValueError("{} is a root account but has a name = '{}'".format(self, self.name))
else:
raise ValueError("{} has no parent but is not a root account".format(self))
@validates('commodity')
def observe_commodity(self, key, value):
"""
Ensure update of commodity_scu when commodity is changed
"""
if value and (self.commodity_scu is None or self.non_std_scu == 0):
self.commodity_scu = value.fraction
return value
@property
def fullname(self):
if self.parent:
pfn = self.parent.fullname
if pfn:
return u"{}:{}".format(pfn, self.name)
else:
return self.name
else:
return u""
def get_balance(self, recurse=True, commodity=None, branch_id=None, start_date=None, stop_date=None):
if start_date and stop_date:
balance = sum([sp.value for sp in self.splits if
sp.transaction.branch_id == branch_id and start_date <= sp.transaction.post_date <= stop_date]) * self.sign
elif start_date and not stop_date:
balance = sum([sp.value for sp in self.splits if
sp.transaction.branch_id == branch_id and start_date <= sp.transaction.post_date]) * self.sign
elif stop_date and not start_date:
balance = sum([sp.value for sp in self.splits if
sp.transaction.branch_id == branch_id and sp.transaction.post_date <= stop_date]) * self.sig
else:
balance = sum([sp.value for sp in self.splits if sp.transaction.branch_id == branch_id]) * self.sign
if recurse and self.children:
balance += sum( acc.get_balance( recurse=recurse , commodity=commodity ,
branch_id=branch_id ,
start_date=start_date ,
stop_date=stop_date ) for acc in self.children )
return balance
@property
def sign(self):
return 1 if (self.type in positive_types) else -1
@property
def is_template(self):
return self.commodity.namespace == 'template'
def __unirepr__(self):
if self.commodity:
return u"Account<{acc.fullname}[{acc.commodity.mnemonic}]>".format(acc=self)
else:
return u"Account<{acc.fullname}>".format(acc=self)
def __lt__(self, other):
return self.name < other.name
