Connecting to Redshift Data Using Python - Error: current transaction is aborted, commands ignored until end of transaction block

Question

I am trying to connect the Redshift data using Python (Jupyter Notebook). after running the last line, am getting this error:

InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block

could you please help me here.

import psycopg2
con=psycopg2.connect(dbname= 'TBD', host='TBD', 
port= 'TBD', user= 'TBD', password= 'TBD')
cur = con.cursor()
cur.execute("SELECT site_id FROM dfa_std")

do you need a semi-colon at the end of your SQL? Possibly single quotes around the table name? (not sure on that one). Also add cur.fetchall() at the end — JD2775
I've tried with and without semi-colon, also with and without single quotes, did not work! — Mahsa
yep, when am running the first and second lines, there is no error. import psycopg2 con=psycopg2.connect(dbname= 'TBD', host='TBD', port= 'TBD', user= 'TBD', password= 'TBD') — Mahsa
are there any other lines before that error? about connection failing? can you see whether you can connect (from the same ip) using psql command line tool and if not - what error? — Jon Scott

Joe Harris Joe Harris · Accepted Answer · 2021-03-08T12:44:01

As well as psycopg2 you need have sqlalchemy and ipython-sql installed in the Notebook server's Python.

Create a file with your Redshift endpoint and credentials:

echo "{
  \"user_name\": \"my_user\",
  \"password\": \"my_pswd\",
  \"host_name\": \"my_cluster_endpoint\",
  \"port_num\": \"5439\",
  \"db_name\": \"my_db\"
}" > my.creds

Then try this in your notebook:

import sqlalchemy
import psycopg2
import simplejson

%reload_ext sql
%config SqlMagic.displaylimit = 25

with open("my.creds") as fh:
    creds = simplejson.loads(fh.read())
connect_to_db = "postgresql+psycopg2://" + \
                creds["user_name"] +':'+ creds["password"] +'@'+ \
                creds["host_name"] +':'+ creds["port_num"] +'/'+ creds["db_name"];
%sql $connect_to_db
%sql SELECT current_user, version();

Expected output:

'Connected: my_user@my_db'

current_user | version
 my_user     | PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.24238

Connecting to Redshift Data Using Python - Error: current transaction is aborted, commands ignored until end of transaction block

1 Answers