AWS Lambda to connect to PostgreSQL and execute a function/query using Python

It’s been long time since i wrote a blog post. In this post i would like to show an example of lambda to connect to PostgreSQL database and execute the query.

  1. Build a PostgreSQL psycopg module in order to connect to database. I’ve built a custom compiled python library to connect to latest PostgreSQL 9.6 database using this. You may download the package from here 
  2. Write a sample lambda that updates the url in the table. It calls the function to update.
    import psycopg2
    def lambda_handler(event,context):
    conn_string = "dbname='test' port='5432' user='username' password='password' host='RDSEndpoint'"
    conn = psycopg2.connect(conn_string)
    cursor = conn.cursor()
    cursor.execute("select system_env_host('ramasankarmolleti.com')")
    conn.commit()
    cursor.close()
    print("working")
    
  3. You may also download python code here 
  4. Now, let’s create a lambda function
  5. Lambda_1
  6. In the above screenshot I’ve created a test_lambda_role iam role to get access to lambda services. Once you create the lambda, then combine both step1 and step2 as one zip file and upload the zip file in lambda you may download the combined zip file here
  7. Lambda_2
  8. Next, Here comes to access to Database from lambda. You need to create a security group that have access to PostgreSQL database from the lambda. I’ve created a security group and attached to lambda.
  9. Now, we have lambda deployed and run the lambda for testing. Click on test to test the lambda. This will prompt you to create test even as below
  10. Lambda_3.PNG
  11. After creating test even you can run the lambda by clicking test before that make sure lambda function name is updated to function name you created. In this example function name is UpdateHost_Python  as shown in below
  12. Lambda_4
  13. Now, Click on test to test the lambda.
  14. Lambda_5.PNG
  15.  Here you go lambda is successfully executed
  16. Lambda_6.PNG
  17. Let’s see the results in the database. You can see the results that host is updated to ramasankarmolleti.com.
  18. Update_Host_Python

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

LinkedIn

About Ramasankar

My name is Ramasankar Molleti, I have been working on various database and cloud systems (SQL Server, Oracle, PostgreSQL, DynamoDB, Redshift, Amazon, Azure) over 12 years, primarily focusing on Architect , Development, Administration and Business intelligence. I hold various Certifications such as Amazon Web services Solution Architect Associate, Microsoft certifications (Microsoft Certified Information Technology Professional, Microsoft Certified Technology Specialist, and Microsoft Certified Solution Associate).
This entry was posted in AWS, PostgreSQL, Uncategorized. Bookmark the permalink.

5 Responses to AWS Lambda to connect to PostgreSQL and execute a function/query using Python

  1. Shilpa says:

    Where did you get the library, most of them work from local windows , and then give error of “module not found” on aws lambda

    • Ramasankar says:

      You can build the library on any linux machine and zip it and upload to lambda. Here are the below steps you may follow to build. At the time of writing this blog i used python 2.7 version. you can build module on python 3.6 as below

      # Download postgres source code
      wget -c https://ftp.postgresql.org/pub/source/v11.4/postgresql-11.4.tar.gz
      # Download psycopg2 source code
      git clone https://github.com/psycopg/psycopg2
      # Unzip the module
      tar zxfv postgresql-11.4.tar.gz
      # Create local install direcotry
      mkdir ./pgsql-11.4
      #compile and install
      cd postgresql-11.4 ; ./configure –prefix=/home/postgres/pgsql-11.4 –without-readline –without-zlib
      make ; make install
      # Edit psycopg2 setup config
      cd psycopg2 ;vim setup.cfg
      # Change:
      static_libpq=1
      pg_config=/home/postgres/pgsql-11.4/bin/pg_config
      # Compile psycopg2
      python3.6 setup.py build
      # Package psycopg2
      mv ~/psycopg2 ~/psycopg2_source
      cp -r ~/psycopg2_source/build/lib.linux-x86_64-3.6/psycopg2/ ~/
      tar cvfz psycopg2.tgz psycopg2/

      hope this helps. thanks

  2. Amishi Shah says:

    Can anyone provide the same example in Powershell ?

  3. Ali says:

    Hi,

    this is a nice post, it works for me. But I have one small problem that I think it is relate to psycopg2, I am trying to execute cur.execute(“create tablespace ts_data owner my_owner location ‘/data/ts_data’;create tablespace ts_2015 owner my_owner location ‘/data/ts_2015’;”)

    And I am getting error back: psycopg2.InternalError: CREATE TABLESPACE cannot run inside a transaction block

    I searched a lot in order to get some solutions on this topic, but nothing worked for me.

    Any ideas on how to resolve this ?
    Thanks.

    • Ramasankar says:

      Hi Ali, That’s the behaviour of POSTGRESQL. You cannot run inside a transaction block. You can do either of these to mitigate issue. 1. Turn on autocommit 2. Put End; at the begging of the statement. Hope this helps.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s