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
-
Download DataDirect ODBC Driver for Amazon Redshift for Windows.
-
Install the driver according to the procedure.
Set up a RedShift connection
-
Click "Add" from ODBC Administrator to define the connection. Select DataDirect 7.1 Amazon Redshift Wire Protocol as the driver.
-
In the settings window, enter the host name, port, and data source as shown below.
-
Click Test Connect and check if you can connect normally with your user name and password.
Connect to Redshift from Python
- To access Redshift from Python, install the pyodbc package. Please install with the following command.
pip install pyodbc
- 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)
- 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