Redshift python connector columns names are byte string

Question

Suppose I have the following table in redshift:

a | b
-----
1 | 2
3 | 4

If I want to extract it from Redshift to a pd.DataFrame I can do the following:

import redshift_connector
import pandas as pd

query = 'SELECT * FROM table'
conn = redshift_connector(user=user, host=host, password=password, port=port, database=database)

df = pd.read_sql_query(query, conn)

I'm using the following package redshift_connector. But the problem is that the name of the columns in df are byte-strings:

df['a']

This would return an error, since the name of the column is b'a'. Does anyone know any workaround for this? I already have written code using psycopg2 which uses normal strings, and thus would like have a solution that doesn't change too much of the code.

Edit:

Versions

Python = 3.9.7

Redshift-connector = 2.0.889

Pandas = 1.2.5

Pavel Slepiankou · Accepted Answer · 2021-10-29 04:41:34Z

5

You could just fix this with one line

df.columns = [col.decode("utf-8") for col in df.columns]

Or instead of using pd.read_sql_query use the connection approach suggested in the documentation

cursor: redshift_connector.Cursor = conn.cursor()
cursor.execute("SELECT * FROM table")

result: pd.DataFrame = cursor.fetch_dataframe()

answered Oct 29, 2021 at 4:41

Pavel Slepiankou

3,5752 gold badges27 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

brooke-white · Accepted Answer · 2023-03-31 23:00:34Z

2

This was fixed in v2.0.908 of redshift-connector

answered Mar 31, 2023 at 23:00

brooke-white

211 bronze badge

Collectives™ on Stack Overflow

Redshift python connector columns names are byte string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related