CODEWITHSUNDEEP
pythoncx-oracle
bluefox
bluefox

Reputation: 175

cx_Oracle: select query following an insert produces no result

in my python code I insert a value into a table.

In the table, there is a sequence which automatically assigns an ID.

After the insert, I want to get this it back in to my python application:

import cx_Oracle, sys
with cx_Oracle.connect(user=ORA_USER,password=ORA_PWD,dsn=ORA_DSN) as conn:
                with conn.cursor() as cur:
                    cur.execute("Insert into my_table columns(data) values ('Hello')") 
                    conn.commit()

with cx_Oracle.connect(user=ORA_USER,password=ORA_PWD,dsn=ORA_DSN) as conn:
           with conn.cursor() as cur:
               r = cur.execute("select id from my_table where data = 'Hello'") 
               print(r)       
               if r is None:
                  print("Cannot retrieve ID")
                  sys.exit()

Unfortunately, the result set r is always "None" even though the value has been inserted properly (checked via sqldeveloper).

What am I doing wrong? I even open a new connection to be sure to grab the value...

Upvotes: 0

Views: 516

Answers (1)

Christopher Jones
Christopher Jones

Reputation: 10506

After calling execute() for a SELECT statement you need to call fetchone(), fetchmany() or fetchall() as shown in the cx_Oracle documentation SQL Queries.

Or you can use an iterator:

with connection.cursor() as cursor:
    try:

        sql = """select systimestamp from dual"""
        for r in cursor.execute(sql):
            print(r)

        sql = """select 123 from dual"""
        (c_id,) = cursor.execute(sql).fetchone()
        print(c_id)

    except oracledb.Error as e:
        error, = e.args
        print(sql)
        print('*'.rjust(error.offset+1, ' '))
        print(error.message)

However to get an automatically generated ID returned without the overhead of an additional SELECT, you can change the INSERT statement to use a RETURNING INTO clause. There is an example in the cx_Oracle documentation DML RETURNING Bind Variables that shows an UPDATE. You can use similar syntax with INSERT.

With the table:

CREATE TABLE mytable
  (myid NUMBER(11) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 1),
   mydata VARCHAR2(20));

You can insert and get the generated key like:

myidvar = cursor.var(int)
sql = "INSERT INTO mytable (mydata) VALUES ('abc') RETURNING myid INTO :bv"
cursor.execute(sql, bv=myidvar)
i, = myidvar.getvalue()
print(i)

If you just want a unique identifier you get the ROWID of an inserted row without needing a bind variable. Simple access cursor.lastrowid after executing an INSERT.

Upvotes: 1

Related Questions