I have created a cluster in redshift and I am trying to connect to the cluster via python psycopg ... I am able to connect through sqlworkbench/j using jdbc driver but I am unable to do so in python. Here is my code
import psycopg2
import os
# Redshift Server Details
dsn_database = "dev"
dsn_hostname = "redshift-cluster-1.cdd5oumaebpd.ap-south-1.redshift.amazonaws.com:5439/dev"
dsn_port = "5439"
dsn_uid = "*****"
dsn_pwd = "*****"
con=psycopg2.connect(dbname= dsn_database, host=dsn_hostname,
port= dsn_port, user= dsn_uid, password= dsn_pwd)
I am getting the following error: sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not translate host name "host.amazonaws.com" to address: Unknown host
Please help!
Since your error that you received says "Unknown host" it gives you a clue where you can troubleshoot first.
Refer to the official psycopg2 documentation for the guidance on how to use the arguments. You can connect using the dsn parameter or you can use keyword arguments. Since you are using keyword arguments (the second option in the documentation), you should specify the host without the port and the database and see if that works. That means that dsn_hostname = "redshift-cluster-1.xxx.xxx.redshift.amazonaws.com".
This is very similar to this Stack Overflow question and answer here, if you would like more information.
Also I really hope those aren't your actual credentials, if so please remove them for the security of your database!