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())