unrecognized configuration parameter "standard_conforming_strings" while querying from Redshift

Question

I am trying to connect to my redshift cluster using Sqlalchemy in a linux environment, but i am facing the following issue.

from sqlalchemy import create_engine
import pandas as pd
conn = create_engine('postgresql://connection string')
data_frame = pd.read_sql_query("SELECT * FROM schema.table", conn)

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) unrecognized configuration parameter "standard_conforming_strings"

I really dont understand what the issue is. It works perfectly fine in Windows.

PS: Not sure if this makes any difference but i have installed psycopg2-binary on the linux machine in contrast to psycopg2 on windows.

EDIT

1 .The version of pyscopg2 is windows is 2.9.3 while the version of pyscopg2-binary in linux is 2.9.6

2. The version of Sqlalchemy in windows is 1.4.39 while in linux is 2.0.16

Ephod

More information needed 1) What version of psycopg2 on Windows vs Linux? 2) What version of SQLAlchemy? Add answers as update to question. – Williamson

Answer 1

I have figured out the answer based on the comment by @Adrian . Just changed the version of Sql alchemy on the linux env to what i have on Windows and it works now.

Ephod

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Quickly

Answer 2

Use of pyscopg2 is now outdated for connecting to redshift.

For direct connection use redshift_connector - see https://docs.aws.amazon.com/redshift/latest/mgmt/python-connect-examples.html

import redshift_connector
conn = redshift_connector.connect(
     host='examplecluster.abc123xyz789.us-west-1.redshift.amazonaws.com',
     database='dev',
     port=5439,
     user='awsuser',
     password='my_password'
  )

For sqlAlchemy use redshift_connect and sqlalchemy-redshift - see https://aws.amazon.com/blogs/big-data/use-the-amazon-redshift-sqlalchemy-dialect-to-interact-with-amazon-redshift/

import sqlalchemy as sa
from sqlalchemy.engine.url import URL

# build the sqlalchemy URL
url = URL.create(
drivername='redshift+redshift_connector', # indicate redshift_connector driver and dialect will be used
host='<clusterid>.xxxxxx.<aws-region>.redshift.amazonaws.com', # Amazon Redshift host
port=5439, # Amazon Redshift port
database='dev', # Amazon Redshift database
username='awsuser', # Amazon Redshift username
password='<pwd>' # Amazon Redshift password
)

engine = sa.create_engine(url)

Lehrer

One downside to this package is that you cannot use SQLAlchemy 2.0, as the requirements are SQLAlchemy<2.0.0,>=0.9.2. Any thoughts on how to connect to redshift while using a more up-to-date version of SQLAlchemy? – Forbid

@CilantroDitrek sorry - I'm not a fan of the SQLAlchemy style of implementation so avoid using it – Lehrer

As Ditrek said, it can't support sqlalchemy 2.0. that's terrible – Bulldog

Answer 3

I had the same issue

File "/home/ec2-user/miniconda3/envs/test_env/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2339, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/ec2-user/miniconda3/envs/test_env/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
    self.dialect.do_execute(
  File "/home/ec2-user/miniconda3/envs/test_env/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) unrecognized configuration parameter "standard_conforming_strings"

resolved it by installing

sqlalchemy-redshift https://pypi.org/project/sqlalchemy-redshift/

what it did

 pip install sqlalchemy-redshift
Collecting sqlalchemy-redshift
  Downloading sqlalchemy_redshift-0.8.14-py2.py3-none-any.whl (38 kB)
Requirement already satisfied: packaging in /home/ec2-user/miniconda3/envs/test_env/lib/python3.10/site-packages (from sqlalchemy-redshift) (23.1)
Collecting SQLAlchemy<2.0.0,>=0.9.2
  Downloading SQLAlchemy-1.4.49-cp310-cp310-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_17_x86_64.manylinux2014_x86_64.whl (1.6 MB)
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 1.6/1.6 MB 73.1 MB/s eta 0:00:00
Requirement already satisfied: greenlet!=0.4.17 in /home/ec2-user/miniconda3/envs/test_env/lib/python3.10/site-packages (from SQLAlchemy<2.0.0,>=0.9.2->sqlalchemy-redshift) (2.0.2)
Installing collected packages: SQLAlchemy, sqlalchemy-redshift
  Attempting uninstall: SQLAlchemy
    Found existing installation: SQLAlchemy 2.0.19
    Uninstalling SQLAlchemy-2.0.19:
      Successfully uninstalled SQLAlchemy-2.0.19
Successfully installed SQLAlchemy-1.4.49 sqlalchemy-redshift-0.8.14

Ninnyhammer

Answer 4

sqlalchemy-redshift is pinned to sqlalchemy <2.0.0 (https://github.com/sqlalchemy-redshift/sqlalchemy-redshift/blob/main/setup.py#L24)

I ran into this error because I had a later version of sqlalchemy installed. Downgrading to <2.0.0 fixed the error.

Sugar