Python 查询 Redshift 在 "Connection reset by peer" 上失败，但适用于 dbeaver

Question

我正在尝试使用python查询我的Redshift DB，我尝试了以下两种方法:

与<<p> strong> sqlalchemy :

connection_string = "redshift+psycopg2://%s:%s@%s:%s/%s" % (USER, PASS, HOST, str(PORT), DATABASE)
engine = sa.create_engine(connection_string)
session = sessionmaker()
session.configure(bind=engine)
sess = session()
sess.execute('SELECT * FROM MY_TABLE LIMIT 1;') 

与<<p> strong> redshift_connector :

conn = redshift_connector.connect(
host=HOST,
port=PORT,
database=DATABASE,
user=USER,
password=PASS)
cursor = conn.cursor()
cursor.execute('SELECT * FROM MY_TABLE LIMIT 1;')
all_results =  cursor.fetchall()
conn.close()

两者都返回'连接重置由同行'，而当我试图使用DBeaver连接时，我能够运行此查询没有任何问题

我可能错过了什么?

Answer 1

我遇到了同样的错误。在我的例子中，它与SSL有关。您可以配置ssl或禁用它。禁用它,你可以使用以下选项:

sqlalchemy:

connection_string = "redshift+psycopg2://%s:%s@%s:%s/%s" % (USER, PASS, HOST, str(PORT), DATABASE)
engine = sa.create_engine(connection_string, connect_args={'ssl': False})
session = sessionmaker()
session.configure(bind=engine)
sess = session()
results = sess.execute('SELECT * FROM MY_TABLE LIMIT 1;')
results.fetchall()

与redshift_connector:

conn = redshift_connector.connect(host=HOST,
port=PORT,
database=DATABASE,
user=USER,
password=PASS,
ssl=False)
cursor = conn.cursor()
cursor.execute('SELECT * FROM MY_TABLE LIMIT 1;')
all_results =  cursor.fetchall()