步骤 4：查询分类账中的表

在 Amazon QLDB 分类账中创建表格和加载数据后，您可运行查询以查看刚刚插入的车辆登记数据。QLDB 使用 PartiQL作为其查询语言，使用 Amazon Ion作为其面向文档的数据模型。

PartiQL 是开源、与 SQL 兼容的查询语言，现已扩展为可与 Ion 配合使用。使用 PartiQL，您可使用熟悉的 SQL 运算符插入、查询和管理数据。Amazon Ion 是 JSON 的超集。Ion 是基于文档的开源数据格式，可让您灵活地存储和处理结构化、半结构化和嵌套数据。

在此步骤中，您将使用 SELECT 语句从 vehicle-registration分类账中的表中读取数据。

警告

当你在没有索引查找的情况下运行查询时，它会调用全表扫描。PartiQL 之所以支持此类查询，是因为其与 SQL 兼容。但是，切勿在 QLDB 中对生产用例运行表扫描。这可能会导致大型表出现性能问题，包括并发冲突与事务超时。

为避免表扫描，必须在索引字段或文档 ID 上使用相等运算符 (WHERE indexedField = 123或WHERE indexedField IN (456, 789)) 运行带有WHERE谓词子句的语句。有关更多信息，请参阅优化查询性能。

查询表格

1. 编译并运行以下程序 (find_vehicles.py)，以查询分类账中某人名下注册的所有车辆。

   3.x

   # Copyright 2019 Amazon.com, Inc. or its affiliates. All Rights Reserved.
   # SPDX-License-Identifier: MIT-0
   #
   # Permission is hereby granted, free of charge, to any person obtaining a copy of this
   # software and associated documentation files (the "Software"), to deal in the Software
   # without restriction, including without limitation the rights to use, copy, modify,
   # merge, publish, distribute, sublicense, and/or sell copies of the Software, and to
   # permit persons to whom the Software is furnished to do so.
   #
   # THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
   # INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A
   # PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
   # HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
   # OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
   # SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
   #
   # This code expects that you have AWS credentials setup per:
   # https://boto3.amazonaws.com/v1/documentation/api/latest/guide/quickstart.html
   from logging import basicConfig, getLogger, INFO
   
   from pyqldbsamples.model.sample_data import get_document_ids, print_result, SampleData
   from pyqldbsamples.constants import Constants
   from pyqldbsamples.connect_to_ledger import create_qldb_driver
   
   logger = getLogger(__name__)
   basicConfig(level=INFO)
   
   
   def find_vehicles_for_owner(driver, gov_id):
       """
       Find vehicles registered under a driver using their government ID.
   
       :type driver: :py:class:`pyqldb.driver.qldb_driver.QldbDriver`
       :param driver: An instance of the QldbDriver class.
   
       :type gov_id: str
       :param gov_id: The owner's government ID.
       """
       document_ids = driver.execute_lambda(lambda executor: get_document_ids(executor, Constants.PERSON_TABLE_NAME,
                                                                              'GovId', gov_id))
   
       query = "SELECT Vehicle FROM Vehicle INNER JOIN VehicleRegistration AS r " \
               "ON Vehicle.VIN = r.VIN WHERE r.Owners.PrimaryOwner.PersonId = ?"
   
       for ids in document_ids:
           cursor = driver.execute_lambda(lambda executor: executor.execute_statement(query, ids))
           logger.info('List of Vehicles for owner with GovId: {}...'.format(gov_id))
           print_result(cursor)
   
   
   def main(ledger_name=Constants.LEDGER_NAME):
       """
       Find all vehicles registered under a person.
       """
       try:
           with create_qldb_driver(ledger_name) as driver:
               # Find all vehicles registered under a person.
               gov_id = SampleData.PERSON[0]['GovId']
               find_vehicles_for_owner(driver, gov_id)
       except Exception as e:
           logger.exception('Error getting vehicles for owner.')
           raise e
   
   
   if __name__ == '__main__':
       main()

   2.x

   # Copyright 2019 Amazon.com, Inc. or its affiliates. All Rights Reserved.
   # SPDX-License-Identifier: MIT-0
   #
   # Permission is hereby granted, free of charge, to any person obtaining a copy of this
   # software and associated documentation files (the "Software"), to deal in the Software
   # without restriction, including without limitation the rights to use, copy, modify,
   # merge, publish, distribute, sublicense, and/or sell copies of the Software, and to
   # permit persons to whom the Software is furnished to do so.
   #
   # THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
   # INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A
   # PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
   # HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
   # OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
   # SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
   #
   # This code expects that you have AWS credentials setup per:
   # https://boto3.amazonaws.com/v1/documentation/api/latest/guide/quickstart.html
   from logging import basicConfig, getLogger, INFO
   
   from pyqldbsamples.model.sample_data import get_document_ids, print_result, SampleData
   from pyqldbsamples.constants import Constants
   from pyqldbsamples.connect_to_ledger import create_qldb_session
   
   logger = getLogger(__name__)
   basicConfig(level=INFO)
   
   
   def find_vehicles_for_owner(transaction_executor, gov_id):
       """
       Find vehicles registered under a driver using their government ID.
   
       :type transaction_executor: :py:class:`pyqldb.execution.executor.Executor`
       :param transaction_executor: An Executor object allowing for execution of statements within a transaction.
   
       :type gov_id: str
       :param gov_id: The owner's government ID.
       """
       document_ids = get_document_ids(transaction_executor, Constants.PERSON_TABLE_NAME, 'GovId', gov_id)
   
       query = "SELECT Vehicle FROM Vehicle INNER JOIN VehicleRegistration AS r " \
               "ON Vehicle.VIN = r.VIN WHERE r.Owners.PrimaryOwner.PersonId = ?"
   
       for ids in document_ids:
           cursor = transaction_executor.execute_statement(query, ids)
           logger.info('List of Vehicles for owner with GovId: {}...'.format(gov_id))
           print_result(cursor)
   
   
   if __name__ == '__main__':
       """
       Find all vehicles registered under a person.
       """
       try:
           with create_qldb_session() as session:
               # Find all vehicles registered under a person.
               gov_id = SampleData.PERSON[0]['GovId']
               session.execute_lambda(lambda executor: find_vehicles_for_owner(executor, gov_id),
                                      lambda retry_attempt: logger.info('Retrying due to OCC conflict...'))
       except Exception:
           logger.exception('Error getting vehicles for owner.')

   注意

   首先，该程序在 Person 表格中查询文档GovId LEWISR261LL，以获取其id元数据字段。

   然后，它使用文档id，通过PrimaryOwner.PersonId作为外键查询VehicleRegistration表。它还结合VIN字段上的Vehicle表VehicleRegistration。

2. 要运行该程序，请输入以下命令。

   python find_vehicles.py

要了解如何修改 vehicle-registration 分类账表格中的文档，请参阅 步骤 5：修改分类账中的文档。