Data integration from Python app on Windows to Amazon Redshift with ODBC

Introduction

In this article, I will explain how to use the DataDirect driver to easily link data from a Python application on Windows to Amazon Redshift with ODBC.

Install DataDirect driver for Amazon Redshift

  1. Download DataDirect ODBC Driver for Amazon Redshift for Windows.

  2. Install the driver according to the procedure.

Set up a RedShift connection

  1. Click "Add" from ODBC Administrator to define the connection. Select DataDirect 7.1 Amazon Redshift Wire Protocol as the driver. image.png

  2. In the settings window, enter the host name, port, and data source as shown below. image.png

  3. Click Test Connect and check if you can connect normally with your user name and password.

Connect to Redshift from Python

  1. To access Redshift from Python, install the pyodbc package. Please install with the following command.
pip install pyodbc
  1. Access the Redshift data with the following Python program. (Please change the connection settings)
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)
  1. You can define the connection string as above, or use the following connection string to omit the connection settings in ODBC Administrator.
Driver={DataDirect 7.1 Amazon Redshift Wire Protocol}; HostName=Please set the host name; Database=Please set the DB name; UID=Please set user ID; PWD=Please set a password; Port=5439

It's very easy.

Reference article

Real-time access to Amazon Redshift

DataDirect for Redshift Tutorial

Recommended Posts

Data integration from Python app on Windows to Amazon Redshift with ODBC
Data integration from Python app on Linux to Amazon Redshift with ODBC
Copy data from Amazon S3 to Google Cloud Storage with Python (boto)
How to scrape image data from flickr with python
PIL with Python on Windows 8 (for Google App Engine)
Get data from database via ODBC with Python (Access)
ODBC access to SQL Server from Linux with Python
OpenJTalk on Windows10 (Speak Japanese with Python from environment construction)
[Linux] Copy data from Linux to Windows with a shell script
Notes on importing data from MySQL or CSV with Python
I tried changing the python script from 2.7.11 to 3.6.0 on windows10
Python 3.6 on Windows ... and to Xamarin.
Create folders from '01' to '12' with python
Getting started with Python 3.8 on Windows
Update Python on Mac from 2 to 3
Introduction to Data Analysis with Python P17-P26 [ch02 1.usa.gov data from bit.ly]
Get data from MySQL on a VPS with Python 3 and SQLAlchemy
Manipulating kintone data with Python & C Data ODBC Driver from AWS Lambda
Everything from building a Python environment to running it on Windows
[Note] Get data from PostgreSQL with Python
Run servo with Python on ESP32 (Windows)
Retrieving food data with Amazon API (Python)
[Kivy] How to install Kivy on Windows [Python]
Convert Excel data to JSON with python
Convert FX 1-minute data to 5-minute data with Python
Connecting from python to MySQL on CentOS 6.4
[Amazon Linux] Switching from Python 2 series to Python 3 series
Put Cabocha 0.68 on Windows and try to analyze the dependency with Python
Steps to create a Python virtual environment with VS Code on Windows
Put MicroPython on Windows to run ESP32 on Python
ODBC connection to FileMaker 11 Server Advanced with Python 3
Strategy on how to monetize with Python Java
Vienna with Python + Flask web app on Jenkins
GUI automation with Python x Windows App Driver
Install OpenCV 4.0 and Python 3.7 on Windows 10 with Anaconda
[Python] How to read data from CIFAR-10 and CIFAR-100
Convert Windows epoch values to date with python
I tried to get CloudWatch data with Python
Folium: Visualize data on a map with Python
[Python] Flow from web scraping to data analysis
PHP and Python integration from scratch on Laravel
Boot CentOS 8 from Windows 10 with Wake On LAN
Write CSV data to AWS-S3 with AWS-Lambda + Python
[Python] How to install OpenCV on Anaconda [Windows]
From Python environment construction to virtual environment construction with anaconda
Run Python on Apache to view InfluxDB data
Connect to MySQL with Python on Raspberry Pi
Extract data from a web page with Python
A real way for people using python 3.8.0-2 from windows to work with multibyte characters
How to embed mod_wsgi into Apache on Python Windows
Problems with windows python being called on pipenv on WSL
I tried to implement Minesweeper on terminal with python
App development to tweet in Python from Visual Studio 2017
Yum command to access MySQL with Python 3 on Linux
Reading Note: An Introduction to Data Analysis with Python
Steps from installing Python 3 to creating a Django app
Interact with Python on Android from PC via adb
From buying a computer to running a program with python
I want to AWS Lambda with Python on Mac!
How to use Django on Google App Engine / Python
I tried to analyze J League data with Python