Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to connect Amazon Redshift to python

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

like image 622
vihaa_vrutti Avatar asked Jul 20 '17 10:07

vihaa_vrutti


People also ask

Can we query Redshift tables from Python?

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.


2 Answers

It appears that you wish to run Amazon Redshift queries from Python code.

The parameters you would want to use are:

  • dbname: This is the name of the database you entered in the Database name field when the cluster was created.
  • user: This is you entered in the Master user name field when the cluster was created.
  • password: This is you entered in the Master user password field when the cluster was created.
  • host: This is the Endpoint provided in the Redshift management console (without the port at the end): redshifttest-xyz.cooqucvshoum.us-west-2.redshift.amazonaws.com
  • port: 5439

For example:

con=psycopg2.connect("dbname=sales host=redshifttest-xyz.cooqucvshoum.us-west-2.redshift.amazonaws.com port=5439 user=master password=secret")
like image 191
John Rotenstein Avatar answered Sep 30 '22 22:09

John Rotenstein


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!

like image 31
GG. Avatar answered Sep 30 '22 23:09

GG.