def add_pet(data_api: DataAPI, pet_names: List[str]) -> None: response = data_api.execute('INSERT INTO pets (name) VALUES (:name)', {'name': pet_names[0]}) assert response.generated_fields_first == 1 response = data_api.execute('INSERT INTO pets (name) VALUES (:name)', {'name': pet_names[1]}) assert response.generated_fields_first == 2
def test_simple_execute(rds_data_client): data_api = DataAPI( resource_arn, secret_arn, database=database, client=rds_data_client ) result: Result = data_api.execute('show tables') assert len(result.one()) == 1 assert result.one()[0] == 'pets'
def example_with_statement(): # DataAPI supports with statement for handling transaction with DataAPI(database=database, resource_arn=resource_arn, secret_arn=secret_arn) as data_api: # start transaction insert: Insert = Insert(Pets, {'name': 'dog'}) # INSERT INTO pets (name) VALUES ('dog') # `execute` accepts SQL statement as str or SQL Alchemy SQL objects result: Result = data_api.execute(insert) print(result.number_of_records_updated) # 1 query = Query(Pets).filter(Pets.id == 1) result: Result = data_api.execute(query) # or data_api.execute('select id, name from pets') # SELECT pets.id, pets.name FROM pets WHERE pets.id = 1 # `Result` like a Result object in SQL Alchemy print(result.scalar()) # 1 print(result.one()) # [Record<id=1, name='dog'>] # `Result` is Sequence[Record] records: List[Record] = list(result) print(records) # [Record<id=1, name='dog'>] # Record is Sequence and Iterator record = records[0] print(record[0]) # 1 print(record[1]) # dog for column in record: print(column) # 1 ... # show record as dict() print(record.dict()) # {'id': 1, 'name': 'dog'} # batch insert insert: Insert = Insert(Pets) data_api.batch_execute(insert, [ {'id': 2, 'name': 'cat'}, {'id': 3, 'name': 'snake'}, {'id': 4, 'name': 'rabbit'}, ]) result = data_api.execute('select * from pets') print(list(result)) # [Record<id=1, name='dog'>, Record<id=2, name='cat'>, Record<id=3, name='snake'>, Record<id=4, name='rabbit'>] # result is a sequence object for record in result: print(record)
def test_rollback_with_custom_exception(db_connection): rds_data_client = boto3.client( 'rds-data', endpoint_url='http://127.0.0.1:8080', aws_access_key_id='aaa', aws_secret_access_key='bbb', ) class OriginalError(Exception): pass class OtherError(Exception): pass try: with DataAPI( resource_arn=resource_arn, secret_arn=secret_arn, rollback_exception=OriginalError, database=database, client=rds_data_client, ) as data_api: data_api.execute('INSERT INTO pets (name) VALUES (:name)', {'name': 'dog'}) raise OriginalError # rollback except: pass result = list(db_connection.execute('select * from pets')) assert result == [] try: with DataAPI( resource_arn=resource_arn, secret_arn=secret_arn, rollback_exception=OriginalError, database=database, client=rds_data_client, ) as data_api: data_api.execute('INSERT INTO pets (name) VALUES (:name)', {'name': 'dog'}) raise OtherError except: pass result = list(get_connection().execute('select * from pets')) assert result == [(2, 'dog', None)]
def test_with_statement(rds_data_client, db_connection): with DataAPI( database=database, resource_arn=resource_arn, secret_arn=secret_arn, client=rds_data_client, ) as data_api: result = data_api.execute('INSERT INTO pets (name) VALUES (:name)', {'name': 'dog'}) assert result.number_of_records_updated == 1 query = 'SELECT pets.id AS pets_id, pets.name AS pets_name, pets.seen_at AS pets_seen_at FROM pets WHERE pets.id = 1' result = data_api.execute(query) assert list(result) == [Record([1, 'dog', None], [])] result = data_api.execute('select * from pets') assert result.one().dict() == {'id': 1, 'name': 'dog', 'seen_at': None} # This is deprecated. SQL Alchemy object will be no longer supported data_api.batch_execute( 'INSERT INTO pets (id, name, seen_at) VALUES (:id, :name, :seen_at)', [ { 'id': 2, 'seen_at': '2020-01-02 03:04:05.678912', 'name': 'cat' }, { 'id': 3, 'name': 'snake', 'seen_at': '2020-01-02 03:04:05.678912' }, { 'id': 4, 'name': 'rabbit', 'seen_at': '2020-01-02 03:04:05.678912' }, ], ) result = data_api.execute('select * from pets') expected = [ Record([1, 'dog', None], ['id', 'name', 'seen_at']), Record([2, 'cat', '2020-01-02 03:04:05.678912'], ['id', 'name', 'seen_at']), Record([3, 'snake', '2020-01-02 03:04:05.678912'], ['id', 'name', 'seen_at']), Record([4, 'rabbit', '2020-01-02 03:04:05.678912'], ['id', 'name', 'seen_at']), ] assert list(result) == expected for row, expected_row in zip(result, expected): assert row == expected_row
def example_rollback_with_custom_exception(): class OriginalError(Exception): pass with DataAPI(resource_arn=resource_arn, secret_arn=secret_arn, rollback_exception=rollback_exception=OriginalError) as data_api: data_api.execute(Insert(Pets, {'name': 'dog'})) # some logic ... # rollback when happen `rollback_exception` raise OriginalError # rollback
def test_rollback(rds_data_client, db_connection): try: with DataAPI(resource_arn, secret_arn) as data_api: data_api.execute(Insert(Pets, {'name': 'dog'})) # you can rollback by Exception raise Exception except: pass result = list(db_connection.execute('select * from pets')) assert result == []
def test_with_statement(rds_data_client, db_connection): with DataAPI( database=database, resource_arn=resource_arn, secret_arn=secret_arn, client=rds_data_client, ) as data_api: insert: Insert = Insert(Pets, {'name': 'dog'}) result = data_api.execute(insert) assert result.number_of_records_updated == 1 query = Query(Pets).filter(Pets.id == 1) result = data_api.execute(query) assert list(result) == [Record([1, 'dog'], [])] result = data_api.execute('select * from pets') assert result.one().dict() == {'id': 1, 'name': 'dog'} insert: Insert = Insert(Pets) data_api.batch_execute( insert, [ { 'id': 2, 'name': 'cat' }, { 'id': 3, 'name': 'snake' }, { 'id': 4, 'name': 'rabbit' }, ], ) result = data_api.execute('select * from pets') expected = [ Record([1, 'dog'], ['id', 'name']), Record([2, 'cat'], ['id', 'name']), Record([3, 'snake'], ['id', 'name']), Record([4, 'rabbit'], ['id', 'name']), ] assert list(result) == expected for row, expected_row in zip(result, expected): assert row == expected_row
def add_pet(data_api: DataAPI, pet_names: List[str]) -> None: response = data_api.execute(Insert(Pets, {'name': pet_names[0]})) assert response.generated_fields_first == 1 response = data_api.execute(Insert(Pets, {'name': pet_names[1]})) assert response.generated_fields_first == 2
def add_pets(data_api: DataAPI, pet_names: List[str]) -> None: # start transaction for pet_name in pet_names: data_api.execute(Insert(Pets, {'name': pet_name}))
def example_rollback(): with DataAPI(resource_arn=resource_arn, secret_arn=secret_arn) as data_api: data_api.execute(Insert(Pets, {'name': 'dog'})) # you can rollback by Exception raise Exception
def example_simple_execute(): data_api = DataAPI(resource_arn=resource_arn, secret_arn=secret_arn, database=database) result: Result = data_api.execute('show tables') print(result.scalar())