Django/PostgreSQL: How to get PostgreSQL hint using django.db.connections and the cursor() function

Question

I am using Django for my app backend. Due to a legacy database, I had to write custom helper functions that execute raw SQL queries.

I have this particular rollback function that I wrote and I want to be able to get the RAISE EXCEPTION USING HINT that was generated by the supplied query.

When the RAISE clause in the query is encountered, the HttpResponse throws error 400 which is what I wanted. I get the HINT via the console when I print out the connection cursor.

What I want now is to be able to get and store that to a variable or pass it to the HttpResponse so I can prompt it in the frontend.

DRF API FUNCTION

def post(self, request):
  try:
    text = request.data.get("text")
    query = ("""
            START TRANSACTION;
                DO $$
                  DECLARE psid INT4;

                    BEGIN
                      SELECT personid INTO psid FROM profile.systemid WHERE id=%s;

                      IF psid IS NOT NULL THEN
                        INSERT INTO test (text) VALUES (%s)
                      ELSE
                        RAISE EXCEPTION USING HINT = 'Please check that you have filled your PDS Basic Personal info first';
                      END IF;
                    END;
                $$;
    """)
    unformatted_query_result = raw_sql_insert(query, "default", (
        text
    ))
    if unformatted_query_result:
      res = raw_sql_commit_with_notices("default")
      print(res)
      return Response({
            "success": True,
            "message": "A voluntary work history has been added successfully."
        }, status=status.HTTP_201_CREATED)
    else:
      res = raw_sql_rollback_with_notices("default")
      print(res)
      return Response({
            "success": False,
            "message": "There was a problem adding this voluntary work history."
        }, status=status.HTTP_400_BAD_REQUEST)
  except Exception as e:
    traceback.print_exc()
    return Response({
      "success": False,
      "message": "Internal System Error: " + str(e)
    }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)

HELPER FUNCTION

import traceback
from django.db import connections
from collections import namedtuple

def raw_sql_commit_with_notices(connection):
   cn = connections[connection].cursor()
   try:
     cn.execute('COMMIT; END TRANSACTION;')
     return cn
   except Exception as e:
     traceback.print_exc()
     return cn

def raw_sql_rollback_with_notices(connection):
    cn = connections[connection].cursor()
    try:
        cn.execute('ROLLBACK; END TRANSACTION;')
        print("MSG: \n\n" + str(cn))
        return cn
    except Exception as e:
        traceback.print_exc()
        return cn

CONSOLE RESPONSE

django.db.utils.InternalError: P0001
HINT:  Please check that you have filled your PDS Basic Personal info first
CONTEXT:  PL/pgSQL function inline_code_block line 55 at RAISE

MSG:

<django.db.backends.utils.CursorDebugWrapper object at 0x08E66590>
<django.db.backends.utils.CursorDebugWrapper object at 0x08E66590>
HTTP POST /jobnet/api/profile/pds/voluntary_work/ 400 [0.19, 127.0.0.1:64483]

Django/PostgreSQL: How to get PostgreSQL hint using django.db.connections and the cursor() function

0 Answers0