redshift_connector

redshift_connector is the Amazon Redshift connector for Python. Easy integration with pandas and numpy, as well as support for numerous Amazon Redshift specific features help you get the most out of your data

Supported Amazon Redshift features include:

• IAM authentication
• Identity provider (IdP) authentication
• Redshift specific data types

This pure Python connector implements Python Database API Specification 2.0.

Getting Started

Install from Binary

Install from Source

You may install from source by cloning this repository.

$ git clone https://github.com/aws/amazon-redshift-python-driver.git
$ cd redshift_connector
$ pip install .

Tutorials

• 001 - Connecting to Amazon Redshift
• 002 - Data Science Library Integrations
• 003 - Amazon Redshift Feature Support
• 004 - Amazon Redshift Datatypes

We are working to add more documentation and would love your feedback. Please reach out to the team by opening an issue or starting a discussion to help us fill in the gaps in our documentation.

Integrations

redshift_connector integrates with various open source projects to provide an interface to Amazon Redshift. Please open an issue with our project to request new integrations or get support for a redshift_connector issue seen in an existing integration.

• apache-airflow
• querybook
• sqlalchemy-redshift

Basic Example

import redshift_connector

# Connects to Redshift cluster using AWS credentials
conn = redshift_connector.connect(
    host='examplecluster.abc123xyz789.us-west-1.redshift.amazonaws.com',
    database='dev',
    user='awsuser',
    password='my_password'
 )

cursor: redshift_connector.Cursor = conn.cursor()
cursor.execute("create Temp table book(bookname varchar,author varchar)")
cursor.executemany("insert into book (bookname, author) values (%s, %s)",
                    [
                        ('One Hundred Years of Solitude', 'Gabriel García Márquez'),
                        ('A Brief History of Time', 'Stephen Hawking')
                    ]
                  )
cursor.execute("select * from book")

result: tuple = cursor.fetchall()
print(result)
>> (['One Hundred Years of Solitude', 'Gabriel García Márquez'], ['A Brief History of Time', 'Stephen Hawking'])

Enabling autocommit

Following the DB-API specification, autocommit is off by default. It can be turned on by using the autocommit property of the connection.

# Make sure we're not in a transaction
conn.rollback()
conn.autocommit = True
conn.run("VACUUM")
conn.autocommit = False

Configuring paramstyle

