Read AWS S3 CSV Column Names in Lambda

user_10634126 0
Viewed 2958

I am trying to write a script that collects the schema from an AWS Aurora Serverless MySQL database table, collects the column headers from a CSV file stored in an AWS S3 bucket, and only writes the CSV to the table if its column headers are a subset of the schema (e.g., if the table fields are ['Name', 'DOB', 'Height'] but the CSV fields are ['Name', 'DOB', 'Weight'] the script will throw an exception.

Thus far, I have successfully returned the table schema in an AWS Lambda function, and successfully read in the CSV file, but I am not sure how to get the column headers from the S3 object.

def return_db_schema(event):
    schema = []
    conn = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, connect_timeout=5)
    with conn.cursor() as cur:
        cur.execute('SHOW columns FROM SampleTable')
        conn.commit()
        cur.close()
        for row in cur:
            schema.append(list(row)[0])
        return schema

def return_csv_cols(event):
    s3 = boto3.client('s3')
    tester = s3.get_object(Bucket=s3_bucket, Key=test_key)
    contents = tester['Body'].read()

def main(event, context):
    print(return_db_schema(event))
    print()
    print(return_csv_cols(event))

I am unsure how to proceed from here (e.g., is there a way to do this without loading the CSV to a pandas DataFrame and calling df.columns() or something like that?).

aws-lambda boto3 csv amazon-s3 python
3 Answers

I have solved this with the following code:

    s3 = boto3.client('s3')
    tester = s3.get_object(Bucket=s3_bucket, Key=test_key)
    contents = tester['Body'].read().decode('UTF-8')
    cols = contents.split('\n')[0].split(',')
    return cols, contents
user_106341260

(As you're using aws lambda) I think the best way is :

import csv
csv_file = s3.get_object(Bucket="s3_bucket", Key="test_key")
read_csv = csv_file["Body"].read().decode('utf-8').splitlines(True) 
csv_data = csv.DictReader(read_csv)

Yes, csv library is available in lambda. Make sure you don't skip decode as it is use to decode the bytestream.

Now you can loop on csv_data and access using keys.

Hope this helps.

user_75146730
user_16869030
Related