Redshift / SQLAlchemy: 25P02 error/warning with all queries

Question

I'm querying Redshift with SQLAlchemy over an ODBC connection. No matter what I do, I get the following warning:

C:\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py:324: SAWarning: Exception attempting to detect unicode returns: ProgrammingError("(pyodbc.ProgrammingError) ('25P02', '[25P02] [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 25P02] ERROR: current transaction is aborted, commands ignored until end of transaction block\n (30) (SQLExecDirectW)')") "detect unicode returns: %r" % de)

It is not an error, just a warning. I still get the correct results. For example, simple queries like this:

from sqlalchemy import create_engine

engine = create_engine("mssql+pyodbc://@MY_CONN")
with engine.connect() as conn:
    ct = conn.execute("SELECT COUNT(1) FROM my_table").scalar()
print(ct)

Will produce the correct count but still show that warning. I've done some research that indicates this might be related to autocommit options, but when I run the following code, I still get the warning, and this time with an incorrect result of 0:

ct = (
    conn.execute(text("SELECT COUNT(1) FROM my_table").execution_options(autocommit=True)).scalar()
)

Besides I would think autocommit has nothing to do with read queries.

Any insights into this?

My guess is it's something to do with you using "mssql+pyodbc", which is intended for Microsoft SQL Server, with Amazon Redshift. You should try the postgresql dialect, or maybe github.com/sqlalchemy-redshift/sqlalchemy-redshift — Nathan Griffiths
This was the answer! But I can't use redshift+psycopg2 because I can't put the login in the clear and thus I must use ODBC. Redshift and postgresql don't seem to work over pyodbc so I am just skipping sqlalchemy and using pyodbc directly, which is fine for now. Anyway if you want to post your comment as an answer I will gladly accept it. Thanks again! — sparc_spread

Nathan Griffiths Nathan Griffiths · Accepted Answer · 2019-01-08T20:27:24

As per my comment, the probably cause of this error is the use of "mssql+pyodbc". This dialect is intended for Microsoft SQL Server and so is probably making incompatible metadata queries in the background, causing the warning.

To work with Redshift, try using a PostgreSQL dialect or a Redshift dialect (e.g. https://github.com/sqlalchemy-redshift/sqlalchemy-redshift).

Redshift / SQLAlchemy: 25P02 error/warning with all queries

1 Answers