Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà. # Passaggio 3: Creare tabelle, indici e dati di esempio **Importante** Avviso di fine del supporto: i clienti esistenti potranno utilizzare Amazon QLDB fino alla fine del supporto, il 31/07/2025. Per ulteriori dettagli, consulta [Migrare un registro Amazon QLDB su Amazon Aurora PostgreSQL](https://aws.amazon.com/blogs/database/migrate-an-amazon-qldb-ledger-to-amazon-aurora-postgresql/). Quando il tuo registro Amazon QLDB è attivo e accetta connessioni, puoi iniziare a creare tabelle per i dati sui veicoli, i loro proprietari e le loro informazioni di immatricolazione. Dopo aver creato le tabelle e gli indici, puoi caricarli con i dati. In questo passaggio, crei quattro tabelle nel libro mastro: `vehicle-registration` + `VehicleRegistration` + `Vehicle` + `Person` + `DriversLicense` Vengono inoltre creati i seguenti indici. **** | Nome tabella | Campo | | --- | --- | | VehicleRegistration | VIN | | VehicleRegistration | LicensePlateNumber | | Vehicle | VIN | | Person | GovId | | DriversLicense | LicenseNumber | | DriversLicense | PersonId | Quando si inseriscono dati di esempio, si inseriscono innanzitutto i documenti nella tabella. `Person` Quindi, si utilizzano i dati assegnati `id` dal sistema a ciascun `Person` documento per compilare i campi corrispondenti nei documenti e nei documenti appropriati. `VehicleRegistration` `DriversLicense` **Suggerimento** Come procedura ottimale, utilizzate il sistema assegnato a un documento come chiave esterna`id`. Sebbene sia possibile definire campi destinati a essere identificatori univoci (ad esempio, il VIN di un veicolo), il vero identificatore univoco di un documento è il suo. `id` Questo campo è incluso nei metadati del documento, a cui è possibile eseguire una query nella visualizzazione confermata (*la vista* di una tabella definita dal sistema). Per ulteriori informazioni sulle viste in QLDB, vedere. [Concetti principali](ledger-structure.md) Per ulteriori informazioni sui metadati, consulta. [Interrogazione dei metadati dei documenti](working.metadata.md) **Per creare tabelle e indici** 1. Utilizzate il seguente programma (`create_table.py`) per creare le tabelle menzionate in precedenza. ------ #### [ 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** Questo programma dimostra come utilizzare la `execute_lambda` funzione. In questo esempio, esegui più istruzioni `CREATE TABLE` PartiQL con una singola espressione lambda. Questa funzione di esecuzione avvia implicitamente una transazione, esegue tutte le istruzioni in lambda e quindi esegue automaticamente il commit della transazione. 1. Per eseguire il programma, immetti il comando seguente: ``` python create_table.py ``` 1. Utilizzate il seguente programma (`create_index.py`) per creare indici sulle tabelle, come descritto in precedenza. ------ #### [ 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. Per eseguire il programma, immetti il comando seguente: ``` python create_index.py ``` **Per caricare i dati nelle tabelle** 1. Esaminate il seguente file (`sample_data.py`), che rappresenta i dati di esempio che inserite nelle `vehicle-registration` tabelle. Questo file viene inoltre importato dal `amazon.ion` pacchetto per fornire funzioni di supporto che convertono, analizzano e stampano i dati di [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** La `get_document_ids` funzione esegue una query che restituisce un documento IDs assegnato dal sistema da una tabella. Per ulteriori informazioni, consulta [Utilizzo della clausola BY per interrogare l'ID del documento](working.metadata.by-clause.md). 1. Utilizzate il seguente programma (`insert_document.py`) per inserire i dati di esempio nelle tabelle. ------ #### [ 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** Questo programma dimostra come chiamare la `execute_statement` funzione con valori parametrizzati. È possibile passare parametri di dati oltre all'istruzione PartiQL che si desidera eseguire. Utilizzate un punto interrogativo (`?`) come segnaposto variabile nella stringa dell'istruzione. Se un'`INSERT`istruzione ha esito positivo, restituisce il valore `id` di ogni documento inserito. 1. Per eseguire il programma, immetti il comando seguente: ``` python insert_document.py ``` Successivamente, è possibile utilizzare `SELECT` le istruzioni per leggere i dati dalle tabelle del `vehicle-registration` libro mastro. Passa a [Passaggio 4: interrogare le tabelle in un libro mastro](getting-started.python.step-4.md).