翻訳は機械翻訳により提供されています。提供された翻訳内容と英語版の間で齟齬、不一致または矛盾がある場合、英語版が優先します。 # ステップ 3: テーブル、インデックス、およびサンプルデータを作成する **重要** サポート終了通知: 既存のお客様は、07/31/2025 のサポート終了まで Amazon QLDB を使用できます。詳細については、[「Amazon QLDB 台帳を Amazon Aurora PostgreSQL に移行する](https://aws.amazon.com/blogs/database/migrate-an-amazon-qldb-ledger-to-amazon-aurora-postgresql/)」を参照してください。 Amazon QLDB 台帳がアクティブになり、接続を受け入れると、車両、車両の所有者、登録情報に関するデータのテーブルを作成できるようになります。テーブルとインデックスを作成した後、これらにデータをロードできます。 このステップでは、`vehicle-registration` 台帳に 4 つのテーブルを作成します。 + `VehicleRegistration` + `Vehicle` + `Person` + `DriversLicense` 以下のインデックスも作成します。 **** | テーブル名 | フィールド | | --- | --- | | VehicleRegistration | VIN | | VehicleRegistration | LicensePlateNumber | | Vehicle | VIN | | Person | GovId | | DriversLicense | LicenseNumber | | DriversLicense | PersonId | サンプルデータを挿入するときは、まず `Person` テーブルにドキュメントを挿入します。次に、各 `Person` ドキュメントからシステムによって割り当てられた `id` を使用して、適切な `VehicleRegistration` および `DriversLicense` ドキュメントの対応するフィールドに入力します。 **ヒント** ベストプラクティスとして、外部キーには、ドキュメントにシステムによって割り当てられた `id` を使用します。一意の識別子 (車両の VIN など) 用にフィールドを定義することはできますが、実際のドキュメントの一意の識別子はその `id` です。このフィールドはドキュメントのメタデータに含まれており、コミット済みビュー (*テーブルのシステム定義のビュー*) でクエリを実行できます。 QLDB におけるビューの詳細については、「[重要な概念](ledger-structure.md)」を参照してください。メタデータの詳細については、「[ドキュメントのメタデータのクエリの実行](working.metadata.md)」を参照してください。 **テーブルとインデックスを作成するには** 1. 次のプログラム (`create_table.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.constants import Constants from pyqldbsamples.connect_to_ledger import create_qldb_driver logger = getLogger(__name__) basicConfig(level=INFO) def create_table(driver, table_name): """ Create a table with the specified name. :type driver: :py:class:`pyqldb.driver.qldb_driver.QldbDriver` :param driver: An instance of the QldbDriver class. :type table_name: str :param table_name: Name of the table to create. :rtype: int :return: The number of changes to the database. """ logger.info("Creating the '{}' table...".format(table_name)) statement = 'CREATE TABLE {}'.format(table_name) cursor = driver.execute_lambda(lambda executor: executor.execute_statement(statement)) logger.info('{} table created successfully.'.format(table_name)) return len(list(cursor)) def main(ledger_name=Constants.LEDGER_NAME): """ Create registrations, vehicles, owners, and licenses tables. """ try: with create_qldb_driver(ledger_name) as driver: create_table(driver, Constants.DRIVERS_LICENSE_TABLE_NAME) create_table(driver, Constants.PERSON_TABLE_NAME) create_table(driver, Constants.VEHICLE_TABLE_NAME) create_table(driver, Constants.VEHICLE_REGISTRATION_TABLE_NAME) logger.info('Tables created successfully.') except Exception as e: logger.exception('Errors creating tables.') 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.constants import Constants from pyqldbsamples.connect_to_ledger import create_qldb_session logger = getLogger(__name__) basicConfig(level=INFO) def create_table(transaction_executor, table_name): """ Create a table with the specified name using an Executor object. :type transaction_executor: :py:class:`pyqldb.execution.executor.Executor` :param transaction_executor: An Executor object allowing for execution of statements within a transaction. :type table_name: str :param table_name: Name of the table to create. :rtype: int :return: The number of changes to the database. """ logger.info("Creating the '{}' table...".format(table_name)) statement = 'CREATE TABLE {}'.format(table_name) cursor = transaction_executor.execute_statement(statement) logger.info('{} table created successfully.'.format(table_name)) return len(list(cursor)) if __name__ == '__main__': """ Create registrations, vehicles, owners, and licenses tables in a single transaction. """ try: with create_qldb_session() as session: session.execute_lambda(lambda x: create_table(x, Constants.DRIVERS_LICENSE_TABLE_NAME) and create_table(x, Constants.PERSON_TABLE_NAME) and create_table(x, Constants.VEHICLE_TABLE_NAME) and create_table(x, Constants.VEHICLE_REGISTRATION_TABLE_NAME), lambda retry_attempt: logger.info('Retrying due to OCC conflict...')) logger.info('Tables created successfully.') except Exception: logger.exception('Errors creating tables.') ``` ------ **注記** このプログラムは、`execute_lambda` 関数を使用する方法を示しています。この例では、1 つの Lambda 式で複数の `CREATE TABLE` PartiQL ステートメントを実行します。 この execute 関数は、暗黙的にトランザクションを開始し、Lambda ですべてのステートメントを実行して、トランザクションを自動コミットします。 1. このプログラムを実行するには、次のコマンドを入力します。 ``` python create_table.py ``` 1. 次のプログラム (`create_index.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.constants import Constants from pyqldbsamples.connect_to_ledger import create_qldb_driver logger = getLogger(__name__) basicConfig(level=INFO) def create_index(driver, table_name, index_attribute): """ Create an index for a particular table. :type driver: :py:class:`pyqldb.driver.qldb_driver.QldbDriver` :param driver: An instance of the QldbDriver class. :type table_name: str :param table_name: Name of the table to add indexes for. :type index_attribute: str :param index_attribute: Index to create on a single attribute. :rtype: int :return: The number of changes to the database. """ logger.info("Creating index on '{}'...".format(index_attribute)) statement = 'CREATE INDEX on {} ({})'.format(table_name, index_attribute) cursor = driver.execute_lambda(lambda executor: executor.execute_statement(statement)) return len(list(cursor)) def main(ledger_name=Constants.LEDGER_NAME): """ Create indexes on tables in a particular ledger. """ logger.info('Creating indexes on all tables...') try: with create_qldb_driver(ledger_name) as driver: create_index(driver, Constants.PERSON_TABLE_NAME, Constants.GOV_ID_INDEX_NAME) create_index(driver, Constants.VEHICLE_TABLE_NAME, Constants.VEHICLE_VIN_INDEX_NAME) create_index(driver, Constants.VEHICLE_REGISTRATION_TABLE_NAME, Constants.LICENSE_PLATE_NUMBER_INDEX_NAME) create_index(driver, Constants.VEHICLE_REGISTRATION_TABLE_NAME, Constants.VEHICLE_VIN_INDEX_NAME) create_index(driver, Constants.DRIVERS_LICENSE_TABLE_NAME, Constants.PERSON_ID_INDEX_NAME) create_index(driver, Constants.DRIVERS_LICENSE_TABLE_NAME, Constants.LICENSE_NUMBER_INDEX_NAME) logger.info('Indexes created successfully.') except Exception as e: logger.exception('Unable to create indexes.') 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.constants import Constants from pyqldbsamples.connect_to_ledger import create_qldb_session logger = getLogger(__name__) basicConfig(level=INFO) def create_index(transaction_executor, table_name, index_attribute): """ Create an index for a particular table. :type transaction_executor: :py:class:`pyqldb.execution.executor.Executor` :param transaction_executor: An Executor object allowing for execution of statements within a transaction. :type table_name: str :param table_name: Name of the table to add indexes for. :type index_attribute: str :param index_attribute: Index to create on a single attribute. :rtype: int :return: The number of changes to the database. """ logger.info("Creating index on '{}'...".format(index_attribute)) statement = 'CREATE INDEX on {} ({})'.format(table_name, index_attribute) cursor = transaction_executor.execute_statement(statement) return len(list(cursor)) if __name__ == '__main__': """ Create indexes on tables in a particular ledger. """ logger.info('Creating indexes on all tables in a single transaction...') try: with create_qldb_session() as session: session.execute_lambda(lambda x: create_index(x, Constants.PERSON_TABLE_NAME, Constants.GOV_ID_INDEX_NAME) and create_index(x, Constants.VEHICLE_TABLE_NAME, Constants.VEHICLE_VIN_INDEX_NAME) and create_index(x, Constants.VEHICLE_REGISTRATION_TABLE_NAME, Constants.LICENSE_PLATE_NUMBER_INDEX_NAME) and create_index(x, Constants.VEHICLE_REGISTRATION_TABLE_NAME, Constants.VEHICLE_VIN_INDEX_NAME) and create_index(x, Constants.DRIVERS_LICENSE_TABLE_NAME, Constants.PERSON_ID_INDEX_NAME) and create_index(x, Constants.DRIVERS_LICENSE_TABLE_NAME, Constants.LICENSE_NUMBER_INDEX_NAME), lambda retry_attempt: logger.info('Retrying due to OCC conflict...')) logger.info('Indexes created successfully.') except Exception: logger.exception('Unable to create indexes.') ``` ------ 1. このプログラムを実行するには、次のコマンドを入力します。 ``` python create_index.py ``` **データをテーブルにロードするには** 1. 次のファイル (`sample_data.py`) を確認します。このファイルには、`vehicle-registration` テーブルに挿入するサンプルデータが含まれています。このファイルは、[Amazon Ion](ion.md) データを変換、解析、および出力するヘルパー関数を提供する `amazon.ion` パッケージからもインポートされます。 ``` # 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. from datetime import datetime from decimal import Decimal from logging import basicConfig, getLogger, INFO from amazon.ion.simple_types import IonPyBool, IonPyBytes, IonPyDecimal, IonPyDict, IonPyFloat, IonPyInt, IonPyList, \ IonPyNull, IonPySymbol, IonPyText, IonPyTimestamp from amazon.ion.simpleion import dumps, loads logger = getLogger(__name__) basicConfig(level=INFO) IonValue = (IonPyBool, IonPyBytes, IonPyDecimal, IonPyDict, IonPyFloat, IonPyInt, IonPyList, IonPyNull, IonPySymbol, IonPyText, IonPyTimestamp) class SampleData: """ Sample domain objects for use throughout this tutorial. """ DRIVERS_LICENSE = [ { 'PersonId': '', 'LicenseNumber': 'LEWISR261LL', 'LicenseType': 'Learner', 'ValidFromDate': datetime(2016, 12, 20), 'ValidToDate': datetime(2020, 11, 15) }, { 'PersonId': '', 'LicenseNumber': 'LOGANB486CG', 'LicenseType': 'Probationary', 'ValidFromDate': datetime(2016, 4, 6), 'ValidToDate': datetime(2020, 11, 15) }, { 'PersonId': '', 'LicenseNumber': '744 849 301', 'LicenseType': 'Full', 'ValidFromDate': datetime(2017, 12, 6), 'ValidToDate': datetime(2022, 10, 15) }, { 'PersonId': '', 'LicenseNumber': 'P626-168-229-765', 'LicenseType': 'Learner', 'ValidFromDate': datetime(2017, 8, 16), 'ValidToDate': datetime(2021, 11, 15) }, { 'PersonId': '', 'LicenseNumber': 'S152-780-97-415-0', 'LicenseType': 'Probationary', 'ValidFromDate': datetime(2015, 8, 15), 'ValidToDate': datetime(2021, 8, 21) } ] PERSON = [ { 'FirstName': 'Raul', 'LastName': 'Lewis', 'Address': '1719 University Street, Seattle, WA, 98109', 'DOB': datetime(1963, 8, 19), 'GovId': 'LEWISR261LL', 'GovIdType': 'Driver License' }, { 'FirstName': 'Brent', 'LastName': 'Logan', 'DOB': datetime(1967, 7, 3), 'Address': '43 Stockert Hollow Road, Everett, WA, 98203', 'GovId': 'LOGANB486CG', 'GovIdType': 'Driver License' }, { 'FirstName': 'Alexis', 'LastName': 'Pena', 'DOB': datetime(1974, 2, 10), 'Address': '4058 Melrose Street, Spokane Valley, WA, 99206', 'GovId': '744 849 301', 'GovIdType': 'SSN' }, { 'FirstName': 'Melvin', 'LastName': 'Parker', 'DOB': datetime(1976, 5, 22), 'Address': '4362 Ryder Avenue, Seattle, WA, 98101', 'GovId': 'P626-168-229-765', 'GovIdType': 'Passport' }, { 'FirstName': 'Salvatore', 'LastName': 'Spencer', 'DOB': datetime(1997, 11, 15), 'Address': '4450 Honeysuckle Lane, Seattle, WA, 98101', 'GovId': 'S152-780-97-415-0', 'GovIdType': 'Passport' } ] VEHICLE = [ { 'VIN': '1N4AL11D75C109151', 'Type': 'Sedan', 'Year': 2011, 'Make': 'Audi', 'Model': 'A5', 'Color': 'Silver' }, { 'VIN': 'KM8SRDHF6EU074761', 'Type': 'Sedan', 'Year': 2015, 'Make': 'Tesla', 'Model': 'Model S', 'Color': 'Blue' }, { 'VIN': '3HGGK5G53FM761765', 'Type': 'Motorcycle', 'Year': 2011, 'Make': 'Ducati', 'Model': 'Monster 1200', 'Color': 'Yellow' }, { 'VIN': '1HVBBAANXWH544237', 'Type': 'Semi', 'Year': 2009, 'Make': 'Ford', 'Model': 'F 150', 'Color': 'Black' }, { 'VIN': '1C4RJFAG0FC625797', 'Type': 'Sedan', 'Year': 2019, 'Make': 'Mercedes', 'Model': 'CLK 350', 'Color': 'White' } ] VEHICLE_REGISTRATION = [ { 'VIN': '1N4AL11D75C109151', 'LicensePlateNumber': 'LEWISR261LL', 'State': 'WA', 'City': 'Seattle', 'ValidFromDate': datetime(2017, 8, 21), 'ValidToDate': datetime(2020, 5, 11), 'PendingPenaltyTicketAmount': Decimal('90.25'), 'Owners': { 'PrimaryOwner': {'PersonId': ''}, 'SecondaryOwners': [] } }, { 'VIN': 'KM8SRDHF6EU074761', 'LicensePlateNumber': 'CA762X', 'State': 'WA', 'City': 'Kent', 'PendingPenaltyTicketAmount': Decimal('130.75'), 'ValidFromDate': datetime(2017, 9, 14), 'ValidToDate': datetime(2020, 6, 25), 'Owners': { 'PrimaryOwner': {'PersonId': ''}, 'SecondaryOwners': [] } }, { 'VIN': '3HGGK5G53FM761765', 'LicensePlateNumber': 'CD820Z', 'State': 'WA', 'City': 'Everett', 'PendingPenaltyTicketAmount': Decimal('442.30'), 'ValidFromDate': datetime(2011, 3, 17), 'ValidToDate': datetime(2021, 3, 24), 'Owners': { 'PrimaryOwner': {'PersonId': ''}, 'SecondaryOwners': [] } }, { 'VIN': '1HVBBAANXWH544237', 'LicensePlateNumber': 'LS477D', 'State': 'WA', 'City': 'Tacoma', 'PendingPenaltyTicketAmount': Decimal('42.20'), 'ValidFromDate': datetime(2011, 10, 26), 'ValidToDate': datetime(2023, 9, 25), 'Owners': { 'PrimaryOwner': {'PersonId': ''}, 'SecondaryOwners': [] } }, { 'VIN': '1C4RJFAG0FC625797', 'LicensePlateNumber': 'TH393F', 'State': 'WA', 'City': 'Olympia', 'PendingPenaltyTicketAmount': Decimal('30.45'), 'ValidFromDate': datetime(2013, 9, 2), 'ValidToDate': datetime(2024, 3, 19), 'Owners': { 'PrimaryOwner': {'PersonId': ''}, 'SecondaryOwners': [] } } ] def convert_object_to_ion(py_object): """ Convert a Python object into an Ion object. :type py_object: object :param py_object: The object to convert. :rtype: :py:class:`amazon.ion.simple_types.IonPyValue` :return: The converted Ion object. """ ion_object = loads(dumps(py_object)) return ion_object def to_ion_struct(key, value): """ Convert the given key and value into an Ion struct. :type key: str :param key: The key which serves as an unique identifier. :type value: str :param value: The value associated with a given key. :rtype: :py:class:`amazon.ion.simple_types.IonPyDict` :return: The Ion dictionary object. """ ion_struct = dict() ion_struct[key] = value return loads(str(ion_struct)) def get_document_ids(transaction_executor, table_name, field, value): """ Gets the document IDs from the given table. :type transaction_executor: :py:class:`pyqldb.execution.executor.Executor` :param transaction_executor: An Executor object allowing for execution of statements within a transaction. :type table_name: str :param table_name: The table name to query. :type field: str :param field: A field to query. :type value: str :param value: The key of the given field. :rtype: list :return: A list of document IDs. """ query = "SELECT id FROM {} AS t BY id WHERE t.{} = ?".format(table_name, field) cursor = transaction_executor.execute_statement(query, convert_object_to_ion(value)) return list(map(lambda table: table.get('id'), cursor)) def get_document_ids_from_dml_results(result): """ Return a list of modified document IDs as strings from DML results. :type result: :py:class:`pyqldb.cursor.buffered_cursor.BufferedCursor` :param: result: The result set from DML operation. :rtype: list :return: List of document IDs. """ ret_val = list(map(lambda x: x.get('documentId'), result)) return ret_val def print_result(cursor): """ Pretty print the result set. Returns the number of documents in the result set. :type cursor: :py:class:`pyqldb.cursor.stream_cursor.StreamCursor`/ :py:class:`pyqldb.cursor.buffered_cursor.BufferedCursor` :param cursor: An instance of the StreamCursor or BufferedCursor class. :rtype: int :return: Number of documents in the result set. """ result_counter = 0 for row in cursor: # Each row would be in Ion format. print_ion(row) result_counter += 1 return result_counter def print_ion(ion_value): """ Pretty print an Ion Value. :type ion_value: :py:class:`amazon.ion.simple_types.IonPySymbol` :param ion_value: Any Ion Value to be pretty printed. """ logger.info(dumps(ion_value, binary=False, indent=' ', omit_version_marker=True)) ``` **注記** `get_document_ids` 関数は、システムによって割り当てられたドキュメント ID をテーブルから返すクエリを実行します。詳細については、「[BY 句を使用したドキュメント ID のクエリの実行](working.metadata.by-clause.md)」を参照してください。 1. 次のプログラム (`insert_document.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.constants import Constants from pyqldbsamples.model.sample_data import convert_object_to_ion, SampleData, get_document_ids_from_dml_results from pyqldbsamples.connect_to_ledger import create_qldb_driver logger = getLogger(__name__) basicConfig(level=INFO) def update_person_id(document_ids): """ Update the PersonId value for DriversLicense records and the PrimaryOwner value for VehicleRegistration records. :type document_ids: list :param document_ids: List of document IDs. :rtype: list :return: Lists of updated DriversLicense records and updated VehicleRegistration records. """ new_drivers_licenses = SampleData.DRIVERS_LICENSE.copy() new_vehicle_registrations = SampleData.VEHICLE_REGISTRATION.copy() for i in range(len(SampleData.PERSON)): drivers_license = new_drivers_licenses[i] registration = new_vehicle_registrations[i] drivers_license.update({'PersonId': str(document_ids[i])}) registration['Owners']['PrimaryOwner'].update({'PersonId': str(document_ids[i])}) return new_drivers_licenses, new_vehicle_registrations def insert_documents(driver, table_name, documents): """ Insert the given list of documents into a table in a single transaction. :type driver: :py:class:`pyqldb.driver.qldb_driver.QldbDriver` :param driver: An instance of the QldbDriver class. :type table_name: str :param table_name: Name of the table to insert documents into. :type documents: list :param documents: List of documents to insert. :rtype: list :return: List of documents IDs for the newly inserted documents. """ logger.info('Inserting some documents in the {} table...'.format(table_name)) statement = 'INSERT INTO {} ?'.format(table_name) cursor = driver.execute_lambda(lambda executor: executor.execute_statement(statement, convert_object_to_ion(documents))) list_of_document_ids = get_document_ids_from_dml_results(cursor) return list_of_document_ids def update_and_insert_documents(driver): """ Handle the insertion of documents and updating PersonIds. :type driver: :py:class:`pyqldb.driver.qldb_driver.QldbDriver` :param driver: An instance of the QldbDriver class. """ list_ids = insert_documents(driver, Constants.PERSON_TABLE_NAME, SampleData.PERSON) logger.info("Updating PersonIds for 'DriversLicense' and PrimaryOwner for 'VehicleRegistration'...") new_licenses, new_registrations = update_person_id(list_ids) insert_documents(driver, Constants.VEHICLE_TABLE_NAME, SampleData.VEHICLE) insert_documents(driver, Constants.VEHICLE_REGISTRATION_TABLE_NAME, new_registrations) insert_documents(driver, Constants.DRIVERS_LICENSE_TABLE_NAME, new_licenses) def main(ledger_name=Constants.LEDGER_NAME): """ Insert documents into a table in a QLDB ledger. """ try: with create_qldb_driver(ledger_name) as driver: # An INSERT statement creates the initial revision of a document with a version number of zero. # QLDB also assigns a unique document identifier in GUID format as part of the metadata. update_and_insert_documents(driver) logger.info('Documents inserted successfully!') except Exception as e: logger.exception('Error inserting or updating documents.') 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.constants import Constants from pyqldbsamples.model.sample_data import convert_object_to_ion, SampleData, get_document_ids_from_dml_results from pyqldbsamples.connect_to_ledger import create_qldb_session logger = getLogger(__name__) basicConfig(level=INFO) def update_person_id(document_ids): """ Update the PersonId value for DriversLicense records and the PrimaryOwner value for VehicleRegistration records. :type document_ids: list :param document_ids: List of document IDs. :rtype: list :return: Lists of updated DriversLicense records and updated VehicleRegistration records. """ new_drivers_licenses = SampleData.DRIVERS_LICENSE.copy() new_vehicle_registrations = SampleData.VEHICLE_REGISTRATION.copy() for i in range(len(SampleData.PERSON)): drivers_license = new_drivers_licenses[i] registration = new_vehicle_registrations[i] drivers_license.update({'PersonId': str(document_ids[i])}) registration['Owners']['PrimaryOwner'].update({'PersonId': str(document_ids[i])}) return new_drivers_licenses, new_vehicle_registrations def insert_documents(transaction_executor, table_name, documents): """ Insert the given list of documents into a table in a single transaction. :type transaction_executor: :py:class:`pyqldb.execution.executor.Executor` :param transaction_executor: An Executor object allowing for execution of statements within a transaction. :type table_name: str :param table_name: Name of the table to insert documents into. :type documents: list :param documents: List of documents to insert. :rtype: list :return: List of documents IDs for the newly inserted documents. """ logger.info('Inserting some documents in the {} table...'.format(table_name)) statement = 'INSERT INTO {} ?'.format(table_name) cursor = transaction_executor.execute_statement(statement, convert_object_to_ion(documents)) list_of_document_ids = get_document_ids_from_dml_results(cursor) return list_of_document_ids def update_and_insert_documents(transaction_executor): """ Handle the insertion of documents and updating PersonIds all in a single transaction. :type transaction_executor: :py:class:`pyqldb.execution.executor.Executor` :param transaction_executor: An Executor object allowing for execution of statements within a transaction. """ list_ids = insert_documents(transaction_executor, Constants.PERSON_TABLE_NAME, SampleData.PERSON) logger.info("Updating PersonIds for 'DriversLicense' and PrimaryOwner for 'VehicleRegistration'...") new_licenses, new_registrations = update_person_id(list_ids) insert_documents(transaction_executor, Constants.VEHICLE_TABLE_NAME, SampleData.VEHICLE) insert_documents(transaction_executor, Constants.VEHICLE_REGISTRATION_TABLE_NAME, new_registrations) insert_documents(transaction_executor, Constants.DRIVERS_LICENSE_TABLE_NAME, new_licenses) if __name__ == '__main__': """ Insert documents into a table in a QLDB ledger. """ try: with create_qldb_session() as session: # An INSERT statement creates the initial revision of a document with a version number of zero. # QLDB also assigns a unique document identifier in GUID format as part of the metadata. session.execute_lambda(lambda executor: update_and_insert_documents(executor), lambda retry_attempt: logger.info('Retrying due to OCC conflict...')) logger.info('Documents inserted successfully!') except Exception: logger.exception('Error inserting or updating documents.') ``` ------ **注記** このプログラムは、パラメータ化された値を渡して `execute_statement` 関数を呼び出す方法を示しています。実行する PartiQL ステートメントに加えて、データパラメータを渡すことができます。ステートメント文字列の変数プレースホルダーとして疑問符 (`?`) を使用します。 `INSERT` ステートメントが成功すると、 挿入された各ドキュメントの `id` が返されます。 1. このプログラムを実行するには、次のコマンドを入力します。 ``` python insert_document.py ``` 次に、`SELECT` ステートメントを使用すると、`vehicle-registration` 台帳のテーブルからデータを読み取ることができます。[ステップ 4: 台帳のテーブルにクエリを実行する](getting-started.python.step-4.md) に進みます。