def toggle_order_shipped(self):
""" toggle Shipped Date, to trigger balance adjustment """
""" also test join.
session.query(Customer).join(Invoice).filter(Invoice.amount == 8500).all()
"""
pre_cust = session.query(models.Customer).filter(models.Customer.Id == "ALFKI").one()
session.expunge(pre_cust)
print("")
test_order = session.query(models.Order).filter(models.Order.Id == 11011).join(models.Employee).one()
if test_order.ShippedDate is None or test_order.ShippedDate == "":
# with restored db, cust[ALFKI] has bal 960 & 3 unpaid orders, Order[11011) is 960, unshipped
test_order.ShippedDate = str(datetime.now())
print(prt("Shipping order - ShippedDate: ['' -> " + test_order.ShippedDate + "]" +
" for customer balance: " + str(pre_cust.Balance) +
", with UnpaidOrderCount: " + str(pre_cust.UnpaidOrderCount)))
else:
test_order.ShippedDate = None
print(prt("Returning order - ShippedDate: [ -> None]"))
# session.commit()
print("")
post_cust = session.query(models.Customer).filter(models.Customer.Id == "ALFKI").one()
logic_row = LogicRow(row=post_cust, old_row=pre_cust, ins_upd_dlt="*", nest_level=0, a_session=session, row_sets=None)
if abs(post_cust.Balance - pre_cust.Balance) == 960:
logic_row.log("Correct adjusted Customer Result")
assert True
else:
self.fail(logic_row.log("Incorrect adjusted Customer Result - expected 960 difference"))
def test_run(self):
self.toggle_order_shipped()
pre_adjusted_product = session.query(
models.Product).filter(models.Product.Id == 58).one()
session.expunge(pre_adjusted_product)
print("\nNow delete the Customer...")
delete_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
session.delete(delete_cust)
session.commit()
post_adjusted_product = session.query(
models.Product).filter(models.Product.Id == 58).one()
logic_row = LogicRow(row=post_adjusted_product,
old_row=pre_adjusted_product,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
if post_adjusted_product.UnitsShipped == pre_adjusted_product.UnitsShipped - 40:
logic_row.log("Product adjusted properly on delete customer")
else:
logic_row.log("Product adjusted improperly on delete customer")
assert False, "Product adjusted improperly on delete customer"
print("\nCustomer deleted - check log")
self.assertTrue(True)
def toggle_order_shipped(self):
"""
toggle Shipped Date, to trigger
* balance adjustment
* cascade to OrderDetails
* and Product adjustment
also test join
"""
pre_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
session.expunge(pre_cust)
print("")
test_order = session.query(models.Order).filter(models.Order.Id == 11011).\
join(models.Employee).options(joinedload(models.Order.SalesRep)).\
one()
if test_order.ShippedDate is None or test_order.ShippedDate == "":
test_order.ShippedDate = str(datetime.now())
print(
prt("Shipping order - ShippedDate: ['' -> " +
test_order.ShippedDate + "]"))
else:
test_order.ShippedDate = None
print(prt("Returning order - ShippedDate: [ -> None]"))
session.commit()
print("")
post_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
logic_row = LogicRow(row=post_cust,
old_row=pre_cust,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
if abs(post_cust.Balance - pre_cust.Balance) == 960:
logic_row.log("Correct adjusted Customer Result")
assert True
else:
self.fail(
logic_row.log(
"ERROR - incorrect adjusted Customer Result (not 960 delta)"
))
if post_cust.Balance == 56:
pass
else:
self.fail(logic_row.log("ERROR - balance should be 56"))
if post_cust.UnpaidOrderCount == 3 and pre_cust.UnpaidOrderCount == 4:
pass
else:
self.fail(logic_row.log("Error - UnpaidOrderCount should be 3"))
def test_run(self):
""" test class <> table name """
pre_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
session.expunge(pre_cust)
print("")
test_order = session.query(models.OrderClass).filter(
models.OrderClass.Id == 11011).join(models.Employee).one()
if test_order.RequiredDate is None or test_order.RequiredDate == "":
test_order.RequiredDate = str(datetime.now())
print(
prt("Shipping order - RequiredDate: ['' -> " +
test_order.RequiredDate + "]"))
else:
test_order.RequiredDate = None
print(prt("Returning order - RequiredDate: [ -> None]"))
session.commit()
print("")
post_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
logic_row = LogicRow(row=pre_cust,
old_row=post_cust,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
# logic_row.row.Balance = 10 # force failure
if abs(post_cust.Balance - pre_cust.Balance) == 0:
logic_row.log("Correct non-adjusted Customer Result")
assert True
else:
self.fail(
logic_row.log(
"Incorrect adjusted Customer Result - expected no difference"
))
print("\nupd_order_required, ran to completion")
def toggle_order_shipped(self):
""" toggle Shipped Date, to trigger balance adjustment """
pre_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
pre_adjusted_product = session.query(
models.Product).filter(models.Product.Id == 58).one()
session.expunge(pre_cust)
session.expunge(pre_adjusted_product)
print("")
test_order = session.query(models.Order).filter(
models.Order.Id == 11011).join(models.Employee).one()
if test_order.ShippedDate is None or test_order.ShippedDate == "":
test_order.ShippedDate = str(datetime.now())
print(
prt("Shipping order - ShippedDate: ['' -> " +
test_order.ShippedDate + "]"))
else:
test_order.ShippedDate = None
print(prt("Returning order - ShippedDate: [ -> None]"))
session.commit()
print("")
post_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
logic_row = LogicRow(row=post_cust,
old_row=pre_cust,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
if abs(post_cust.Balance - pre_cust.Balance) == 960:
logic_row.log("Correct adjusted Customer Result")
assert True
else:
self.fail(
logic_row.log(
"Incorrect adjusted Customer Result - expected 960 difference"
))
if post_cust.Balance == 56:
pass
else:
self.fail(logic_row.log("ERROR - balance should be 56"))
if post_cust.UnpaidOrderCount == 3 and pre_cust.UnpaidOrderCount == 4:
pass
else:
self.fail(logic_row.log("Error - UnpaidOrderCount should be 2"))
post_adjusted_product = session.query(
models.Product).filter(models.Product.Id == 58).one()
logic_row = LogicRow(row=post_adjusted_product,
old_row=pre_adjusted_product,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
if post_adjusted_product.UnitsShipped == pre_adjusted_product.UnitsShipped + 40:
logic_row.log("Product adjusted properly on ship order")
else:
self.fail(
logic_row.log("Product adjusted improperly on ship order"))
def test_run(self):
"""
Illustrate re-use with a number of changes:
1 - reassign the order to a different customer
2 - change an OrderDetail (eg, "I'll buy 1 WidgetSet, not 5 Widgets")
a. A different Product
b. A different Quantity
"""
pre_alfki = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
pre_anatr = session.query(
models.Customer).filter(models.Customer.Id == "ANATR").one()
logic_row = LogicRow(row=pre_alfki,
old_row=pre_alfki,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
logic_row.log("starting")
logic_row = LogicRow(row=pre_anatr,
old_row=pre_anatr,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
logic_row.log("starting")
pre_order = session.query(models.Order).filter(
models.Order.Id == 11011).one() # type : Order
logic_row = LogicRow(row=pre_order,
old_row=pre_order,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
logic_row.log("starting")
session.expunge(pre_alfki)
session.expunge(pre_anatr)
session.expunge(pre_order)
print("")
test_order = session.query(models.Order).filter(
models.Order.Id == 11011).one() # type : Order
test_order_details = test_order.OrderDetailList
changed_order_detail = None
for each_order_detail in test_order_details:
if each_order_detail.ProductId == 58: # Escargots de Bourgogne, @ $13.25
each_order_detail.ProductId = 48 # Chocolade, @ $12.75
each_order_detail.Quantity = 10 # 40 x 13.25 => 10 x 12.75
break
pre_amount_total = test_order.AmountTotal
post_amount_total = pre_amount_total -\
Decimal(40.0) * Decimal(13.25) +\
Decimal(10.0) * Decimal(12.75)
test_order.CustomerId = "ANATR"
print("\n" + prt("Reparenting *altered* order - new CustomerId: " +
test_order.CustomerId))
print(
f'order amount {pre_amount_total} projected to be {post_amount_total}'
)
session.commit()
print('')
msg = 'Committed... order.amountTotal ' + \
str(pre_amount_total) + ' -> ' + \
str(post_amount_total)
logic_row = LogicRow(row=test_order,
old_row=pre_order,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
logic_row.log(msg)
print("\n")
post_alfki = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
logic_row = LogicRow(row=post_alfki,
old_row=pre_alfki,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
if post_alfki.Balance == 56:
logic_row.log("Correct non-adjusted Customer Result")
assert True
else:
msg = "ERROR - incorrect adjusted Customer Result, " + "should be 56"
self.fail(logic_row.log(msg))
post_anatr = session.query(
models.Customer).filter(models.Customer.Id == "ANATR").one()
logic_row = LogicRow(row=post_anatr,
old_row=pre_anatr,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
if post_anatr.Balance == 557.50:
logic_row.log("Correct non-adjusted Customer Result")
assert True
else:
print("\n*** ERROR***")
msg = "ERROR - incorrect adjusted Customer Result, " + "should be 557.50"
self.fail(logic_row.log(msg))
print("\nupd_order_customer_reuse, ran to completion")
self.assertTrue(True)
"""
def toggle_order_shipped(self):
"""
toggle Shipped Date, to trigger
* balance adjustment
* cascade to OrderDetails
* and Product adjustment
also test join
"""
# fails on post_cust with TypeError: my_load_listener() got an unexpected keyword argument 'attrs'
# @sqlalchemy.event.listens_for(sqlalchemy.orm.Mapper, 'refresh', named=True)
def my_load_listener(target, context):
print("on load!")
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
print(f'type(Base): {type(Base)}'
) # sqlalchemy.ext.declarative.api.DeclarativeMeta
do_listen = False
if do_listen:
event.listen(
sqlalchemy.ext.declarative.api.
DeclarativeMeta, # models.Customer is fine
'load',
my_load_listener)
# , propagate=True) # TypeError: descriptor '__subclasses__' of 'type' object needs an argument
pre_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
supers = pre_cust.__class__.__bases__
for each_super in supers:
print(f'pre_cust super: {type(each_super)}')
session.expunge(pre_cust)
print("")
test_order = session.query(models.Order).filter(models.Order.Id == 11011).\
join(models.Employee).options(joinedload(models.Order.SalesRep)).\
one()
if test_order.ShippedDate is None or test_order.ShippedDate == "":
test_order.ShippedDate = str(datetime.now())
print(
prt("Shipping order - ShippedDate: ['' -> " +
test_order.ShippedDate + "]"))
else:
test_order.ShippedDate = None
print(prt("Returning order - ShippedDate: [ -> None]"))
session.commit()
print("")
post_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
logic_row = LogicRow(row=post_cust,
old_row=pre_cust,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
if abs(post_cust.Balance - pre_cust.Balance) == 960:
logic_row.log("Correct adjusted Customer Result")
assert True
else:
self.fail(
logic_row.log(
"ERROR - incorrect adjusted Customer Result (not 960 delta)"
))
if post_cust.Balance == 56:
pass
else:
self.fail(logic_row.log("ERROR - balance should be 56"))
if post_cust.UnpaidOrderCount == 3 and pre_cust.UnpaidOrderCount == 4:
pass
else:
self.fail(logic_row.log("Error - UnpaidOrderCount should be 3"))
def test_run(self):
pre_alfki = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
pre_anatr = session.query(
models.Customer).filter(models.Customer.Id == "ANATR").one()
session.expunge(pre_alfki)
session.expunge(pre_anatr)
if pre_alfki.Balance != 1016:
self.fail(
"pre_alfki balance not 1016 (database-gold not copied?), value: "
+ str(pre_alfki.Balance))
print("")
test_order = session.query(models.Order).filter(
models.Order.Id == 11011).one() # type : Order
amount_total = test_order.AmountTotal
if test_order.CustomerId == "ALFKI":
test_order.CustomerId = "ANATR"
else:
test_order.CustomerId = "ALFKI"
print(
prt("Reparenting order - new CustomerId: " +
test_order.CustomerId))
session.commit()
print("")
post_alfki = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
logic_row = LogicRow(row=post_alfki,
old_row=pre_alfki,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
if abs(post_alfki.Balance - pre_alfki.Balance) == 960:
logic_row.log("Correct adjusted Customer Result")
pass
else:
self.fail(
logic_row.log(
"Incorrect adjusted Customer Result - expected 960 difference"
))
post_anatr = session.query(
models.Customer).filter(models.Customer.Id == "ANATR").one()
logic_row = LogicRow(row=post_anatr,
old_row=pre_anatr,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
if abs(post_anatr.Balance - pre_anatr.Balance) == 960:
logic_row.log("Correct adjusted Customer Result")
pass
else:
self.fail(
logic_row.log(
"Incorrect adjusted Customer Result - expected 960 difference"
))
print("\nupd_order_customer, ran to completion")
self.assertTrue(True)
def test_run(self):
"""
Test 1 - toggle Due Date, to verify no effect on Customer, OrderDetails
Also test join.
"""
pre_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
session.expunge(pre_cust)
print("")
test_order = session.query(models.Order).filter(
models.Order.Id == 11011).join(models.Employee).one()
if test_order.RequiredDate is None or test_order.RequiredDate == "":
test_order.RequiredDate = str(datetime.now())
print(
prt("Shipping order - RequiredDate: ['' -> " +
test_order.RequiredDate + "]"))
else:
test_order.RequiredDate = None
print(prt("Returning order - RequiredDate: [ -> None]"))
session.commit()
print("")
post_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
logic_row = LogicRow(row=pre_cust,
old_row=post_cust,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
# logic_row.row.Balance = 0 # force error (for debug)
if abs(post_cust.Balance - pre_cust.Balance) == 0:
logic_row.log("Correct non-adjusted Customer Result")
self.assertTrue(True)
else:
self.fail(
logic_row.log(
"\nIncorrect adjusted Customer Result - Balance changed"))
"""
Test 2 - Invalid SalesRep
"""
pre_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
session.expunge(pre_cust)
print("")
test_order = session.query(models.Order).filter(
models.Order.Id == 11011).join(models.Employee).one()
if test_order.RequiredDate is None or test_order.RequiredDate == "":
test_order.RequiredDate = str(datetime.now())
print(
prt("Shipping order - RequiredDate: ['' -> " +
test_order.RequiredDate + "]"))
else:
test_order.RequiredDate = None
print(prt("Returning order - RequiredDate: [ -> None]"))
test_order.EmployeeId = 11 # should provoke error - EmployeeId does not exist
try:
session.commit()
except ConstraintException as ce:
print("Expected constraint: " + str(ce))
session.rollback()
did_fail_as_expected = True
except:
self.fail("Unexpected Exception Type")
if not did_fail_as_expected:
self.fail("huge order expected to fail, but succeeded")
else:
print("\n" + prt(
"huge order failed credit check as expected. Now trying non-commissioned order, should also fail..."
))
print("")
post_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
logic_row = LogicRow(row=pre_cust,
old_row=post_cust,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
# logic_row.row.Balance = 0 # force error (for debug)
if abs(post_cust.Balance - pre_cust.Balance) == 0:
logic_row.log("Correct non-adjusted Customer Result")
self.assertTrue(True)
else:
self.fail(
logic_row.log(
"\nIncorrect adjusted Customer Result - Balance changed"))
print("\nupd_order_required, ran to completion")
self.assertTrue(True)
def test_run(self):
pre_cust = session.query(models.Customer).filter(models.Customer.Id == "ALFKI").one()
session.expunge(pre_cust)
"""
Test 1 - should fail due to credit limit exceeded
"""
bad_order = models.Order(AmountTotal=0, CustomerId="ALFKI", ShipCity="Richmond",
EmployeeId=6, Freight=1)
session.add(bad_order)
# OrderDetails - https://docs.sqlalchemy.org/en/13/orm/backref.html
bad_item1 = models.OrderDetail(ProductId=1, Amount=0,
Quantity=1, UnitPrice=18,
Discount=0)
bad_order.OrderDetailList.append(bad_item1)
bad_item2 = models.OrderDetail(ProductId=2, Amount=0,
Quantity=20000, UnitPrice=18,
Discount=0)
bad_order.OrderDetailList.append(bad_item2)
did_fail_as_expected = False
try:
session.commit()
except ConstraintException as ce:
print("Expected constraint: " + str(ce))
session.rollback()
did_fail_as_expected = True
except:
self.fail("Unexpected Exception Type")
if not did_fail_as_expected:
self.fail("huge order expected to fail, but succeeded")
else:
print("\n" + prt("huge order failed credit check as expected. Now trying non-commissioned order, should also fail..."))
"""
Test 2 - should fail due to not-commissioned
"""
bad_order = models.Order(AmountTotal=0, CustomerId="ALFKI", ShipCity="Richmond",
EmployeeId=2, Freight=1)
session.add(bad_order)
# OrderDetails - https://docs.sqlalchemy.org/en/13/orm/backref.html
bad_item1 = models.OrderDetail(ProductId=1, Amount=0,
Quantity=1, UnitPrice=18,
Discount=0)
bad_order.OrderDetailList.append(bad_item1)
bad_item2 = models.OrderDetail(ProductId=2, Amount=0,
Quantity=2, UnitPrice=18,
Discount=0)
bad_order.OrderDetailList.append(bad_item2)
did_fail_as_expected = False
try:
session.commit()
except ConstraintException:
session.rollback()
did_fail_as_expected = True
except:
print("Unexpected Exception Type")
if not did_fail_as_expected:
self.fail("order for non-commissioned expected to fail, but succeeded")
else:
print("\n" + prt("non-commissioned order failed constraint as expected. Now trying invalid customer, should fail..."))
"""
Test 3 - should fail due to invalid customer
"""
bad_order = models.Order(AmountTotal=0, CustomerId="XX INVALID CUSTOMER", ShipCity="Richmond",
EmployeeId=6, Freight=1)
session.add(bad_order)
# OrderDetails - https://docs.sqlalchemy.org/en/13/orm/backref.html
bad_item1 = models.OrderDetail(ProductId=1, Amount=0,
Quantity=1, UnitPrice=18,
Discount=0)
bad_order.OrderDetailList.append(bad_item1)
bad_item2 = models.OrderDetail(ProductId=2, Amount=0,
Quantity=2, UnitPrice=18,
Discount=0)
bad_order.OrderDetailList.append(bad_item2)
did_fail_as_expected = False
try:
session.commit()
except ConstraintException as ce:
session.rollback()
did_fail_as_expected = True
print(str(ce))
except:
session.rollback()
did_fail_as_expected = True
e = sys.exc_info()[0]
print(e)
if not did_fail_as_expected:
self.fail("order for invalid customer expected to fail, but succeeded")
else:
print("\n" + prt("invalid customer failed as expected. Now trying valid order, should succeed..."))
"""
Test 4 - should succeed
"""
new_order = models.Order(AmountTotal=0, CustomerId="ALFKI", ShipCity="Richmond",
EmployeeId=6, Freight=1)
session.add(new_order)
# OrderDetails - https://docs.sqlalchemy.org/en/13/orm/backref.html
new_item1 = models.OrderDetail(ProductId=1, Amount=0,
Quantity=1, UnitPrice=18,
Discount=0)
new_order.OrderDetailList.append(new_item1)
new_item2 = models.OrderDetail(ProductId=2, Amount=0,
Quantity=2, UnitPrice=18,
Discount=0)
new_order.OrderDetailList.append(new_item2)
session.commit()
post_cust = session.query(models.Customer).filter(models.Customer.Id == "ALFKI").one()
print("\nadd_order, update completed - analyzing results..\n\n")
row_prt(new_order, session, "\nnew Order Result") # $18 + $38 = $56
if new_order.AmountTotal != 56:
self.fail(row_prt(new_order, "Unexpected AmountTotal: " + str(new_order.AmountTotal) +
"... expected 56"))
row_prt(new_item1, session, "\nnew Order Detail 1 Result") # 1 Chai @ $18
row_prt(new_item2, session, "\nnew Order Detail 2 Result") # 2 Chang @ $19 = $38
logic_row = LogicRow(row=post_cust, old_row=pre_cust, ins_upd_dlt="*", nest_level=0, a_session=session, row_sets=None)
if post_cust.Balance == pre_cust.Balance + 56:
logic_row.log("Correct adjusted Customer Result")
assert True
else:
self.fail(logic_row.log("ERROR - incorrect adjusted Customer Result"))
if post_cust.OrderCount == pre_cust.OrderCount + 1 and\
post_cust.UnpaidOrderCount == pre_cust.UnpaidOrderCount + 1:
pass
else:
self.fail(logic_row.log("Error - unexpected OrderCounts - did not increase by 1"))
from sqlalchemy.sql import func
qry = session.query(models.Order.CustomerId,
func.sum(models.Order.AmountTotal).label('sql_balance'))\
.filter(models.Order.CustomerId == "ALFKI", models.Order.ShippedDate == None)
qry = qry.group_by(models.Order.CustomerId).one()
if qry.sql_balance == post_cust.Balance:
logic_row.log("Derived balance matches sql `select sum' result: " + str(post_cust.Balance))
else:
self.fail(logic_row.log("ERROR - computed balance does not match sql result"))
print("\nadd_order, ran to completion\n\n")
self.assertTrue(True)