As an alternative to returning a SYS_REFCUSOR variable to python, using Implicit Results is a bit easier and requires less code.
import oracledb
conn = oracledb.connect('MYDATABASE', user='foo', password='bar')
cur = conn.cursor()
cur.execute(
'''
DECLARE
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR
SELECT 1 foo
FROM dual
;
dbms_sql.return_result(l_cursor);
END;
'''
)
rows = cur.getimplicitresults()[0].fetchall()
assert rows == [(1,)]
Note that this cur.getimplicitresults() returns an array of cursors, one for each call to dbms_sql.return_result(...). So if you wanted to, you could actually open multiple cursors and return them this way.