• Write a Python script that:

  1. Connects to the SQLite database.

  2. Queries the data and saves it locally as employees.csv.

  3. Uploads employees.csv to AWS S3 using boto3.

Assignment Solution

Step 1: Install Required Libraries

Install the necessary Python libraries:

bash

Copy code

pip install pandas boto3 sqlite3

Step 2: Write the Python Script

Here’s an example script that completes all steps:

python

Copy code

import sqlite3

import pandas as pd

import boto3

from botocore.exceptions import NoCredentialsError

# Function to fetch data from the SQLite database

def fetch_data_from_db(db_path, query):

    try:

        connection = sqlite3.connect(db_path)

        print("Connected to SQLite database.")

        # Execute the query and fetch the data

        data = pd.read_sql_query(query, connection)

        # Close the connection

        connection.close()

        print("Data fetched successfully.")

        return data

    except Exception as e:

        print(f"Error querying the database: {e}")

        return None

# Function to save data as a CSV file locally

def save_data_as_csv(data, file_path):

    try:

        data.to_csv(file_path, index=False)

        print(f"Data saved locally as '{file_path}'.")

    except Exception as e:

        print(f"Error saving data as CSV: {e}")

# Function to upload the CSV file to S3

def upload_csv_to_s3(bucket_name, file_name, s3_key):

    s3 = boto3.client('s3')

    try:

        s3.upload_file(file_name, bucket_name, s3_key)

        print(f"File '{file_name}' uploaded successfully to S3 bucket '{bucket_name}' as '{s3_key}'.")

    except FileNotFoundError:

        print(f"File '{file_name}' not found.")

    except NoCredentialsError:

        print("AWS credentials not found.")

    except Exception as e:

        print(f"Error uploading file to S3: {e}")

# Main function

if __name__ == "__main__":

    # SQLite database and AWS S3 configuration

    db_path = "employees.db"  # Path to your SQLite database

    query = "SELECT * FROM employees"  # SQL query to fetch data

    local_csv = "employees.csv"  # Local file name for the CSV

    bucket_name = "your-s3-bucket-name"  # Replace with your S3 bucket name

    s3_key = "uploaded_data/employees.csv"  # Destination key in S3

    # Step 1: Fetch data from the SQLite database

    data = fetch_data_from_db(db_path, query)

    # Step 2: Save the data locally as a CSV file

    if data is not None:

        save_data_as_csv(data, local_csv)

        # Step 3: Upload the CSV file to S3

        upload_csv_to_s3(bucket_name, local_csv, s3_key)

How to Run the Script

1. Set Up Your Environment:

   • Place the employees.db SQLite database in the same directory as the script.

   • Replace your-s3-bucket-name with the name of your AWS S3 bucket.

Run the Script: Execute the script using Python:
bash
Copy code
python upload_employees_to_s3.py

2. 
   

3. Verify Output:

   • Check the employees.csv file in your local directory.

   • Log in to the AWS S3 console to verify the file was uploaded successfully.