Paramstyle can be set on both a module and cursor level. When paramstyle is set on a module level e.g. redshift_connector.paramstyle = 'qmark', the user specified paramstyle is used for all subsequent cursors unless set on the cursor. When paramstyle is set on the cursor e.g. `cursor.paramstyle = 'qmark' the user specified paramstyle is only used for that cursor object.

# setting paramstyle to qmark on a module level
redshift_connector.paramstyle = 'qmark'


with redshift_connector.connect() as conn1:
    with conn1.cursor() as cursor1: # this cursor will use qmark paramstyle as it's been set on the module level
        pass

    with conn1.cursor() as cursor2:
        # setting paramstyle to numeric on the cursor level only this cursor will use numeric paramstyle
        cursor.paramstyle = 'numeric'

    with conn1.cursor() as cursor3: # this cursor will use qmark paramstyle as it's been set on the module level
        pass

 with redshift_connector.connect() as conn2:
    with conn2.cursor() as cursor1: # this cursor will use qmark paramstyle as it's been set on the module level
        pass

The module level default paramstyle used is format. Valid values for paramstyle include qmark, numeric, named, format, pyformat. The below example shows how to use various paramstyles after the paramstyle is set on the cursor.

When paramstyle is set to named or pyformat, parameters must be passed as a Python dictionary to the execute() method. Other paramstyles require parameters to be passed as a Python tuple or list.

# qmark
cursor.paramstyle = 'qmark'
sql = 'insert into foo(bar, jar) VALUES(?, ?)'
cursor.execute(sql, (1, "hello world"))

# numeric
cursor.paramstyle = 'numeric'
sql = 'insert into foo(bar, jar) VALUES(:1, :2)'
cursor.execute(sql, (1, "hello world"))

# named
cursor.paramstyle = 'named'
sql = 'insert into foo(bar, jar) VALUES(:p1, :p2)'
cursor.execute(sql, {"p1":1, "p2":"hello world"})

# format
cursor.paramstyle = 'format'
sql = 'insert into foo(bar, jar) VALUES(%s, %s)'
cursor.execute(sql, (1, "hello world"))

# pyformat
cursor.paramstyle = 'pyformat'
sql = 'insert into foo(bar, jar) VALUES(%(bar)s, %(jar)s)'
cursor.execute(sql, {"bar": 1, "jar": "hello world"})

Exception Handling

redshift_connector uses the guideline for exception handling specified in the Python DB-API. For exception definitions, please see redshift_connector/error.py

Example using IAM Credentials

IAM Credentials can be supplied directly to connect(...) using an AWS profile as shown below:

import redshift_connector

# Connects to Redshift cluster using IAM credentials from default profile defined in ~/.aws/credentials
conn = redshift_connector.connect(
    iam=True,
    database='dev',
    db_user='awsuser',
    password='',
    user='',
    cluster_identifier='examplecluster',
    profile='default'
 )

# ~/.aws/credentials
[default]
aws_access_key_id="my_aws_access_key_id"
aws_secret_access_key="my_aws_secret_access_key"
aws_session_token="my_aws_session_token"

# ~/.aws/config
[default]
region=us-west-2

If a region is not provided in ~/.aws/config or you would like to override its value, region may be passed to connect(...).

Alternatively, IAM credentials can be supplied directly to connect(...) using AWS credentials as shown below:

import redshift_connector

# Connects to Redshift cluster using IAM credentials from default profile defined in ~/.aws/credentials
conn = redshift_connector.connect(
    iam=True,
    database='dev',
    db_user='awsuser',
    password='',
    user='',
    cluster_identifier='examplecluster',
    access_key_id="my_aws_access_key_id",
    secret_access_key="my_aws_secret_access_key",
    session_token="my_aws_session_token",
    region="us-east-2"
 )

Integration with pandas

Retrieving query results as a pandas.DataFrame

import pandas
cursor.execute("create Temp table book(bookname varchar,author varchar)")
cursor.executemany("insert into book (bookname, author) values (%s, %s)",
                   [
                       ('One Hundred Years of Solitude', 'Gabriel García Márquez'),
                       ('A Brief History of Time', 'Stephen Hawking')

                   ])
cursor.execute("select * from book")
result: pandas.DataFrame = cursor.fetch_dataframe()
print(result)
>>                         bookname                 author
>> 0  One Hundred Years of Solitude  Gabriel García Márquez
>> 1        A Brief History of Time         Stephen Hawking

Insert data stored in a pandas.DataFrame into an Amazon Redshift table

import numpy as np
import pandas as pd

df = pd.DataFrame(
    np.array(
        [
            ["One Hundred Years of Solitude", "Gabriel García Márquez"],
            ["A Brief History of Time", "Stephen Hawking"],
        ]
    ),
    columns=["bookname", "author‎"],
)
with conn.cursor() as cursor:
    cursor.write_dataframe(df, "book")
    cursor.execute("select * from book; ")
    result = cursor.fetchall()

Integration with numpy

import numpy
cursor.execute("select * from book")

result: numpy.ndarray = cursor.fetch_numpy_array()
print(result)
>> [['One Hundred Years of Solitude' 'Gabriel García Márquez']
>>  ['A Brief History of Time' 'Stephen Hawking']]

Query using functions

cursor.execute("SELECT CURRENT_TIMESTAMP")
print(cursor.fetchone())
>> [datetime.datetime(2020, 10, 26, 23, 3, 54, 756497, tzinfo=datetime.timezone.utc)]

Connection Parameters

Supported Datatypes

redshift_connector supports the following Amazon Redshift datatypes. redshift_connector will attempt to treat unsupported datatypes as strings. Incoming data from Amazon Redshift is treated as follows:

Logging

redshift_connector uses logging for providing detailed error messages regarding IdP authentication. A do-nothing handler is enabled by default as to prevent logs from being output to sys.stderr.

Enable logging in your application to view logs output by redshift_connector as described in the documentation for Python logging module.

Client Transfer Protocol

redshift_connector requests the Amazon Redshift server use the highest transfer protocol version supported. As of v2.0.879 binary transfer protocol is requested by default. If necessary, the requested transfer protocol can be modified via the client_protocol_version parameter of redshift_connector.connect(...). Please see the Connection Parameters table for more details.

Getting Help

• Ask a question on Stack Overflow and tag it with redshift_connector
• Open a support ticket with AWS Support
• If you may have found a bug, please open an issue

Contributing

We look forward to collaborating with you! Please read through CONTRIBUTING before submitting any issues or pull requests.

Changelog Generation

An entry in the changelog is generated upon release using gitchangelog. Please use the configuration file, .gitchangelog.rc when generating the changelog.

Running Tests

You can run tests by using pytest test/unit. This will run all unit tests. Integration tests require providing credentials for an Amazon Redshift cluster as well as IdP attributes in test/config.ini.

Additional Resources

• LICENSE
• Python Database API Specification v2.0 (PEP 249)
• PostgreSQL Frontend/Backend Protocol