I have a use case that has to update a record in onprem database using python shell job .
The onprem database connection is working and being used in pyspark jobs to read data.
It has a pem file certificate and ssl security is enabled.
Now my issue is how do i pass the certificate information in connection string while working in aws glue job .
error : : DPY-6005: cannot connect to database (CONNECTION_ID=sdsfsfdfsdffdsfd+KYod/bJg==).
code :
import sys
import boto3
import oracledb
import ast
# Oracle connection details using Easy Connect string
session = boto3.session.Session()
client = session.client(service_name='secretsmanager')
def get_secret(secret_name):
# Create a Secrets Manager client
try:
get_secret_value_response = client.get_secret_value(SecretId=secret_name)
return get_secret_value_response
except Exception as e:
raise
def update_table(table_name):
try :
print("im here update table")
cred=get_secret("inbound-db/OraDim")
cred=ast.literal_eval(cred["SecretString"])
print(f"credentials {cred}")
user =cred["username"]
password =cred["password"]
'''
conn_str = 'jdbc:oracle:thin://@host:port/service_name'
conn_str=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host)
(PORT=port)))(CONNECT_DATA=(SERVICE_NAME=service_name))(SECURITY=(SSL_SERVER_DN_MATCH=True)))'
conn_str=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host)
(PORT=port)))(CONNECT_DATA=(SERVICE_NAME=service_name))(SECURITY=(SSL_SERVER_DN_MATCH=True)))'
conn_str=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host)
(PORT=port)))(CONNECT_DATA=(SID=sid_name))(SECURITY=(SSL_SERVER_DN_MATCH=True)))'
conn_str=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host)
(PORT=port)))(CONNECT_DATA=(SID=sid_name))(SECURITY=(SSL_SERVER_DN_MATCH=True)))'
conn_str='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host)
(PORT=port)))(CONNECT_DATA=(SID=sid_name)))'
conn_str = 'jdbc:oracle:thin:@host:port/service_name'
connection = oracledb.connect(user="hr", password=userpwd,
host="dbhost.example.com", port=1521, sid="orcl")
conn_str = 'jdbc:oracle:thin:@host:port:service_name'
'''
#print (conn_str)
#conn = oracledb.connect(user=user, password=password,dsn=conn_str)
conn = oracledb.connect(user=user, password=password,host=host ,port=port, sid=sid)
cursor = conn.cursor()
print ("update started")
cursor.execute("UPDATE mytable SET load_completion_flag ='N' WHERE table_name ='xyz'")
conn.commit()
cursor.close()
conn.close()
return {
'statusCode': 200,
'body': json.dumps('Update successful')
}
except Exception as e:
raise
I have tried multiple below connection strings
conn_str = ‘jdbc:oracle:thin://@host:port/service_name’
conn_str=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host)
(PORT=port)))(CONNECT_DATA=(SERVICE_NAME=service_name))(SECURITY=(SSL_SERVER_DN_MATCH=True)))'
conn_str=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host)
(PORT=port)))(CONNECT_DATA=(SERVICE_NAME=service_name))(SECURITY=(SSL_SERVER_DN_MATCH=True)))'
conn_str=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host)
(PORT=port)))(CONNECT_DATA=(SID=sid_name))(SECURITY=(SSL_SERVER_DN_MATCH=True)))'
conn_str=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host)
(PORT=port)))(CONNECT_DATA=(SID=sid_name))(SECURITY=(SSL_SERVER_DN_MATCH=True)))'
conn_str='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host)
(PORT=port)))(CONNECT_DATA=(SID=sid_name)))'
conn_str = 'jdbc:oracle:thin:@host:port/service_name'
connection = oracledb.connect(user="hr", password=userpwd,
host="dbhost.example.com", port=1521, sid="orcl")
conn_str = 'jdbc:oracle:thin:@host:port:service_name'