CODEWITHSUNDEEP
pythonoracle-databasepython-3.xcx-oracle
Kevin
Kevin

Reputation: 137

python3.2 - Update clob using cx-oracle

I'm migrating some code from perl to Python. I can read the CLOB I need to update using the code below:

cur.execute("""
SELECT
    , a.Category
    , a.Status
    …
    , c.name
    , a.orig_operator
FROM tables where stuff
""")

for result in cur:
    startTimes = result[18].read( # stringify from lob
    stopTimes = result[19].read()
    lobsterCode = result[17].read()

How can I update one of the CLOB columns? With Perl I select for UPDATE, get the bin_locator then use ora_lob_write from DBI.

I'm looking for examples showing the Python equivalent.

Upvotes: 1

Views: 2223

Answers (1)

Tebbe
Tebbe

Reputation: 1372

Apologies, I don't have Python 3.2. This is on Python 2.7.

In Oracle:

scott@XE_11g> CREATE TABLE t (id NUMBER, c CLOB);

Table created.

scott@XE_11g> INSERT INTO t VALUES (1, 'Happy families are all alike; every unhappy family is unhappy in its own way.');

1 row created.

scott@XE_11g> INSERT INTO t VALUES (2, q'[You don't know about me without you have read a book by the name of The Adventures of Tom Sawyer; but that ain't no matter.]');

1 row created.

scott@XE_11g> COMMIT;

Commit complete.

scott@XE_11g>

Now, in Python:

import cx_Oracle

connection = cx_Oracle.connect('scott/tiger@XE')
cursor = connection.cursor()
new_clob = cursor.var(cx_Oracle.CLOB)
new_clob.setvalue(0,'It was a bright cold day in April, and the clocks were striking thirteen. ')
key_id = cursor.var(cx_Oracle.NUMBER)
key_id.setvalue(0,2)

cursor.execute("""UPDATE t
                  SET    c = :p_clob
                  WHERE  id = :p_key"""
,              p_clob = new_clob
,              p_key = key_id
               )
connection.commit()

And, back in Oracle again:

scott@XE_11g> SELECT c FROM t WHERE id = 2;

C
----------------------------------------------------------------------------------------------------
It was a bright cold day in April, and the clocks were striking thirteen.

scott@XE_11g>

Hope this helps.

Upvotes: 1

Related Questions