How to insert data in redshift using either of boto3 or psycopg2 python libraries

Question

Which library is best to use among "boto3" and "Psycopg2" for redshift operations in python lambda functions:

• Lookup for a table in redshift cluster
• Create a table in redshift cluster
• Insert data in redshift cluster

I would appretiate if i am answered with following:

• python code for either of the library that addresses all of the above 3 needs.

Thanks in Advance!!

Answer 1

Sample basic python code for all three operations using boto3.

import json
import boto3

clientdata = boto3.client('redshift-data')

# looks up table and returns true if found
def lookup_table(table_name):
  response = clientdata.list_tables(
    ClusterIdentifier='redshift-cluster-1',
    Database='dev',
    DbUser='awsuser',
    TablePattern=table_name
  )
  print(response)
  if ( len(response['Tables']) == 0 ):
    return False
  else:
    return True

# creates table with one integer column
def create_table(table_name):
  sqlstmt = 'CREATE TABLE '+table_name+' (col1 integer);'
  print(sqlstmt)
  response = clientdata.execute_statement(
    ClusterIdentifier='redshift-cluster-1',
    Database='dev',
    DbUser='awsuser',
    Sql=sqlstmt,
    StatementName='CreateTable'
  )
  print(response)

# inserts one row with integer value for col1
def insert_data(table_name, dval):
  print(dval)
  sqlstmt = 'INSERT INTO '+table_name+'(col1) VALUES ('+str(dval)+');'
  response = clientdata.execute_statement(
    ClusterIdentifier='redshift-cluster-1',
    Database='dev',
    DbUser='awsuser',
    Sql=sqlstmt,
    StatementName='InsertData'
  )
  print(response)

result = lookup_table('date')
if ( result ):
  print("Table exists.")
else:
  print("Table does not exist!")

create_table("testtab")
insert_data("testtab", 11)

I am not using Lambda, instead executing it just from my shell.
Hope this helps. Assuming credentials and default region are already set up for the client.