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 From 6a494184799b54e22c35880aba4231e4d92bab62 Mon Sep 17 00:00:00 2001 From: Alex Date: Thu, 15 Aug 2024 11:08:26 +0100 Subject: chore: create .gitignore for main --- .gitignore | 15 +++++++++++++++ 1 file changed, 15 insertions(+) create mode 100644 .gitignore diff --git a/.gitignore b/.gitignore new file mode 100644 index 0000000..cd44594 --- /dev/null +++ b/.gitignore @@ -0,0 +1,15 @@ +# Terraform +*.tfstate +*.tfstate.* +*.tfvars +*.tfvars.json +.terraform.tfstate.lock.info +.terraform/ +.terraform* + +# Output Files +*.zip +log* + +# OS-Related Files +.DS_Store -- cgit v1.2.3 From 6f12e84d30a798ce80c90ee29aebd7fa45501eba Mon Sep 17 00:00:00 2001 From: Alex Schofield Date: Thu, 15 Aug 2024 11:26:56 +0100 Subject: ci(staging-checks): replace *.yml with staging-checks.yml --- .github/workflows/staging-checks.yml | 50 ++++++++++++++++++++++++++++++++++++ 1 file changed, 50 insertions(+) create mode 100644 .github/workflows/staging-checks.yml diff --git a/.github/workflows/staging-checks.yml b/.github/workflows/staging-checks.yml new file mode 100644 index 0000000..cec0695 --- /dev/null +++ b/.github/workflows/staging-checks.yml @@ -0,0 +1,50 @@ +name: staging-checks + +on: + push: + branches: [development] + pull_request: + branches: [development, staging] + +jobs: + + check-if-py-files-exist: + runs-on: ubuntu-latest + outputs: + py_files_exist: ${{ steps.check.outputs.py_files_exist }} + steps: + - uses: actions/checkout@v2 + - id: check_files + run: | + if [ -n "$(find . -name '*.py')" ]; then + echo "::set-output name=py_files_exist::true" + else + echo "::set-output name=py_files_exist::false" + fi + + python-quality-checks: + needs: check-if-py-files-exist + if: ${{ needs.check-if-py-files-exist.outputs.py_files_exist == 'true' }} + runs-on: ubuntu-latest + steps: + - uses : actions/checkout@v2 + - name : Setup + uses : actions/setup-python@v2 + with: + python-version: 3.11 + - name : Dependencies + run: | + python -m pip install --upgrade pip + pip install flake8 pylint black bandit safety + - name : Linting + run: | + flake8 . + find . -name "*.py" | xargs pylint + - name : Formatting + run: | + black --check . + - name: Security + run: | + bandit -r . + safety check + \ No newline at end of file -- cgit v1.2.3 From 5fecc3060f0565af004368cd0856df848ca0127a Mon Sep 17 00:00:00 2001 From: Alex Schofield Date: Thu, 15 Aug 2024 12:09:27 +0100 Subject: ci(commit-qc-checks): add initial qc checks for commits using ga" it will: - lint python scripts - check python script formatting - check python script security - check formatting for tf scripts - validate tf configuration --- .github/workflows/on-commit.yml | 60 +++++++++++++++++++++++++++++++++++++++++ 1 file changed, 60 insertions(+) create mode 100644 .github/workflows/on-commit.yml diff --git a/.github/workflows/on-commit.yml b/.github/workflows/on-commit.yml new file mode 100644 index 0000000..e429651 --- /dev/null +++ b/.github/workflows/on-commit.yml @@ -0,0 +1,60 @@ +name: commit-qc-checks + +on: + push: + branches-ignore: + - 'main' + +jobs: + check-if-py-files-exist: + runs-on: ubuntu-latest + outputs: + py_files_exist: ${{ steps.check.outputs.py_files_exist }} + steps: + - uses: actions/checkout@v2 + - id: check_files + run: | + if [ -n "$(find . -name '*.py')" ]; then + echo "::set-output name=py_files_exist::true" + else + echo "::set-output name=py_files_exist::false" + fi + + quality-checks: + needs: check-if-py-files-exist + if: ${{ needs.check-if-py-files-exist.outputs.py_files_exist == 'true' }} + runs-on: ubuntu-latest + steps: + - uses : actions/checkout@v2 + - name : 'Python: Setup' + uses : actions/setup-python@v2 + with: + python-version: 3.11 + - name : 'Python: Install Dependencies' + run: | + python -m pip install --upgrade pip + pip install flake8 pylint black bandit safety + - name : 'Python: Linting' + run: | + flake8 . + find . -name "*.py" | xargs pylint + - name : 'Python: Formatting' + run: | + black --check . + - name: 'Python: Security' + run: | + bandit -r . + safety check + - name: 'Terraform: Setup' + uses: hashicorp/setup-terraform@v3 + with: + terraform_version: latest + - name: 'Terraform: Formatting' + working-directory: ./terraform + run: terraform fmt -check -recursive + - name: 'Terraform: Initialise' + working-directory: ./terraform + run: terraform init -backend=false + - name: 'Terraform: Validate' + working-directory: ./terraform + run: terraform validate -- cgit v1.2.3 From 9050c94bf9af7e90056217e9b1eb85f993ad5886 Mon Sep 17 00:00:00 2001 From: Alex Schofield Date: Thu, 15 Aug 2024 12:12:11 +0100 Subject: chore(ci): remove abandoned yml configs --- .github/workflows/python.yml | 50 ------------------------------------ .github/workflows/staging-checks.yml | 50 ------------------------------------ .github/workflows/terraform.yml | 37 -------------------------- 3 files changed, 137 deletions(-) delete mode 100644 .github/workflows/python.yml delete mode 100644 .github/workflows/staging-checks.yml delete mode 100644 .github/workflows/terraform.yml diff --git a/.github/workflows/python.yml b/.github/workflows/python.yml deleted file mode 100644 index 7d5b5b1..0000000 --- a/.github/workflows/python.yml +++ /dev/null @@ -1,50 +0,0 @@ -name: python-quality-checks - -on: - push: - branches: [development] - pull_request: - branches: [development, staging] - -jobs: - - check-if-py-files-exist: - runs-on: ubuntu-latest - outputs: - py_files_exist: ${{ steps.check.outputs.py_files_exist }} - steps: - - uses: actions/checkout@v2 - - id: check_files - run: | - if [ -n "$(find . -name '*.py')" ]; then - echo "::set-output name=py_files_exist::true" - else - echo "::set-output name=py_files_exist::false" - fi - - quality-checks: - needs: check-if-py-files-exist - if: ${{ needs.check-if-py-files-exist.outputs.py_files_exist == 'true' }} - runs-on: ubuntu-latest - steps: - - uses : actions/checkout@v2 - - name : Setup - uses : actions/setup-python@v2 - with: - python-version: 3.11 - - name : Dependencies - run: | - python -m pip install --upgrade pip - pip install flake8 pylint black bandit safety - - name : Linting - run: | - flake8 . - find . -name "*.py" | xargs pylint - - name : Formatting - run: | - black --check . - - name: Security - run: | - bandit -r . - safety check - \ No newline at end of file diff --git a/.github/workflows/staging-checks.yml b/.github/workflows/staging-checks.yml deleted file mode 100644 index cec0695..0000000 --- a/.github/workflows/staging-checks.yml +++ /dev/null @@ -1,50 +0,0 @@ -name: staging-checks - -on: - push: - branches: [development] - pull_request: - branches: [development, staging] - -jobs: - - check-if-py-files-exist: - runs-on: ubuntu-latest - outputs: - py_files_exist: ${{ steps.check.outputs.py_files_exist }} - steps: - - uses: actions/checkout@v2 - - id: check_files - run: | - if [ -n "$(find . -name '*.py')" ]; then - echo "::set-output name=py_files_exist::true" - else - echo "::set-output name=py_files_exist::false" - fi - - python-quality-checks: - needs: check-if-py-files-exist - if: ${{ needs.check-if-py-files-exist.outputs.py_files_exist == 'true' }} - runs-on: ubuntu-latest - steps: - - uses : actions/checkout@v2 - - name : Setup - uses : actions/setup-python@v2 - with: - python-version: 3.11 - - name : Dependencies - run: | - python -m pip install --upgrade pip - pip install flake8 pylint black bandit safety - - name : Linting - run: | - flake8 . - find . -name "*.py" | xargs pylint - - name : Formatting - run: | - black --check . - - name: Security - run: | - bandit -r . - safety check - \ No newline at end of file diff --git a/.github/workflows/terraform.yml b/.github/workflows/terraform.yml deleted file mode 100644 index c349756..0000000 --- a/.github/workflows/terraform.yml +++ /dev/null @@ -1,37 +0,0 @@ -name: terraform-quality-checks - -on: - push: - branches: [development] - paths: - - 'terraform/**.tf' - - 'terraform/**.tfvars' - pull_request: - branches: [development, staging] - paths: - - 'terraform/**.tf' - - 'terraform/**.tfvars' -jobs: - terraform-validation: - runs-on: ubuntu-latest - defaults: - run: - working-directory: ./terraform - steps: - - uses: actions/checkout@v2 - - name: Setup Terraform - uses: hashicorp/setup-terraform@v1 - with: - terraform_version: latest # Using the latest version, but not sure if it's the best practice - - name: Format - run: terraform fmt -check -recursive - - name: Init - run: terraform init -backend=false - - name: Validate - run: terraform validate - - name: Setup TFLint - uses: terraform-linters/setup-tflint@v2 - with: - tflint_version: latest - - name: Run TFLint - run: tflint -f compact \ No newline at end of file -- cgit v1.2.3 From fe548561acc5e133e3bee4026aab85db2e511bcd Mon Sep 17 00:00:00 2001 From: lian-manonog Date: Thu, 15 Aug 2024 13:51:53 +0100 Subject: wip: secrets manager pushing to merge with extract_lambda --- .gitignore | 1 + src/extract_lambda.py | 1 + src/secrets_manager.py | 48 ++++++++++++++++++++++++++++++++++++++++++++ test/test_secrets_manager.py | 34 +++++++++++++++++++++++++++++++ 4 files changed, 84 insertions(+) create mode 100644 src/secrets_manager.py create mode 100644 test/test_secrets_manager.py diff --git a/.gitignore b/.gitignore index d1df545..d164c3f 100644 --- a/.gitignore +++ b/.gitignore @@ -8,3 +8,4 @@ .terraform* log* .DS_Store +venv \ No newline at end of file diff --git a/src/extract_lambda.py b/src/extract_lambda.py index 7d56c66..faa1d30 100644 --- a/src/extract_lambda.py +++ b/src/extract_lambda.py @@ -2,6 +2,7 @@ from pg8000.native import Connection, Error, DatabaseError, InterfaceError from dotenv import load_dotenv import os + load_dotenv() def extract(): diff --git a/src/secrets_manager.py b/src/secrets_manager.py new file mode 100644 index 0000000..c0fb61e --- /dev/null +++ b/src/secrets_manager.py @@ -0,0 +1,48 @@ +import boto3 +from botocore.exceptions import ClientError +import json + + +def sm_client(): + sm_client = boto3.client('secretsmanager') + yield sm_client + +def create_secret(sm_client, secret_name, cohort_id, user, password, host, database, port): + secret = { + "cohort_id": cohort_id, + "user": user, + "password": password, + "host": host, + "database": database, + "port": port + } + + response = sm_client.create_secret( + Name = secret_name, + SecretString = json.dumps(secret) + ) + + print(response) + return response + +def list_secret(sm_client): + response = sm_client.list_secrets() + secret_dict = response['SecretList'] + secret_names = [] + for items in secret_dict: + secret_names.append(items['Name']) + print(f'{len(secret_names)} secret(s) available') + for name in secret_names: + print(name) + return secret_names + +def retrieve_secrets(sm_client): + response = sm_client.get_secrets( + + ) + + + +#retrieve secret +#so lambda can access totesy db +#so lambda connect to the db and then retrieve the data \ No newline at end of file diff --git a/test/test_secrets_manager.py b/test/test_secrets_manager.py new file mode 100644 index 0000000..86533bc --- /dev/null +++ b/test/test_secrets_manager.py @@ -0,0 +1,34 @@ +from src.secrets_manager import sm_client, create_secret, list_secret +import boto3 +from moto import mock_aws +import json +import pytest +import os + +pytest.fixture(scope='class') +def mock_aws_credentials(): + """Mocked AWS Credentials for moto.""" + os.environ["AWS_ACCESS_KEY_ID"] = "testing" + os.environ["AWS_SECRET_ACCESS_KEY"] = "testing" + os.environ["AWS_SECURITY_TOKEN"] = "testing" + os.environ["AWS_SESSION_TOKEN"] = "testing" + os.environ["AWS_DEFAULT_REGION"] = "eu-west-2" + +@pytest.fixture(scope='class') +def mock_sm_client(mock_aws_credentials): + with mock_aws(): + yield boto3.client('secretsmanager') + + +def test_create_secret_stores_secrets(mock_sm_client): + cohort_id = "test_cohort_id" + user = "test_user_id" + password = "test_password" + host = "test_host" + database = "test_database" + port = "test_port" + + secret_name = "test_secret" + response = create_secret(mock_sm_client, secret_name, cohort_id, user, password, host, database, port) + + assert response['Name'] == secret_name \ No newline at end of file -- cgit v1.2.3 From 47a7b818cdbbde6b6a5f30c533909d41d16355f0 Mon Sep 17 00:00:00 2001 From: Alex Schofield Date: Thu, 15 Aug 2024 14:50:41 +0100 Subject: test: trigger commit workflow --- test.py | 0 1 file changed, 0 insertions(+), 0 deletions(-) create mode 100644 test.py diff --git a/test.py b/test.py new file mode 100644 index 0000000..e69de29 -- cgit v1.2.3 From 1c80682afe1ace3ad96982233b90e255ed9b4a20 Mon Sep 17 00:00:00 2001 From: Alex Schofield Date: Thu, 15 Aug 2024 14:53:58 +0100 Subject: ci: remove check if py files exist since we have some python/terraform files now, we shouldn't require this now! --- .github/workflows/on-commit.yml | 16 ---------------- 1 file changed, 16 deletions(-) diff --git a/.github/workflows/on-commit.yml b/.github/workflows/on-commit.yml index e429651..2b6061c 100644 --- a/.github/workflows/on-commit.yml +++ b/.github/workflows/on-commit.yml @@ -6,23 +6,7 @@ on: - 'main' jobs: - check-if-py-files-exist: - runs-on: ubuntu-latest - outputs: - py_files_exist: ${{ steps.check.outputs.py_files_exist }} - steps: - - uses: actions/checkout@v2 - - id: check_files - run: | - if [ -n "$(find . -name '*.py')" ]; then - echo "::set-output name=py_files_exist::true" - else - echo "::set-output name=py_files_exist::false" - fi - quality-checks: - needs: check-if-py-files-exist - if: ${{ needs.check-if-py-files-exist.outputs.py_files_exist == 'true' }} runs-on: ubuntu-latest steps: - uses : actions/checkout@v2 -- cgit v1.2.3 From 65e899353bb71be9a087c5738e6b3c2abdda87e4 Mon Sep 17 00:00:00 2001 From: Alex Schofield Date: Thu, 15 Aug 2024 15:06:13 +0100 Subject: ci: update bandit command --- .github/workflows/on-commit.yml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/.github/workflows/on-commit.yml b/.github/workflows/on-commit.yml index 2b6061c..5f3aebf 100644 --- a/.github/workflows/on-commit.yml +++ b/.github/workflows/on-commit.yml @@ -27,7 +27,7 @@ jobs: black --check . - name: 'Python: Security' run: | - bandit -r . + bandit -lll */*.py *c/*.py safety check - name: 'Terraform: Setup' uses: hashicorp/setup-terraform@v3 -- cgit v1.2.3 From b3c2954488127ac165ab6ad0e4b09cf68456f3f9 Mon Sep 17 00:00:00 2001 From: Alex Schofield Date: Thu, 15 Aug 2024 15:13:14 +0100 Subject: ci: update checkout & python action versions --- .github/workflows/on-commit.yml | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/.github/workflows/on-commit.yml b/.github/workflows/on-commit.yml index 5f3aebf..01de8d7 100644 --- a/.github/workflows/on-commit.yml +++ b/.github/workflows/on-commit.yml @@ -9,9 +9,9 @@ jobs: quality-checks: runs-on: ubuntu-latest steps: - - uses : actions/checkout@v2 + - uses : actions/checkout@v4 - name : 'Python: Setup' - uses : actions/setup-python@v2 + uses : actions/setup-python@v5 with: python-version: 3.11 - name : 'Python: Install Dependencies' -- cgit v1.2.3 From acf776a3732de3676e6178c27b8a46b564f36ad3 Mon Sep 17 00:00:00 2001 From: Alex Schofield Date: Thu, 15 Aug 2024 15:16:12 +0100 Subject: ci: remove security checks --- .github/workflows/on-commit.yml | 4 ---- 1 file changed, 4 deletions(-) diff --git a/.github/workflows/on-commit.yml b/.github/workflows/on-commit.yml index 01de8d7..937aeaa 100644 --- a/.github/workflows/on-commit.yml +++ b/.github/workflows/on-commit.yml @@ -25,10 +25,6 @@ jobs: - name : 'Python: Formatting' run: | black --check . - - name: 'Python: Security' - run: | - bandit -lll */*.py *c/*.py - safety check - name: 'Terraform: Setup' uses: hashicorp/setup-terraform@v3 with: -- cgit v1.2.3 From 3ee88c87fbae7e9968c4fc7f9aae5e7f28581aad Mon Sep 17 00:00:00 2001 From: Alex Schofield Date: Thu, 15 Aug 2024 15:32:53 +0100 Subject: ci: separate python & terraform jobs --- .github/workflows/on-commit.yml | 7 +++++-- 1 file changed, 5 insertions(+), 2 deletions(-) diff --git a/.github/workflows/on-commit.yml b/.github/workflows/on-commit.yml index 937aeaa..e4eb4f8 100644 --- a/.github/workflows/on-commit.yml +++ b/.github/workflows/on-commit.yml @@ -6,7 +6,7 @@ on: - 'main' jobs: - quality-checks: + python-quality-checks: runs-on: ubuntu-latest steps: - uses : actions/checkout@v4 @@ -25,6 +25,9 @@ jobs: - name : 'Python: Formatting' run: | black --check . + terraform-quality-checks: + runs-on: ubuntu-latest + steps: - name: 'Terraform: Setup' uses: hashicorp/setup-terraform@v3 with: @@ -37,4 +40,4 @@ jobs: run: terraform init -backend=false - name: 'Terraform: Validate' working-directory: ./terraform - run: terraform validate + run: terraform validate \ No newline at end of file -- cgit v1.2.3 From 6964625c65ae1552b8182891cf47997e480cce5e Mon Sep 17 00:00:00 2001 From: Alex Schofield Date: Thu, 15 Aug 2024 15:34:03 +0100 Subject: fix(ci): correct terraform folder path --- .github/workflows/on-commit.yml | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) diff --git a/.github/workflows/on-commit.yml b/.github/workflows/on-commit.yml index e4eb4f8..355729e 100644 --- a/.github/workflows/on-commit.yml +++ b/.github/workflows/on-commit.yml @@ -33,11 +33,11 @@ jobs: with: terraform_version: latest - name: 'Terraform: Formatting' - working-directory: ./terraform + working-directory: terraform run: terraform fmt -check -recursive - name: 'Terraform: Initialise' - working-directory: ./terraform + working-directory: terraform run: terraform init -backend=false - name: 'Terraform: Validate' - working-directory: ./terraform + working-directory: terraform run: terraform validate \ No newline at end of file -- cgit v1.2.3 From e5f2d8c98dd029bfb9926c35002abcf998510cf7 Mon Sep 17 00:00:00 2001 From: Alex Schofield Date: Thu, 15 Aug 2024 15:37:26 +0100 Subject: fix(ci): add missing checkout action --- .github/workflows/on-commit.yml | 1 + 1 file changed, 1 insertion(+) diff --git a/.github/workflows/on-commit.yml b/.github/workflows/on-commit.yml index 355729e..a4e66bb 100644 --- a/.github/workflows/on-commit.yml +++ b/.github/workflows/on-commit.yml @@ -28,6 +28,7 @@ jobs: terraform-quality-checks: runs-on: ubuntu-latest steps: + - uses : actions/checkout@v4 - name: 'Terraform: Setup' uses: hashicorp/setup-terraform@v3 with: -- cgit v1.2.3 From 47f5abae5b1b033a805b08c2a00d7df0bb0dcd97 Mon Sep 17 00:00:00 2001 From: Alex Schofield Date: Thu, 15 Aug 2024 15:41:35 +0100 Subject: test(ci): add continue-on-error for debugging --- .github/workflows/on-commit.yml | 8 +++++++- 1 file changed, 7 insertions(+), 1 deletion(-) diff --git a/.github/workflows/on-commit.yml b/.github/workflows/on-commit.yml index a4e66bb..fd9ffb8 100644 --- a/.github/workflows/on-commit.yml +++ b/.github/workflows/on-commit.yml @@ -18,13 +18,16 @@ jobs: run: | python -m pip install --upgrade pip pip install flake8 pylint black bandit safety + continue-on-error: true - name : 'Python: Linting' run: | flake8 . find . -name "*.py" | xargs pylint + continue-on-error: true - name : 'Python: Formatting' run: | black --check . + continue-on-error: true terraform-quality-checks: runs-on: ubuntu-latest steps: @@ -36,9 +39,12 @@ jobs: - name: 'Terraform: Formatting' working-directory: terraform run: terraform fmt -check -recursive + continue-on-error: true - name: 'Terraform: Initialise' working-directory: terraform run: terraform init -backend=false + continue-on-error: true - name: 'Terraform: Validate' working-directory: terraform - run: terraform validate \ No newline at end of file + run: terraform validate + continue-on-error: true \ No newline at end of file -- cgit v1.2.3 From cc13dc8d170d8c60dbb92e4e802a854bbdf81d5b Mon Sep 17 00:00:00 2001 From: Alex Schofield Date: Thu, 15 Aug 2024 16:03:00 +0100 Subject: test(ci): add terraform deploy job --- .github/workflows/deploy.yml | 37 +++++++++++++++++++++++++++++++++++++ 1 file changed, 37 insertions(+) create mode 100644 .github/workflows/deploy.yml diff --git a/.github/workflows/deploy.yml b/.github/workflows/deploy.yml new file mode 100644 index 0000000..6674373 --- /dev/null +++ b/.github/workflows/deploy.yml @@ -0,0 +1,37 @@ +name: deploy-terraform + +on: + push: + branches: + - main # Adjust the branch based on our deployment strategy + +jobs: + deploy-terraform: + name: Deploy Terraform + runs-on: ubuntu-latest + environment: test-env + steps: + - name: Checkout Repo + uses: actions/checkout@v4 + + - name: Install Terraform + uses: hashicorp/setup-terraform@v3 + + - name: Configure AWS Credentials + uses: aws-actions/configure-aws-credentials@v4 + with: + aws-access-key-id: ${{ secrets.AWS_ACCESS_KEY_ID }} + aws-secret-access-key: ${{ secrets.AWS_SECRET_ACCESS_KEY }} + aws-region: ${{ secrets.AWS_REGION }} + + - name: Terraform Init + working-directory: terraform + run: terraform init + + - name: Terraform Plan + working-directory: terraform + run: terraform plan + + - name: Terraform Apply + working-directory: terraform + run: terraform apply --auto-approve \ No newline at end of file -- cgit v1.2.3 From fc8e61c0e58df57195c6a33852a0a17ba34322c6 Mon Sep 17 00:00:00 2001 From: Alex Schofield Date: Thu, 15 Aug 2024 16:05:03 +0100 Subject: fix(ci): amend to trigger on commit to test-ci/... --- .github/workflows/deploy.yml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/.github/workflows/deploy.yml b/.github/workflows/deploy.yml index 6674373..372d0b3 100644 --- a/.github/workflows/deploy.yml +++ b/.github/workflows/deploy.yml @@ -3,7 +3,7 @@ name: deploy-terraform on: push: branches: - - main # Adjust the branch based on our deployment strategy + - test-ci/** # Adjust the branch based on our deployment strategy jobs: deploy-terraform: -- cgit v1.2.3 From c9bf342c8f6038a3f5397bfc8c53d251f27e7eec Mon Sep 17 00:00:00 2001 From: Ang Bel Date: Thu, 15 Aug 2024 16:45:47 +0100 Subject: procefss_and_upload_tables test in progress --- requirements.txt | 30 ++++++++++++++++++++++++++++ src/extract_lambda.py | 30 +++++++++++++++++++--------- tests/dummy_identical.csv | 4 ++++ tests/test_extract_lambda.py | 47 +++++++++++++++++++++----------------------- 4 files changed, 77 insertions(+), 34 deletions(-) create mode 100644 requirements.txt create mode 100644 tests/dummy_identical.csv diff --git a/requirements.txt b/requirements.txt new file mode 100644 index 0000000..6f383f9 --- /dev/null +++ b/requirements.txt @@ -0,0 +1,30 @@ +asn1crypto==1.5.1 +boto3==1.34.159 +botocore==1.34.159 +certifi==2024.7.4 +cffi==1.17.0 +charset-normalizer==3.3.2 +cryptography==43.0.0 +idna==3.7 +iniconfig==2.0.0 +Jinja2==3.1.4 +jmespath==1.0.1 +MarkupSafe==2.1.5 +moto==5.0.12 +packaging==24.1 +pg8000==1.31.2 +pluggy==1.5.0 +pycparser==2.22 +pytest==8.3.2 +pytest-mock==3.14.0 +python-dateutil==2.9.0.post0 +python-dotenv==1.0.1 +PyYAML==6.0.2 +requests==2.32.3 +responses==0.25.3 +s3transfer==0.10.2 +scramp==1.4.5 +six==1.16.0 +urllib3==2.2.2 +Werkzeug==3.0.3 +xmltodict==0.13.0 \ No newline at end of file diff --git a/src/extract_lambda.py b/src/extract_lambda.py index 56b47a6..fb2d7e8 100644 --- a/src/extract_lambda.py +++ b/src/extract_lambda.py @@ -6,6 +6,7 @@ from botocore.exceptions import ClientError import logging import json from datetime import datetime +import re logger = logging.getLogger() @@ -117,9 +118,16 @@ def process_and_upload_tables(db, existing_files, client=boto3.client('s3')): 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 """ - + ## NEW CODE + all_datetimes = [] + for file_names in existing_files.keys(): + datetime_str_on_s3 = ''.join(re.search(r'\/(.+/).+_(.+)\.csv',file_names).group(1,2)) + all_datetimes.append(datetime.strptime(datetime_str_on_s3, '%Y/%m/%d/%H:%M:%S')) + latest_timestamp = max(all_datetimes) + ## END OF NEW CODE + tables = db.run("SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE';") - + print(tables) for table in tables: table_name = table[0] rows = db.run(f"SELECT * FROM {table_name};") @@ -128,17 +136,21 @@ def process_and_upload_tables(db, existing_files, client=boto3.client('s3')): csv_file_path = f"/tmp/{table_name}.csv" with open(csv_file_path, "w", newline='') as file: writer = csv.writer(file) - column_names = [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};")] + column_names = [col_name[0] for col_name in db.run(f"SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS where table_name = '{table_name}';")] writer.writerow(column_names) writer.writerows(rows) - - s3_key = f"{table_name}/{datetime.today().year}/{datetime.today().month}/{datetime.today().day}/{table_name}_{datetime.now().strftime('%H:%M:%S')}.csv" + s3_key = datetime.strftime(datetime.today(),f'{table_name}/%Y/%m/%d/{table_name}_%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: + ## NEW CODE + latest_s3_object_key = datetime.strftime(latest_timestamp,f'{table_name}/%Y/%m/%d/{table_name}_%H:%M:%S.csv') + ## END OF NEW CODE + if existing_files[latest_s3_object_key] != new_csv_content: try: 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 + logger.error(f'Error uploading to S3: {e}') + else: + logger.info(f"No new data.") + \ No newline at end of file diff --git a/tests/dummy_identical.csv b/tests/dummy_identical.csv new file mode 100644 index 0000000..fdd8993 --- /dev/null +++ b/tests/dummy_identical.csv @@ -0,0 +1,4 @@ +Food_type,Flavour,Colour +Vegetable,Sour,Green +Berry,Sweet,Red + diff --git a/tests/test_extract_lambda.py b/tests/test_extract_lambda.py index 74d7e2c..e94a8a4 100644 --- a/tests/test_extract_lambda.py +++ b/tests/test_extract_lambda.py @@ -1,7 +1,7 @@ import pytest import boto3 from moto import mock_aws -from unittest.mock import patch +from unittest.mock import patch, MagicMock from unittest import TestCase from src.extract_lambda import list_existing_s3_files, connect_to_database, DBConnectionException, process_and_upload_tables import os @@ -81,32 +81,29 @@ class TestConnectToDatabase: with pytest.raises(DBConnectionException): connect_to_database() assert 'Interface error' in caplog.text - +''' class TestProcessAndUploadTables: - def test_error_process_and_upload_tables(mock_conn, mock_config, s3_client, caplog, mocker): + def test_error_process_and_upload_tables(mock_conn, mock_config, s3_client, caplog): 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.' }) + #### + queries = ["SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE';", + "SELECT * FROM Fruits;", + "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS where table_name = 'Fruits'"] + return_values = [[['Fruits']], + [['Vegetable','Sour','Green'],['Berry','Sweet','Red']], + [['Food_type'],['Flavour'],['Colour']]] + vals = dict(zip(queries,return_values)) + + #### + with patch('src.extract_lambda.connect_to_database') as mock_db: + mock_db().run.side_effects = return_values + s3_key = 'Fruits/2024/08/15/Fruits_16:46:30.csv' + existing_files = {s3_key: 'Food_type,Flavour,Colour\nFruit,Sour,Green\nBerry,Sweet,Red'} 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 + CreateBucketConfiguration={'LocationConstraint': 'eu-west-2'}) + s3_client.upload_file('tests/dummy_identical.csv', 'extract_bucket', s3_key) + process_and_upload_tables(mock_db(), existing_files, client=s3_client) + assert 'No new data.' in caplog.text +''' \ No newline at end of file -- cgit v1.2.3 From 486fb62af5568a70e22ded622072883758e9ffdf Mon Sep 17 00:00:00 2001 From: Alex Schofield Date: Thu, 15 Aug 2024 17:25:03 +0100 Subject: fix(tf): resolve naming issues with resources --- terraform/rds.tf | 42 ++++++++++++++++++++++-------------------- terraform/vars.tf | 4 ++-- 2 files changed, 24 insertions(+), 22 deletions(-) diff --git a/terraform/rds.tf b/terraform/rds.tf index 4b25c5f..88783b7 100644 --- a/terraform/rds.tf +++ b/terraform/rds.tf @@ -2,9 +2,9 @@ data "aws_availability_zones" "available" {} module "vpc" { source = "terraform-aws-modules/vpc/aws" - version = "2.77.0" + version = "5.12.1" - name = "${var.project_name}" + name = var.project_name cidr = "10.0.0.0/16" azs = data.aws_availability_zones.available.names public_subnets = ["10.0.4.0/24", "10.0.5.0/24", "10.0.6.0/24"] @@ -13,7 +13,7 @@ module "vpc" { } resource "aws_db_subnet_group" "Terrific-Totes-sub-gr" { - name = "TT-db-subnet" + name = "tt-db-subnet" subnet_ids = module.vpc.public_subnets tags = { @@ -45,7 +45,7 @@ resource "aws_security_group" "rds" { } resource "aws_db_parameter_group" "Terrific-Totes-param-gr" { - name = "TT-db-param" + name = "tt-db-param" family = "postgres14" parameter { @@ -54,25 +54,27 @@ resource "aws_db_parameter_group" "Terrific-Totes-param-gr" { } } -resource "aws_db_instance" "Terrific-Totes-rds" { - db_name = "${var.project_name}" - instance_class = "db.t3.micro" - allocated_storage = 5 - engine = "postgres" - engine_version = "14.1" - username = "user credentials for the root user" # we could use .env here - password = "user password for the root user" # we could use .env here +resource "aws_db_instance" "terrific-totes-rds" { + db_name = var.project_name + instance_class = "db.t3.micro" + allocated_storage = 5 + engine = "postgres" + engine_version = "14.10" + username = "totes" + password = "totes123" + # username = "user credentials for the root user" # we could use .env here + # password = "user password for the root user" # we could use .env here ### alternatively to providing username nad password we can specify: -# resource "aws_kms_key" "example_key" { -# description = "Example KMS Key" -# } -# within the resource: -# manage_master_user_password = true -# master_user_secret_kms_key_id = aws_kms_key.example.key_id -# } + # resource "aws_kms_key" "example_key" { + # description = "Example KMS Key" + # } + # within the resource: + # manage_master_user_password = true + # master_user_secret_kms_key_id = aws_kms_key.example.key_id + # } db_subnet_group_name = aws_db_subnet_group.Terrific-Totes-sub-gr.name vpc_security_group_ids = [aws_security_group.rds.id] parameter_group_name = aws_db_parameter_group.Terrific-Totes-param-gr.name publicly_accessible = false skip_final_snapshot = true -} \ No newline at end of file +} diff --git a/terraform/vars.tf b/terraform/vars.tf index d5cdafb..3c88731 100644 --- a/terraform/vars.tf +++ b/terraform/vars.tf @@ -29,8 +29,8 @@ variable "load_lambda_name" { } variable "project_name" { - type = string - default = "Terrific-Totes" + type = string + default = "tt" } data "aws_caller_identity" "current" {} -- cgit v1.2.3