python-oracledb how to change arraysize for session

Question

Stack:

• oracledb==2.5.1
• sqlalchemy==2.0.25
• fastapi==0.105.0

How can I change Cursor's arraysize for a session? I Know how to set arraysize on createengine and it works for all connections in the pool

My code

class OracleConnection:
    def __init__(self, *, db_url, pool_size, max_overflow, pool_timeout, pool_recycle):
        self.engine = create_async_engine(
            db_url,
            pool_pre_ping=True,
            echo=True,
            pool_size=pool_size,
            max_overflow=max_overflow,
            pool_timeout=pool_timeout,
            pool_recycle=pool_recycle,
            # arraysize=5000 - default arraysize for all cursors in pull
        )

        self.session_factory = async_sessionmaker(
            class_=ASession,
            autocommit=False,
            autoflush=False,
            bind=self.engine,
            expire_on_commit=False,
        )


    async def get_session(self) -> typing.Generator[ASession, None, None]:
        """
        Returns an AsyncSession from connection pool
        """
        session = self.session_factory()
        try:
            yield session
        except Exception as ex:  # noqa
            await session.rollback()
            logger.critical("Database session rollback at exception:")
            traceback.print_exc()
            raise ex
        finally:
            await session.commit()
            await session.close()
            logger.debug("Database session closed")

Well, I need to change arraysize for large selections

@dummy_router.get("/dummy_db_gen")
async def dummy_db_gen(
    *, chunk_size: int = 1000, session: AsyncSession = Depends(suag_connection.get_session)
) -> int:
    # Here I want to set arraysize
    res = await session.execute(
        text("select id_art, name, id_group from suag.v_article where rownum <= 100000")
    )
    iteration = 1
    for chunk in chunks.yield_per(chunk_size):
        iteration += 1
    return iteration

Answer 1

Executing the SQL command through a cursor is a way that allows you to change arraysize .

The dummy_gen_function can be modified

conn = await session.connection()

cursor = conn.connection.cursor()
cursor.arraysize = 1000
 
result = cursor.execute("xxxxxx")