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

Published by Ramasankar

Hi. I’m Ramasankar Molleti. I’m a passionate IT professional with over 14 years of experience on providing solutions for customers who are looking on cloud computing, Database Migration, Development, and Big Data. I love learning new technologies and share my knowledge to community. I am currently working as Sr Cloud Architect with focus on Cloud Infrastructure, Big Data. I work with developers to architect, build, and manage cloud infrastructure, and services. I have deeep knowledge and experience on working with various database platforms such as MS SQL Server, PostgeSQL, Oracle, MongoDB, Redshift, Dyanamodb, Amazon Aurora. I worked as Database Engineer, Database Administrator, BI Developer and successfully transit myself into Cloud Architect with focus on Cloud infranstructure and Big Data. I live in USA and put my thoughts down on this blog. If you want to get in touch with me, contact me on my Linkedin here: https://www.linkedin.com/in/ramasankar-molleti-23b13218/ My Certifications: Amazon: AWS Certified Solutions Architect – Professional AWS Certified DevOps Engineer – Professional certificate AWS Certified Big Data – Specialty AWS Certified Security – Specialty certificate AWS Certified Advanced Networking – Specialty certificate AWS Certified Solutions Architect – Associate Microsoft: Microsoft® Certified Solutions Associate: SQL Server 2012/2014 Microsoft Certified Professional Microsoft® Certified IT Professional: Database Administrator 2008 Microsoft® Certified Technology Specialist: SQL Server 2008, Implementation and Maintenance

13 thoughts on “AWS Lambda to connect to PostgreSQL and execute a function/query using Python

    1. 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

  1. 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.

    1. 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.

  2. thanks for the post.Its very helpful..iam working on a website for which backend database is Postgresql (hosted on aws).There are rating and reviews that a user will submit on the site and has and the data has to be entered in to postgresql.Is there any sample code that you can provide for this kind of use case or do you suggest using dynamo db as the database?

  3. Thanks for the response Ram.Initially database was decided as postgresql for the application.Can the same architecture implemented with postgresql?

  4. Thanks a lot for this blog. It really helped me a lot.
    Instead of giving username and password statically can we do it in dynamically. if so, then then how?

  5. Yes, There are multiple ways to do 1. Lambda environment variables 2. Parameter store 3. secret manager.
    Environment Variable Example:
    You can retrieve the values from lambda as
    os.environ[‘username’]
    os.environ[‘password’]

    Here username and password are two environment variables.
    For more information about Lambda environment variables, you may refer (https://docs.aws.amazon.com/lambda/latest/dg/configuration-envvars.html )

    I will write a blog post with all three examples soon.

Leave a Reply to Ramasankar Cancel 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

%d bloggers like this: