How to connect to a cluster in Amazon Redshift using SQLAlchemy?

Question

In Amazon Redshift's Getting Started Guide, it's mentioned that you can utilize SQL client tools that are compatible with PostgreSQL to connect to your Amazon Redshift Cluster.

In the tutorial, they utilize SQL Workbench/J client, but I'd like to utilize python (in particular SQLAlchemy). I've found a related question, but the issue is that it does not go into the detail or the python script that connects to the Redshift Cluster.

I've been able to connect to the cluster via SQL Workbench/J, since I have the JDBC URL, as well as my username and password, but I'm not sure how to connect with SQLAlchemy.

Based on this documentation, I've tried the following:

from sqlalchemy import create_engine
engine = create_engine('jdbc:redshift://shippy.cx6x1vnxlk55.us-west-2.redshift.amazonaws.com:5439/shippy')

ERROR:

Could not parse rfc1738 URL from string 'jdbc:redshift://shippy.cx6x1vnxlk55.us-west-2.redshift.amazonaws.com:5439/shippy'

Answer 1

I was running into the exact same issue, and then I remembered to include my Redshift credentials:

eng = create_engine('postgresql://[LOGIN]:[PASSWORD]@shippy.cx6x1vnxlk55.us-west-2.redshift.amazonaws.com:5439/shippy')

Answer 2

I don't think SQL Alchemy "natively" knows about Redshift. You need to change the JDBC "URL" string to use postgres.

jdbc:postgres://shippy.cx6x1vnxlk55.us-west-2.redshift.amazonaws.com:5439/shippy

Alternatively, you may want to try using sqlalchemy-redshift using the instructions they provide.