Connect to Amazon Redshift using ODBC from Python on Windows
Introduction
In this tutorial, we will walk through how to connect to Amazon RedShift from Python on a Windows machine.
Install DataDirect Amazon RedShift ODBC driver
1. Download the Progress DataDirect ODBC Driver for Amazon RedShift.
2. Run the executable to install the RedShift ODBC driver.
Configure RedShift Connection
1. Open ODBC Administrator and click on Add to create the connection. Choose DataDirect 8.0 Amazon Redshift Wire Protocol as the driver.
2. On the configuration window, fill in the Hostname, Port, Database as shown below.
3. Click on Test Connect, fill in your Username and Password to verify if you can connect successfully.
Connect to Redshift from Python
1. To access your Redshift from Python, you must install pyodbc package. Install it by running the below command
pip install pyodbc
2. Now use a sample Python program like below to access your data from Redshift
import pyodbcconn = pyodbc.connect('DSN=Redshift;UID=awsuser;PWD=awsPassword')cursor = conn.cursor()## Create Tablescursor.execute("CREATE TABLE Track ( TrackId INT NOT NULL, Name VARCHAR(200) NOT NULL, AlbumId INT, MediaTypeId INT NOT NULL, GenreId INT, Composer VARCHAR(220), Milliseconds INT NOT NULL, Bytes INT, UnitPrice NUMERIC(10,2) NOT NULL);")cursor.execute("INSERT INTO Track (TrackId, Name, AlbumId, MediaTypeId, GenreId, Composer, Milliseconds, Bytes, UnitPrice) VALUES (1, 'For Those About To Rock (We Salute You)', 1, 1, 1, 'Angus Young, Malcolm Young, Brian Johnson', 343719, 11170334, 0.99);")conn.commit()##Access Data using SQLcursor.execute("select * from Track")while True: row = cursor.fetchone() if not row: break print(row)##Access Data using SQLcursor.execute("select * from Artist")while True: row = cursor.fetchone() if not row: break print(row)
3. You can either use the connection string with DSN as shown above or you can use the connection string like below and skip configuring your connection in ODBC Administrator.
Driver={DataDirect 8.0 Amazon Redshift Wire Protocol}; HostName=redshift-cluster-1.cy1mp8nn6ntk.us-west-2.redshift.amazonaws.com; Database=dev; UID=awsuser; PWD=insert_your_master_user_password_here; Port=5439
Feel free to download the Progress DataDirect ODBC Driver for Amazon RedShift and try it out. If you have any questions contact us and we will be happy to help you.