SqlAlchemy - Retrieve long text from oracle db

user_7235615 0
Viewed 50

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.

cx-oracle sqlalchemy python-3.x scripting database
1 Answers

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:tiger@192.168.0.199/?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
user_21443900
Related