As traduções são geradas por tradução automática. Em caso de conflito entre o conteúdo da tradução e da versão original em inglês, a versão em inglês prevalecerá. # Etapa 3: criar tabelas, índices e dados de amostra **Importante** Aviso de fim do suporte: os clientes existentes poderão usar o Amazon QLDB até o final do suporte em 31/07/2025. Para obter mais detalhes, consulte [Migrar um Amazon QLDB Ledger para o Amazon](https://aws.amazon.com/blogs/database/migrate-an-amazon-qldb-ledger-to-amazon-aurora-postgresql/) Aurora PostgreSQL. Quando seu ledger do Amazon QLDB está ativo e aceita conexão, você pode começar a criar tabelas para dados sobre veículos, seus proprietários e suas informações de registro. Depois de criar as tabelas e os índices, você pode carregá-los com dados. Nesta etapa, você cria quatro tabelas no ledger `vehicle-registration`: + `VehicleRegistration` + `Vehicle` + `Person` + `DriversLicense` Você também cria os índices a seguir. **** | Nome da tabela | Campo | | --- | --- | | VehicleRegistration | VIN | | VehicleRegistration | LicensePlateNumber | | Vehicle | VIN | | Person | GovId | | DriversLicense | LicenseNumber | | DriversLicense | PersonId | Ao inserir dados de amostra, primeiro você insere documentos na tabela `Person`. Em seguida, você usa o sistema atribuído `id` a partir de cada documento `Person` para preencher os campos correspondentes nos documentos `VehicleRegistration` e `DriversLicense` apropriados. **dica** Como prática recomendada, use o `id` de um documento atribuído pelo sistema como uma chave estrangeira. Embora você possa definir campos destinados a serem identificadores exclusivos (por exemplo, o VIN de um veículo), o verdadeiro identificador exclusivo de um documento é seu `id`. Esse campo está incluído nos metadados do documento, que você pode consultar na *visualização confirmada* (a visualização definida pelo sistema de uma tabela). Para obter mais informações sobre visualizações no QLDB, consulte [Conceitos principais](ledger-structure.md). Para saber mais sobre metadados, consulte [Consultar metadados do documento](working.metadata.md). **Para criar tabelas e índices** 1. Use o programa a seguir (`create_table.py`) para criar as tabelas mencionadas anteriormente. ------ #### [ 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.') ``` ------ **nota** Este programa demonstra como usar a função `execute_lambda`. Neste exemplo, você executa várias instruções PartiQL `CREATE TABLE` com uma única expressão lambda. Essa função de execução inicia implicitamente uma transação, executa todas as instruções no lambda e, em seguida, confirma automaticamente a transação. 1. Para executar o programa, digite o comando a seguir. ``` python create_table.py ``` 1. Use o programa a seguir (`create_index.py`) para criar índices nas tabelas, conforme descrito anteriormente. ------ #### [ 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. Para executar o programa, digite o comando a seguir. ``` python create_index.py ``` **Para carregar os dados de exemplo em tabelas** 1. Examine o arquivo (`sample_data.py`) a seguir, que representa os dados de amostra que você insere nas tabelas `vehicle-registration`. Esse arquivo também é importado do pacote `amazon.ion` para fornecer funções auxiliares que convertem, analisam e imprimem dados do [Amazon Ion](ion.md). ``` # 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)) ``` **nota** A `get_document_ids` função executa uma consulta que retorna um documento atribuído pelo sistema a IDs partir de uma tabela. Para saber mais, consulte [Usar a cláusula BY para consultar o ID do documento](working.metadata.by-clause.md). 1. Use o programa a seguir (`insert_document.py`) para inserir os dados de amostra em suas tabelas. ------ #### [ 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.') ``` ------ **nota** Este programa demonstra como chamar a função `execute_statement` com valores parametrizados. Você pode passar parâmetros de dados além da instrução partiQL que deseja executar. Use um ponto de interrogação (`?`) como um marcador variável em sua string de instrução. Se uma instrução `INSERT` for bem-sucedida, ela retornará a `id` de cada documento inserido. 1. Para executar o programa, digite o comando a seguir. ``` python insert_document.py ``` Em seguida, você pode usar instruções `SELECT` para ler os dados das tabelas no ledger `vehicle-registration`. Vá para [Etapa 4: consultar as tabelas em um ledger](getting-started.python.step-4.md).