python3.2 -使用cx-oracle更新clob

Question

我正在将一些代码从perl迁移到Python。我可以使用以下代码读取需要更新的CLOB：

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()

如何更新CLOB列？对于Perl，我选择for UPDATE，获取bin_locator，然后使用DBI中的ora_lob_write。

我正在寻找展示Python等价物的示例。

Answer 1

抱歉，我没有Python 3.2。这是在Python 2.7上。

在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>

现在，在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()

再回到Oracle：

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>

希望这能有所帮助。