SqlAlchemy - Retrieve long text from oracle db

Question

I'm trying to run few queries/stored procs which will return a long text (varchar2/clob), eg: Table/View/etc ddl. Will be getting the long text, do some manipulation & store in a flat file.

I'm getting below output which is a truncated version (highlighted) of the whole thing:

("DROP VIEW <<view>>;\n\n/* Formatted on 27/05/2022 7:08:33 PM (QP5 v5.354) */\nCREATE OR REPLACE FORCE 
VIEW <<view>> ... **(1097 characters truncated)** ... tx', '<<some value>>'),\n                                     'YYYYMMDD');
\n\n\nGRANT <<rights>>> ON <<view>> TO <<user>>;\n",)

I'm using below code:

conn_db = create_engine('oracle://<<user>>:<<pass>>@<<db_service>>')`
with conn_db.connect() as connection:
    res = connection.execute("<<query/proc>>")
    for row in res:
        print(row) # this returns the above output

I've tried setting the arraysize while engine creation but didn't work.

arraysize is a tuning parameter that affects internal buffer sizing when fetching multiple rows. It is not directly related to LOBs — Christopher Jones
– Christopher Jones, Commented May 28, 2022 at 22:58

Gord Thompson · Accepted Answer · 2022-05-28 14:31:42Z

print(row) is truncating the string for display purposes. The actual string is unaffected:

import sqlalchemy as sa

engine = sa.create_engine("oracle+cx_oracle://scott:[email protected]/?service_name=xepdb1")

s = " ".join(["spam" for i in range(200)])
with engine.connect() as conn:
    row = conn.exec_driver_sql("SELECT :s AS spam FROM DUAL", {"s": s}).one()
    print(row)
    # ('spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam ... (701 characters truncated) ... spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam',)
    #
    # [scroll right to see "... (701 characters truncated) ..."]
    #
    spam = row.spam
    print(len(spam))  # 999
    print(spam)
    # spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam spam

Collectives™ on Stack Overflow

SqlAlchemy - Retrieve long text from oracle db

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related