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

Answer 1

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)

Answer 2

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