如何在字典中包含密钥之后求解KeyError

Question

我有一个字典列表，它具有以下值：

decisions = [{'name': 'BM CFTC', 'authority': 'CFTC', 'condition': 'Does CFTC Initial Margin apply', 'evaluated': True, 'applicable': False, 'key': '1bf12c0e-334f-4087-8dd1-183b0ea46234', 'result': True, 'status': 'GREEN', 'message': 'Product is out of CFTC scope', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': 'ForeignExchange', 'isdaBaseProduct': 'Forward', 'isdaSubProduct': '', 'isdaTransactionType': '', 'apdID': None, 'restrictionID': None, 'tenor': None, 'maxTenor': None}}
{'name': 'BM Prudential', 'authority': 'US_PRUDENTIAL', 'condition': 'Does Prudential Initial Margin apply', 'evaluated': True, 'applicable': False, 'key': '1bf12c0e-334f-4087-8dd1-183b0ea46234', 'result': True, 'status': 'GREEN', 'message': 'Product is out of SEC OR CFTC scope', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': 'ForeignExchange', 'isdaBaseProduct': 'Forward', 'isdaSubProduct': '', 'isdaTransactionType': '', 'apdID': None, 'restrictionID': None, 'tenor': None, 'maxTenor': None}}
{'name': 'SuitabilityAppropriateness', 'authority': 'ESMA', 'condition': 'Does ESMA Suitability Appropriateness compliant', 'evaluated': True, 'applicable': False, 'key': '1bf12c0e-334f-4087-8dd1-183b0ea46234', 'result': True, 'status': 'GREEN', 'message': 'Only Investment Firm is in scope', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': 'ForeignExchange', 'isdaBaseProduct': 'Forward', 'isdaSubProduct': '', 'isdaTransactionType': '', 'apdID': None, 'restrictionID': None, 'tenor': None, 'maxTenor': None}}
{'name': 'FirmQuote', 'authority': 'ESMA', 'condition': 'Does ESMA Firm Quote compliant', 'evaluated': True, 'applicable': False, 'key': '1bf12c0e-334f-4087-8dd1-183b0ea46234', 'result': True, 'status': 'GREEN', 'message': 'Product is out of scope', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': 'ForeignExchange', 'isdaBaseProduct': 'Forward', 'isdaSubProduct': '', 'isdaTransactionType': '', 'apdID': None, 'restrictionID': None, 'tenor': None, 'maxTenor': None}}
{'name': 'CFTC Business Conduct', 'authority': 'CFTC', 'condition': 'Does CFTC Business Conduct compliant', 'evaluated': True, 'applicable': False, 'key': '1bf12c0e-334f-4087-8dd1-183b0ea46234', 'result': True, 'status': 'GREEN', 'message': 'Exempt from Business Conduct due to Extraterritoriality (no USP nexus)', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': 'ForeignExchange', 'isdaBaseProduct': 'Forward', 'isdaSubProduct': '', 'isdaTransactionType': '', 'apdID': None, 'restrictionID': None, 'tenor': None, 'maxTenor': None}}
{'name': 'PreTradeTransparency', 'authority': 'ESMA', 'condition': 'Does ESMA Pre Trade Transparency compliant', 'evaluated': True, 'applicable': False, 'key': '1bf12c0e-334f-4087-8dd1-183b0ea46234', 'result': True, 'status': 'GREEN', 'message': 'Product is out of scope', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': 'ForeignExchange', 'isdaBaseProduct': 'Forward', 'isdaSubProduct': '', 'isdaTransactionType': '', 'apdID': None, 'restrictionID': None, 'tenor': None, 'maxTenor': None}}
{'name': 'BM ESMA', 'authority': 'ESMA', 'condition': 'Does ESMA Initial Margin apply', 'evaluated': True, 'applicable': False, 'key': '1bf12c0e-334f-4087-8dd1-183b0ea46234', 'result': True, 'status': 'GREEN', 'message': 'Product is out of ESMA scope', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': 'ForeignExchange', 'isdaBaseProduct': 'Forward', 'isdaSubProduct': '', 'isdaTransactionType': '', 'apdID': None, 'restrictionID': None, 'tenor': None, 'maxTenor': None}}
{'id': '1', 'name': 'LEI Check', 'group': 'COMPANY', 'authority': 'LBG', 'condition': 'Does client have a LEI', 'evaluated': True, 'applicable': True, 'key': 'LEI9', 'result': True, 'status': 'GREEN', 'message': 'Not applicable - Securities Conversion Exemption', 'override': False}
{'id': '3', 'name': 'Product to Booking Entity', 'group': 'PRODUCT', 'authority': 'LBG', 'evaluated': True, 'applicable': True, 'key': 'BEN5', 'result': False, 'status': 'RED', 'message': 'Product is Exposure Bearing, Client is RFI. Can only be offered by LBCM, LBCMW. If this trade is booked on LBCM or LBCMW, Check Client Onboarding Status', 'override': False}
{'id': '4', 'name': 'Product Approval', 'group': 'PRODUCT', 'authority': 'LBG', 'evaluated': True, 'applicable': True, 'key': 'BE3', 'result': True, 'status': 'GREEN', 'message': 'Approved Product', 'override': False}
{'id': '5', 'name': 'Underlying Product Checks', 'group': 'PRODUCT', 'authority': 'LBG', 'evaluated': True, 'applicable': True, 'key': 'PR1', 'result': True, 'status': 'GREEN', 'message': 'Product restrictions passed - A172R21326', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': None, 'isdaBaseProduct': None, 'isdaSubProduct': None, 'isdaTransactionType': None, 'apdID': None, 'restrictionID': 'A172R21326', 'tenor': 0.01642710472279261, 'maxTenor': 5.0}}
{'id': '8', 'name': 'Dodd Frank External Business Conduct', 'group': 'Regulatory Checks - Dodd Frank', 'authority': 'CFTC', 'evaluated': True, 'applicable': False, 'key': '1bf12c0e-334f-4087-8dd1-183b0ea46234', 'result': True, 'status': 'GREEN', 'message': 'Exempt from Business Conduct due to Extraterritoriality (no USP nexus)', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': 'ForeignExchange', 'isdaBaseProduct': 'Forward', 'isdaSubProduct': '', 'isdaTransactionType': '', 'apdID': None, 'restrictionID': None, 'tenor': None, 'maxTenor': None}}
{'id': '9', 'name': 'Dodd Frank Clearing', 'group': 'Regulatory Checks - Dodd Frank', 'authority': 'CFTC', 'evaluated': True, 'applicable': False, 'key': '1bf12c0e-334f-4087-8dd1-183b0ea46234', 'result': True, 'status': 'GREEN', 'message': 'Product is Excluded from CFTC scope', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': 'ForeignExchange', 'isdaBaseProduct': 'Forward', 'isdaSubProduct': '', 'isdaTransactionType': '', 'apdID': None, 'restrictionID': None, 'tenor': None, 'maxTenor': None}}
{'id': '10', 'name': 'Dodd Frank Swap Execution Facilities', 'group': 'Regulatory Checks - Dodd Frank', 'authority': 'CFTC', 'evaluated': True, 'applicable': False, 'key': '1bf12c0e-334f-4087-8dd1-183b0ea46234', 'result': True, 'status': 'GREEN', 'message': 'Product is Excluded from CFTC Scope', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': 'ForeignExchange', 'isdaBaseProduct': 'Forward', 'isdaSubProduct': '', 'isdaTransactionType': '', 'apdID': None, 'restrictionID': None, 'tenor': None, 'maxTenor': None}}
{'id': '11', 'name': 'EMIR Applicability', 'group': 'Regulatory Checks - EMIR', 'authority': 'ESMA', 'evaluated': True, 'applicable': False, 'key': '1bf12c0e-334f-4087-8dd1-183b0ea46234', 'result': True, 'status': 'GREEN', 'message': 'FX Spot transactions are excluded from EMIR scope', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': 'ForeignExchange', 'isdaBaseProduct': 'Forward', 'isdaSubProduct': '', 'isdaTransactionType': '', 'apdID': None, 'restrictionID': None, 'tenor': None, 'maxTenor': None}}
{'id': '12', 'name': 'EMIR Clearing', 'group': 'Regulatory Checks – EMIR', 'authority': 'ESMA', 'evaluated': True, 'applicable': False, 'key': '1bf12c0e-334f-4087-8dd1-183b0ea46234', 'result': True, 'status': 'GREEN', 'message': 'Product excluded from ESMA Clearing Mandate', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': 'ForeignExchange', 'isdaBaseProduct': 'Forward', 'isdaSubProduct': '', 'isdaTransactionType': '', 'apdID': None, 'restrictionID': None, 'tenor': None, 'maxTenor': None}}
{'id': '13', 'name': 'EMIR Uncleared Margin', 'group': 'Regulatory Checks – Uncleared Margin', 'authority': 'LBG', 'evaluated': True, 'applicable': False, 'key': '1bf12c0e-334f-4087-8dd1-183b0ea46234', 'result': True, 'status': 'GREEN', 'message': 'Product is out of ESMA scope', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': 'ForeignExchange', 'isdaBaseProduct': 'Forward', 'isdaSubProduct': '', 'isdaTransactionType': '', 'apdID': None, 'restrictionID': None, 'tenor': None, 'maxTenor': None}}
{'id': '14', 'name': 'US Prudential Uncleared Margin', 'group': 'Regulatory Checks – Uncleared Margin', 'authority': 'LBG', 'evaluated': True, 'applicable': False, 'key': '1bf12c0e-334f-4087-8dd1-183b0ea46234', 'result': True, 'status': 'GREEN', 'message': 'Product is out of SEC OR CFTC scope', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': 'ForeignExchange', 'isdaBaseProduct': 'Forward', 'isdaSubProduct': '', 'isdaTransactionType': '', 'apdID': None, 'restrictionID': None, 'tenor': None, 'maxTenor': None}}
{'id': '15', 'name': 'US CFTC Uncleared Margin', 'group': 'Regulatory Checks - Uncleared Margin', 'authority': 'LBG', 'evaluated': True, 'applicable': False, 'key': '1bf12c0e-334f-4087-8dd1-183b0ea46234', 'result': True, 'status': 'GREEN', 'message': 'Product is out of CFTC scope', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': 'ForeignExchange', 'isdaBaseProduct': 'Forward', 'isdaSubProduct': '', 'isdaTransactionType': '', 'apdID': None, 'restrictionID': None, 'tenor': None, 'maxTenor': None}}
{'id': '16', 'name': 'US Person Status', 'group': 'Regulatory Checks – Lloyds Internal Rules', 'authority': 'LBG', 'evaluated': True, 'applicable': True, 'key': '1bf12c0e-334f-4087-8dd1-183b0ea46234', 'result': True, 'status': 'GREEN', 'message': 'US Person status set', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': 'ForeignExchange', 'isdaBaseProduct': 'Forward', 'isdaSubProduct': '', 'isdaTransactionType': '', 'apdID': None, 'restrictionID': None, 'tenor': None, 'maxTenor': None}}
{'id': '17', 'name': 'Dodd Frank check for Special Entities', 'group': 'Regulatory Checks – Lloyds Internal Rules', 'authority': 'LBG', 'evaluated': True, 'applicable': True, 'key': '1bf12c0e-334f-4087-8dd1-183b0ea46234', 'result': True, 'status': 'GREEN', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': 'ForeignExchange', 'isdaBaseProduct': 'Forward', 'isdaSubProduct': '', 'isdaTransactionType': '', 'apdID': None, 'restrictionID': None, 'tenor': None, 'maxTenor': None}}
{'id': '18', 'name': 'Booking Cleared Trades on BoS', 'group': 'Regulatory Checks – Lloyds Internal Rules', 'authority': 'LBG', 'evaluated': True, 'applicable': True, 'key': '1bf12c0e-334f-4087-8dd1-183b0ea46234', 'result': True, 'status': 'GREEN', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': 'ForeignExchange', 'isdaBaseProduct': 'Forward', 'isdaSubProduct': '', 'isdaTransactionType': '', 'apdID': None, 'restrictionID': None, 'tenor': None, 'maxTenor': None}}
{'id': '19', 'name': 'Credit Default Swaps with Non Swap Dealers', 'group': 'Regulatory Checks – Lloyds Internal Rules', 'authority': 'LBG', 'evaluated': True, 'applicable': True, 'key': '1bf12c0e-334f-4087-8dd1-183b0ea46234', 'result': True, 'status': 'GREEN', 'override': False, 'facts': {'isdaID': None, 'isdaAssetClass': 'ForeignExchange', 'isdaBaseProduct': 'Forward', 'isdaSubProduct': '', 'isdaTransactionType': '', 'apdID': None, 'restrictionID': None, 'tenor': None, 'maxTenor': None}}
{'id': '24', 'name': 'MIFID Client Classification check', 'group': 'Regulatory Checks - MIFID2', 'authority': 'ESMA', 'evaluated': True, 'applicable': True, 'key': 'MCC3', 'result': True, 'status': 'GREEN', 'message': 'MIFID Client Classification in place', 'override': False}
{'id': '27', 'name': 'MIFID2 Quote Transparency', 'group': 'Regulatory Checks - MIFID2', 'authority': 'LBG', 'condition': 'Does Quote Transparency apply', 'evaluated': True, 'applicable': False, 'key': 'SI-1.0', 'result': True, 'status': 'GREEN', 'message': 'Only Trades from MM-FI and ION are in scope for Pre-Trade Transparency', 'override': False}
{'id': '28', 'name': 'MIFID2 Derivatives Trading Obligations', 'group': 'Regulatory Checks - MIFID2', 'authority': 'ESMA', 'condition': 'Is DTO mandated', 'evaluated': True, 'applicable': True, 'key': 'DTO-2', 'result': True, 'status': 'GREEN', 'message': 'Product not in scope of DTO.', 'override': False}
{'id': '29', 'name': 'MIFID2 Client Consent outside of Trading Venue', 'group': 'Regulatory Checks - MIFID2', 'authority': 'ESMA', 'evaluated': True, 'applicable': True, 'key': 'CCT4', 'result': True, 'status': 'GREEN', 'message': 'Client is not a Retail client. Consent is deemed', 'override': False}
{'id': '30', 'name': 'MIFID2 Terms of Business', 'group': 'Regulatory Checks - MIFID2', 'authority': 'ESMA', 'evaluated': True, 'applicable': True, 'key': 'TOB3', 'result': True, 'status': 'GREEN', 'message': 'Terms of Business sent', 'override': False}
{'id': '32', 'name': 'Ring Fencing Conditional Product Checks', 'group': 'Ring Fencing', 'authority': 'LBG', 'condition': 'Have RF Checks Passed', 'evaluated': True, 'applicable': True, 'key': 'RFC2', 'result': True, 'status': 'GREEN', 'message': 'Not a Conditional Product', 'override': False}
{'id': '33', 'name': 'Ring Fencing RFI Checks', 'group': 'Ring Fencing', 'authority': 'LBG', 'condition': 'Have RFI Checks Passed', 'evaluated': True, 'applicable': True, 'key': 'RFI4', 'result': False, 'status': 'RED', 'message': 'Cannot trade an Exposure Bearing Product with a Relevant Financial Institution (RFI)', 'override': False}]

下面是我的代码：

for product in decisions:
    if product['id'] == '17':
       print(product['name'])

请帮助我如何摆脱这个问题，以及如何处理KeyError。

上面是抛出KeyError：'id‘

Answer 1

正如@Charlieclark所提到的，您需要用cell.offset()更改append()中的内容。所以这两个代码应该是这样的..。

import openpyxl
wb = openpyxl.load_workbook(file)
summary_list = []
for sheet in wb.worksheets:
    for row in sheet.iter_rows(min_row=1,max_row=22,min_col=16, max_col=16):
        for cell in row:
            if cell.value == "No Match":
                summary_list.append(cell.offset(0,-4).value) #column L value - offset by minus 4
                summary_list.append(cell.offset(0,-9).value) #column G value - offset by minus 9
#                summary_list.append(sheet.title)
#                summary_list.append(cell.value)