This is my python code and I want to connect my Amazon Redshift database to Python, but it is showing error in host.
Can anyone tell me the correct syntax? Am I passing all the parameters correctly?
con=psycopg2.connect("dbname = pg_table_def, host=redshifttest-icp.cooqucvshoum.us-west-2.redshift.amazonaws.com, port= 5439, user=me, password= secret")
This is the error:
OperationalError: could not translate host name "redshift://redshifttest-xyz.cooqucvshoum.us-west-2.redshift.amazonaws.com," to address: Unknown host
To access your Redshift data using Python, we will first need to connect to our instance. To connect to our Redshift instance, we use psycopg2. Following are the parameters that you need for connecting to any database: Name of the database.
It appears that you wish to run Amazon Redshift queries from Python code.
The parameters you would want to use are:
Database name
field when the cluster was created.Master user name
field when the cluster was created.Master user password
field when the cluster was created.redshifttest-xyz.cooqucvshoum.us-west-2.redshift.amazonaws.com
5439
For example:
con=psycopg2.connect("dbname=sales host=redshifttest-xyz.cooqucvshoum.us-west-2.redshift.amazonaws.com port=5439 user=master password=secret")
Old question but I just arrived here from Google.
The accepted answer doesn't work with SQLAlchemy, although it's powered by psycopg2:
sqlalchemy.exc.ArgumentError: Could not parse rfc1738 URL from string 'dbname=... host=... port=... user=... password=...'
What worked:
create_engine(f"postgresql://{REDSHIFT_USER}:{REDSHIFT_PASSWORD}@{REDSHIFT_HOST}:{REDSHIFT_PORT}/{REDSHIFT_DATABASE}")
Which works with psycopg2 directly too:
psycopg2.connect(f"postgresql://{REDSHIFT_USER}:{REDSHIFT_PASSWORD}@{REDSHIFT_HOST}:{REDSHIFT_PORT}/{REDSHIFT_DATABASE}")
Using the postgresql
dialect works because Amazon Redshift is based on PostgreSQL.
Hope it can help other people!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With