How do I properly escape this query using oracledb for python

Question

I have the following query:

sql  = """SELECT * FROM CLINICAL_NOTES
              WHERE NOTE_TYPE IN 
              ( 'ED Notes',
                'ED Provider Notes',
                'ED Triage Notes',
                'H\&P' )"""

If I run this query directly in Oracle Developer by first executing

set escape on

this works as expected.

However, when I try running

cursor.execute('set escape on;')

I get the error DatabaseError: ORA-00922: missing or invalid option

Of course, without setting this, the sql statement above is invalid when executed in python, due to Oracle not knowing that the & is escaped.

Answer 1

set escape on is a SQL*Plus etc "client side" (aka tool) command and not a DB SQL statement so it won't work in Python. The DB doesn't know about it. This is why you have an error. (Also you don't pass trailing semi-colons in Python except for when using PL/SQL) There's nothing to escape in the statement. You should remove the backslash.

For me, the code:

    connection = oracledb.connect(user=un, password=pw, dsn=cs)

    with connection.cursor() as cursor:
        sql = """SELECT * FROM dual
                 WHERE dummy IN
                 ( 'ED Notes',
                   'ED Provider Notes',
                   'ED Triage Notes',
                   'H&P', 'X' )"""
        for r in cursor.execute(sql):
            print(r)

displays the expected output ('X',). Obviously with your table & data you will get different output.

If you still have problems, then update your question with a full Python snippet