1

I have a postgres database in aws that I can query from just fine using python and psycopg2. My issue is writing to an s3 bucket. I do not know how to do that. Supposable, you have to use boto3 and aws-lambda but I am not familiar with that. I've been trying to find something online that outlines the code but one link doesn't seem to have asked the question correctly: how do I send query data from postgres in AWS to an s3 bucket using python?. And the other, I don't understand how this example works A way to export psql table (or query) directly to AWS S3 as file (csv, json).

Here is what my code looks like at the moment:

import psycopg2
import boto3
import os
import io
#setting values to read

os.environ['AWS_ACCESS_KEY_ID'] = "XXXXXXXXXXXXX"

os.environ['AWS_SECRET_ACCESS_KEY'] = "XXXXXXXXXXXX"

endpoint = "rds_endpoint"
username = 'user_name'
etl_password = 'stored_pass'
database_name = 'db_name'
resource = boto3.resource('s3')
file_name = 'daily_export'
bucket = "my s3 bucket"

copy_query = '''select parent.brand as business_type
                , app.business_name as business_name 
            from hdsn_rsp parent
            join apt_ds app
            on parent.id = app.id'''

def handle(event, context):

    try:
        connection = psycopg2.connect(user= username
                                        , password= etl_password
                                        , host= endpoint 
                                        , port="5432"
                                        , database= database_name)


        cursor = connection.cursor()
        cursor.execute(copy_query)
        file = io.StringIO()
        #cursor.copy_expert(copy_query, file)
        #resource.Object(bucket, file_name+'.csv').put(Body=file.getvalue())
    
    except(Exception, psycopg2.Error) as error:
        print("Error connecting to postgres instance", error)
        
    finally:
        if connection:
            cursor.close()
            connection.close()
    
    return("query has executed and file in bucket")

If leave the commented part in then my code executes just fine(running from local machine), but when I uncomment it, remove the handler and put it in a function, I get my success back but nothing is in my s3 bucket. I thought I had permissions issue so I created a new user and didn't give permissions to that bucket so it would fail and it did, so permissions aren't an issue. But, I don't get what is going on with #cursor.copy_expert(copy_query, file) #resource.Object(bucket, file_name+'.csv').put(Body=file.getvalue()) because it's not placing the file in my bucket.

I'm new here and new to writing code in aws so please be patient with me as I am not entirely sure how to ask the question properly. I know this is a big ask, but I am so confused as to what to do. Could someone please assist me on what corrections I need to make?

26Cocktails
  • 43
  • 4

0 Answers0