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.

Choose Redshift driver

2.       On the configuration window, fill in the Hostname, Port, Database as shown below.

Create DSN

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 pyodbc
 
conn = pyodbc.connect('DSN=Redshift;UID=awsuser;PWD=awsPassword')
 
cursor = conn.cursor()
 
## Create Tables
cursor.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 SQL
cursor.execute("select * from Track")
while True:
    row = cursor.fetchone()
    if not row:
        break
    print(row)
 
##Access Data using SQL
cursor.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.

 

ODBC TUTORIAL

Connect to Amazon Redshift using ODBC from Python on Windows

View all Tutorials

Connect any application to any data source anywhere

A product specialist will be glad to get in
touch with you