From 974a8018f79d8592cbd6a59b1b26a9d288975328 Mon Sep 17 00:00:00 2001 From: T-Aji Date: Tue, 13 Aug 2024 12:30:42 +0100 Subject: dumps data to csv --- src/extract_lambda.py | 32 +++++++++++++++++++++++++++----- 1 file changed, 27 insertions(+), 5 deletions(-) diff --git a/src/extract_lambda.py b/src/extract_lambda.py index 7d56c66..8317ef8 100644 --- a/src/extract_lambda.py +++ b/src/extract_lambda.py @@ -1,22 +1,25 @@ from pg8000.native import Connection, Error, DatabaseError, InterfaceError from dotenv import load_dotenv import os +import boto3 +import csv +from botocore.exceptions import ClientError load_dotenv() -def extract(): +def lambda_handler(event, context): + client = boto3.client('s3') # temporary credentials for dev- will not have access when uploaded - + database = os.getenv('database') user = os.getenv('user') password = os.getenv('password') host = os.getenv('host') port = os.getenv('port') - try: - db = Connection.run( + db = Connection( database=database, user=user, password=password, @@ -27,6 +30,25 @@ def extract(): print(e) except InterfaceError as i: print(i) - + #replace prints with upload to cloudwatch logs + + tables = db.run("SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE';") + for table in tables: + table_name = table[0] + rows = db.run(f"SELECT * FROM {table_name};") + # this saves the csv files to the repo root before writing to s3, this is unnecessary. how will the lambda behave when it attempts to save files? + with open(f"{table_name}.csv", "w", newline='') as file: + writer = csv.writer(file) + writer.writerow([desc["name"] for desc in db.columns(f"SELECT * FROM {table_name};")]) + writer.writerows(rows) + try: + client.upload_file(file, Bucket='ingestion-bucket', Object_name=table_name) + + except ClientError as e: + print(e) + #replace print with upload to cloudwatch logs + + if db: + db.close() \ No newline at end of file -- cgit v1.2.3 From cdb4577b5ad7ae1f708797de6bbf17e289bfac14 Mon Sep 17 00:00:00 2001 From: T-Aji Date: Tue, 13 Aug 2024 15:32:33 +0100 Subject: feat/ add logging & split task into 3 helper functions --- src/extract_lambda.py | 140 +++++++++++++++++++++++++++++++++++++++----------- 1 file changed, 109 insertions(+), 31 deletions(-) diff --git a/src/extract_lambda.py b/src/extract_lambda.py index 8317ef8..11ea5d1 100644 --- a/src/extract_lambda.py +++ b/src/extract_lambda.py @@ -1,54 +1,132 @@ -from pg8000.native import Connection, Error, DatabaseError, InterfaceError +from pg8000.native import Connection, DatabaseError, InterfaceError from dotenv import load_dotenv import os import boto3 import csv from botocore.exceptions import ClientError +import logging +import json +logger = logging.getLogger() +logger.setLevel(logging.INFO) load_dotenv() -def lambda_handler(event, context): - client = boto3.client('s3') -# temporary credentials for dev- will not have access when uploaded +database = os.getenv('database') +user = os.getenv('user') +password = os.getenv('password') +host = os.getenv('host') +port = os.getenv('port') + +def lambda_handler(event, context): + """This lambda function connects to the Totesys database, lists the contents of the ingestion bucket, + and converts all tables to CSV and if any of those tables do not exist in, or are different to the ones in s3, it uploads them + it uses 3 helper functions to achieve these 3 functionalities + """ + try: + db = connect_to_database() + existing_files = list_existing_s3_files() + any_changes = process_and_upload_tables(db, existing_files) + + if not any_changes: + logger.info("No changes detected in the database.") + return { + 'statusCode': 200, + 'body': json.dumps('No changes detected, no CSV files were uploaded.') + } + else: + return { + 'statusCode': 200, + 'body': json.dumps('CSV files processed and uploaded successfully.') + } + + except Exception as e: + logger.error(f'Error: {e}') + return { + 'statusCode': 500, + 'body': json.dumps('Internal server error.') + } - database = os.getenv('database') - user = os.getenv('user') - password = os.getenv('password') - host = os.getenv('host') - port = os.getenv('port') + finally: + + if db: + db.close() +def connect_to_database(): try: - db = Connection( - database=database, - user=user, - password=password, - host=host, - port=port + return Connection( + database=database, + user=user, + password=password, + host=host, + port=port ) except DatabaseError as e: - print(e) + logger.error(f'Database error: {e}') + raise except InterfaceError as i: - print(i) - #replace prints with upload to cloudwatch logs + logger.error(f'Interface error: {i}') + raise + + +def list_existing_s3_files(): + """Creates a dictionary and populates it with the + results of listing the contents of the s3 bucket, then + returns the populated dictionary + """ + client = boto3.client('s3') + existing_files = {} + + try: + response = client.list_objects_v2(Bucket=ingestion_bucket) + + if 'Contents' in response: + for obj in response['Contents']: + s3_key = obj['Key'] + try: + file_obj = client.get_object(Bucket=ingestion_bucket, Key=s3_key) + file_content = file_obj['Body'].read().decode('utf-8') + existing_files[s3_key] = file_content + except ClientError as e: + logger.error(f'Error retrieving S3 object {s3_key}: {e}') + + except ClientError as e: + logger.error(f'Error listing S3 objects: {e}') + + return existing_files + + + +def process_and_upload_tables(db, existing_files): + """Creates a list of the tables from a database query and + then selects everything from each table in individual queries + it then writes each table to CSV files and compares with the item + in the existing_files dictionary with the same name. If it finds sny changes + to files, or new tables/files it uploads them to the s3 bucket + """ + client = boto3.client('s3') tables = db.run("SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE';") + for table in tables: table_name = table[0] rows = db.run(f"SELECT * FROM {table_name};") - # this saves the csv files to the repo root before writing to s3, this is unnecessary. how will the lambda behave when it attempts to save files? - with open(f"{table_name}.csv", "w", newline='') as file: + + + csv_file_path = f"/tmp/{table_name}.csv" + with open(csv_file_path, "w", newline='') as file: writer = csv.writer(file) - writer.writerow([desc["name"] for desc in db.columns(f"SELECT * FROM {table_name};")]) + column_names = [desc["name"] for desc in db.columns(f"SELECT * FROM {table_name};")] + writer.writerow(column_names) writer.writerows(rows) - try: - client.upload_file(file, Bucket='ingestion-bucket', Object_name=table_name) - - except ClientError as e: - print(e) - #replace print with upload to cloudwatch logs - - if db: - db.close() + + s3_key = f"{table_name}/latest.csv" + new_csv_content = open(csv_file_path, "r").read() + - \ No newline at end of file + if s3_key not in existing_files or existing_files[s3_key] != new_csv_content: + try: + client.upload_file(csv_file_path, ingestion_bucket, s3_key) + logger.info(f"Uploaded {s3_key} to S3.") + except ClientError as e: + logger.error(f'Error uploading to S3: {e}') \ No newline at end of file -- cgit v1.2.3 From 4f0d6f287ae83d7cdc0df6988ab7b9de10912f16 Mon Sep 17 00:00:00 2001 From: T-Aji Date: Wed, 14 Aug 2024 12:25:57 +0100 Subject: feat/passing tests to helper function list_existing_s3_files --- .gitignore | 3 +++ src/extract_lambda.py | 12 ++++++----- tests/dummy.txt | 1 + tests/test_extract_lambda.py | 49 ++++++++++++++++++++++++++++++++++++++++++++ 4 files changed, 60 insertions(+), 5 deletions(-) create mode 100644 .gitignore create mode 100644 tests/dummy.txt create mode 100644 tests/test_extract_lambda.py diff --git a/.gitignore b/.gitignore new file mode 100644 index 0000000..428f94e --- /dev/null +++ b/.gitignore @@ -0,0 +1,3 @@ +venv +.env +__pycache__/ \ No newline at end of file diff --git a/src/extract_lambda.py b/src/extract_lambda.py index 11ea5d1..dc70590 100644 --- a/src/extract_lambda.py +++ b/src/extract_lambda.py @@ -18,6 +18,7 @@ password = os.getenv('password') host = os.getenv('host') port = os.getenv('port') + def lambda_handler(event, context): """This lambda function connects to the Totesys database, lists the contents of the ingestion bucket, and converts all tables to CSV and if any of those tables do not exist in, or are different to the ones in s3, it uploads them @@ -69,27 +70,28 @@ def connect_to_database(): raise - -def list_existing_s3_files(): +def list_existing_s3_files(bucket_name='extract_bucket', client=boto3.client('s3')): """Creates a dictionary and populates it with the results of listing the contents of the s3 bucket, then returns the populated dictionary """ - client = boto3.client('s3') + existing_files = {} try: - response = client.list_objects_v2(Bucket=ingestion_bucket) + response = client.list_objects_v2(Bucket='extract_bucket') if 'Contents' in response: for obj in response['Contents']: s3_key = obj['Key'] try: - file_obj = client.get_object(Bucket=ingestion_bucket, Key=s3_key) + file_obj = client.get_object(Bucket=bucket_name, Key=s3_key) file_content = file_obj['Body'].read().decode('utf-8') existing_files[s3_key] = file_content except ClientError as e: logger.error(f'Error retrieving S3 object {s3_key}: {e}') + else: + logger.error('The bucket is empty') except ClientError as e: logger.error(f'Error listing S3 objects: {e}') diff --git a/tests/dummy.txt b/tests/dummy.txt new file mode 100644 index 0000000..af27ff4 --- /dev/null +++ b/tests/dummy.txt @@ -0,0 +1 @@ +This is a test file. \ No newline at end of file diff --git a/tests/test_extract_lambda.py b/tests/test_extract_lambda.py new file mode 100644 index 0000000..472e93a --- /dev/null +++ b/tests/test_extract_lambda.py @@ -0,0 +1,49 @@ +import pytest +import boto3 +from moto import mock_aws +from src.extract_lambda import list_existing_s3_files #process_and_upload_tables +import os +import logging + + +@pytest.fixture(scope='class') +def aws_credentials(): + os.environ["AWS_ACCESS_KEY_ID"] = 'testing' + os.environ["AWS_SECRET_ACCESS_KEY"] = 'testing' + os.environ["AWS_SECURIT_TOKEN"] = 'testing' + os.environ["AWS_SESSION_TOKEN"] = 'testing' + os.environ["AWS_DEFAULT_REGION"]= 'eu-west-2' + +@pytest.fixture(scope='class') +def s3_client(aws_credentials): + with mock_aws(): + yield boto3.client('s3') + +class TestListExistings3Files(): + def test_error_if_no_bucket(self, s3_client, caplog): + + logger = logging.getLogger() + logger.info('Testing now.') + caplog.set_level(logging.ERROR) + list_existing_s3_files(client=s3_client) + assert 'Error listing S3 objects' in caplog.text + + def test_error_if_bucket_is_empty(self, s3_client, caplog): + + s3_client.create_bucket(Bucket='extract_bucket', + CreateBucketConfiguration={ + 'LocationConstraint': 'eu-west-2' + }) + list_existing_s3_files(client=s3_client) + assert 'The bucket is empty' in caplog.text + + def test_error_retrieving_object(self, s3_client, caplog): + s3_client.upload_file('tests/dummy.txt', 'extract_bucket', 'dummy.txt') + list_existing_s3_files(bucket_name='test_bucket', client=s3_client) + + assert 'Error retrieving S3 object ' in caplog.text + + def test_retrieves_file_content(self, s3_client, caplog): + result = list_existing_s3_files(client=s3_client) + + assert list(result.values()) == ['This is a test file.'] \ No newline at end of file -- cgit v1.2.3 From 45e025ac0c4ae8c721cb0b875fd0abd67cc2bc07 Mon Sep 17 00:00:00 2001 From: T-Aji Date: Wed, 14 Aug 2024 15:53:11 +0100 Subject: test: passing test for function connect_to_database --- src/extract_lambda.py | 40 +++++++++++++++++++++++++--------------- tests/test_extract_lambda.py | 40 +++++++++++++++++++++++++++++++++++++--- 2 files changed, 62 insertions(+), 18 deletions(-) diff --git a/src/extract_lambda.py b/src/extract_lambda.py index dc70590..6e94bba 100644 --- a/src/extract_lambda.py +++ b/src/extract_lambda.py @@ -1,6 +1,5 @@ from pg8000.native import Connection, DatabaseError, InterfaceError -from dotenv import load_dotenv -import os +from dotenv import dotenv_values import boto3 import csv from botocore.exceptions import ClientError @@ -9,16 +8,15 @@ import json logger = logging.getLogger() logger.setLevel(logging.INFO) -load_dotenv() - - -database = os.getenv('database') -user = os.getenv('user') -password = os.getenv('password') -host = os.getenv('host') -port = os.getenv('port') +class DBConnectionException(Exception): + """Wraps pg8000.native Error or DatabaseError.""" + def __init__(self, e): + """Initialise with provided error message.""" + self.message = str(e) + super().__init__(self.message) + def lambda_handler(event, context): """This lambda function connects to the Totesys database, lists the contents of the ingestion bucket, and converts all tables to CSV and if any of those tables do not exist in, or are different to the ones in s3, it uploads them @@ -53,8 +51,19 @@ def lambda_handler(event, context): if db: db.close() -def connect_to_database(): +def get_config(path: str = ".env") -> dict: + return dotenv_values(path) + + +def connect_to_database() -> Connection: try: + config = get_config() + host = config["host"] + port = config["port"] + user = config["user"] + password = config["password"] + database = config["database"] + return Connection( database=database, user=user, @@ -62,12 +71,13 @@ def connect_to_database(): host=host, port=port ) - except DatabaseError as e: - logger.error(f'Database error: {e}') - raise + # except DatabaseError as e: + # logger.error(f'Database error: {e}') + # raise except InterfaceError as i: logger.error(f'Interface error: {i}') - raise + raise DBConnectionException("Failed to connect to database") + def list_existing_s3_files(bucket_name='extract_bucket', client=boto3.client('s3')): diff --git a/tests/test_extract_lambda.py b/tests/test_extract_lambda.py index 472e93a..18c49fc 100644 --- a/tests/test_extract_lambda.py +++ b/tests/test_extract_lambda.py @@ -1,10 +1,24 @@ import pytest import boto3 from moto import mock_aws -from src.extract_lambda import list_existing_s3_files #process_and_upload_tables +from unittest.mock import patch +from unittest import TestCase +from src.extract_lambda import list_existing_s3_files, connect_to_database, DBConnectionException #process_and_upload_tables import os import logging +@pytest.fixture(scope='class') +def mock_config(): + env_vars = { + "host": "abc", + "port": "5432", + "user": "def", + "password": "password", + "database": "db", + } + with patch("src.extract_lambda.get_config", return_value=env_vars) as mock_config: + yield mock_config + @pytest.fixture(scope='class') def aws_credentials(): @@ -19,7 +33,7 @@ def s3_client(aws_credentials): with mock_aws(): yield boto3.client('s3') -class TestListExistings3Files(): +class TestListExistings3Files: def test_error_if_no_bucket(self, s3_client, caplog): logger = logging.getLogger() @@ -46,4 +60,24 @@ class TestListExistings3Files(): def test_retrieves_file_content(self, s3_client, caplog): result = list_existing_s3_files(client=s3_client) - assert list(result.values()) == ['This is a test file.'] \ No newline at end of file + assert list(result.values()) == ['This is a test file.'] + +class TestConnectToDatabase: + def test_connect_to_database(mock_conn, mock_config): + with patch("src.extract_lambda.Connection", autospec=True) as mock_conn: + connect_to_database() + mock_conn.assert_called_with( + host="abc", user="def", port="5432", password="password", database="db" + ) + + def test_database_error(self, mock_config): + with pytest.raises(DBConnectionException): + connect_to_database() + + def test_logs_interface_error(self, caplog): + logger = logging.getLogger() + logger.info('Testing now.') + caplog.set_level(logging.ERROR) + with pytest.raises(DBConnectionException): + connect_to_database() + assert 'Interface error' in caplog.text \ No newline at end of file -- cgit v1.2.3 From 848a86b7f3b9c5ce16cd774d19e3fa62ca8ffc68 Mon Sep 17 00:00:00 2001 From: T-Aji Date: Wed, 14 Aug 2024 18:14:01 +0100 Subject: test: mid-through test for process_and_upload_tables --- src/extract_lambda.py | 16 +++++++--------- tests/test_extract_lambda.py | 35 ++++++++++++++++++++++++++++++++--- 2 files changed, 39 insertions(+), 12 deletions(-) diff --git a/src/extract_lambda.py b/src/extract_lambda.py index 6e94bba..a70ecdd 100644 --- a/src/extract_lambda.py +++ b/src/extract_lambda.py @@ -5,6 +5,7 @@ import csv from botocore.exceptions import ClientError import logging import json +from datetime import datetime logger = logging.getLogger() logger.setLevel(logging.INFO) @@ -16,7 +17,7 @@ class DBConnectionException(Exception): """Initialise with provided error message.""" self.message = str(e) super().__init__(self.message) - + def lambda_handler(event, context): """This lambda function connects to the Totesys database, lists the contents of the ingestion bucket, and converts all tables to CSV and if any of those tables do not exist in, or are different to the ones in s3, it uploads them @@ -71,9 +72,6 @@ def connect_to_database() -> Connection: host=host, port=port ) - # except DatabaseError as e: - # logger.error(f'Database error: {e}') - # raise except InterfaceError as i: logger.error(f'Interface error: {i}') raise DBConnectionException("Failed to connect to database") @@ -110,14 +108,14 @@ def list_existing_s3_files(bucket_name='extract_bucket', client=boto3.client('s3 -def process_and_upload_tables(db, existing_files): +def process_and_upload_tables(db, existing_files, client=boto3.client('s3')): """Creates a list of the tables from a database query and then selects everything from each table in individual queries it then writes each table to CSV files and compares with the item - in the existing_files dictionary with the same name. If it finds sny changes + in the existing_files dictionary with the same name. If it finds any changes to files, or new tables/files it uploads them to the s3 bucket """ - client = boto3.client('s3') + tables = db.run("SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE';") for table in tables: @@ -132,13 +130,13 @@ def process_and_upload_tables(db, existing_files): writer.writerow(column_names) writer.writerows(rows) - s3_key = f"{table_name}/latest.csv" + s3_key = f"{table_name}/{datetime.today().year}/{datetime.today().month}/{datetime.today().day}/{table_name}_{datetime.now().strftime('%H:%M:%S')}.csv" new_csv_content = open(csv_file_path, "r").read() if s3_key not in existing_files or existing_files[s3_key] != new_csv_content: try: - client.upload_file(csv_file_path, ingestion_bucket, s3_key) + client.upload_file(csv_file_path, 'extract_bucket', s3_key) logger.info(f"Uploaded {s3_key} to S3.") except ClientError as e: logger.error(f'Error uploading to S3: {e}') \ No newline at end of file diff --git a/tests/test_extract_lambda.py b/tests/test_extract_lambda.py index 18c49fc..74d7e2c 100644 --- a/tests/test_extract_lambda.py +++ b/tests/test_extract_lambda.py @@ -3,7 +3,7 @@ import boto3 from moto import mock_aws from unittest.mock import patch from unittest import TestCase -from src.extract_lambda import list_existing_s3_files, connect_to_database, DBConnectionException #process_and_upload_tables +from src.extract_lambda import list_existing_s3_files, connect_to_database, DBConnectionException, process_and_upload_tables import os import logging @@ -33,7 +33,7 @@ def s3_client(aws_credentials): with mock_aws(): yield boto3.client('s3') -class TestListExistings3Files: +class TestListExistingS3Files: def test_error_if_no_bucket(self, s3_client, caplog): logger = logging.getLogger() @@ -80,4 +80,33 @@ class TestConnectToDatabase: caplog.set_level(logging.ERROR) with pytest.raises(DBConnectionException): connect_to_database() - assert 'Interface error' in caplog.text \ No newline at end of file + assert 'Interface error' in caplog.text + +class TestProcessAndUploadTables: + def test_error_process_and_upload_tables(mock_conn, mock_config, s3_client, caplog, mocker): + logger = logging.getLogger() + logger.info('Testing now.') + caplog.set_level(logging.ERROR) + + with patch("src.extract_lambda.Connection", autospec=True) as mock_conn: + mock_db = connect_to_database() + # need to add a table + s3_key = 'dummy/2024/8/14/dummy_16:46:30.txt' + mock_existing_files = mocker.Mock(return_value={s3_key: 'This is a test file.' }) + s3_client.create_bucket(Bucket='extract_bucket', + CreateBucketConfiguration={ + 'LocationConstraint': 'eu-west-2' + }) + s3_client.upload_file('tests/dummy.txt', 'extract_bucket', s3_key) + process_and_upload_tables(mock_db, mock_existing_files, client=s3_client) + + assert 'Error uploading to S3' in caplog.text + +#@pytest.mark.describe("Helpers") +# @pytest.mark.it("Query processor returns correctly formatted dict") +# def test_process_query(): +# with patch("src.api.helpers.get_db_connection") as mock_conn: +# mock_conn().run.side_effect = db_data +# mock_conn().columns = sample_headers +# result = process_query("test query") +# assert result == sample_result \ No newline at end of file -- cgit v1.2.3