Sqlalchemy oracle connections

benchy_oracledb.py

# Sqlalchemy Async
#  Records: 164
#  Time: 15.389007091522217
#  Average: 0.30778014183044433
# -
# Sqlalchemy Sync
#  Records: 164
#  Time: 15.195691585540771
#  Average: 0.30391383171081543
# -
# Oracledb Sync
#  Records: 164
#  Time: 6.3568267822265625
#  Average: 0.12713653564453126
# -
# Oracledb Async
#  Records: 164
#  Time: 4.254000425338745
#  Average: 0.0850800085067749

import asyncio
import dataclasses
import time
import oracledb

from sqlalchemy import text, URL
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
from sqlalchemy import create_engine


@dataclasses.dataclass
class DatabaseConfig:
    driver: str | None = None
    username: str | None = None
    password: str | None = None
    host: str | None = None
    port: int | None = None
    database: str | None = None
    schema: str | None = None


@dataclasses.dataclass
class Result():
    label: str | None = None
    times: list[float] = dataclasses.field(default_factory=list)
    records: int = 0

    def average(self) -> float:
        return sum(self.times) / len(self.times)

    def total(self) -> float:
        return sum(self.times)



async def main():
    settings = DatabaseConfig(
        driver="oracle+oracledb",
        username="xxx",
        password="xxx",
        host="xxx",
        schema="xxx",
        port=1521,
        database="xxx"
    )
    query = "SELECT * FROM TABLE" # 164 records, 
    loops = 50

    results: list[Result] = list()

    # ----------------------------------------------------------------------
    # Sqlalchemy Async
    # ----------------------------------------------------------------------
    result = Result(label="Sqlalchemy Async")
    engine = create_async_engine(
        URL.create(
            drivername=settings.driver,
            username=settings.username,
            password=settings.password,
            host=settings.host,
            port=settings.port,
            database=settings.database,
        )
    )
    async with engine.connect() as session:
        x = await session.execute(text(query))
        x.fetchall()

        for i in range(0,loops):
            start = time.time()
            records = await session.execute(text(query))
            r = 0
            for record in records.all():
                r = r + 1
                pass
            result.records = r
            result.times.append(time.time() - start)
    print(result)
    results.append(result)



    # ----------------------------------------------------------------------
    # sqlalchemy sync
    # ----------------------------------------------------------------------

    result = Result(label="Sqlalchemy Sync")
    engine = create_engine(URL.create(
        drivername=settings.driver,
        username=settings.username,
        password=settings.password,
        host=settings.host,
        port=settings.port,
        database=settings.database,
    ))
    with engine.connect() as session:
        x = session.execute(text(query))
        x.fetchall()

        for i in range(0, loops):
            start = time.time()
            records = session.execute(text(query))
            r = 0
            for record in records.all():
                r = r + 1
                pass
            result.records = r
            result.times.append(time.time() - start)


    print(result)
    results.append(result)

    # ----------------------------------------------------------------------
    # oracledb sync
    # ----------------------------------------------------------------------
    result = Result(label="Oracledb Sync")

    for i in range(0, loops):
        connection = oracledb.connect(
            user=settings.username,
            password=settings.password,
            host=settings.host,
            port=settings.port,
            service_name=settings.database
        )
        with connection:
            with connection.cursor() as cursor:
                start = time.time()
                records = cursor.execute(query)
                r = 0
                for record in records:
                    r = r + 1
                    pass
                result.records = r
                result.times.append(time.time() - start)
    print(result)
    results.append(result)

    # ----------------------------------------------------------------------
    # oracledb async
    # ----------------------------------------------------------------------
    result = Result(label="Oracledb Async")

    for i in range(0, loops):
        connection = await oracledb.connect_async(
            user=settings.username,
            password=settings.password,
            host=settings.host,
            port=settings.port,
            service_name=settings.database
        )
        async with connection:
            await connection.execute(query)
            async with connection.cursor() as cursor:

                start = time.time()
                await cursor.execute(query)
                r = 0
                async for record in cursor:
                    r = r + 1
                result.records = r
                result.times.append(time.time() - start)
    print(result)
    results.append(result)



    print("-"*80)
    for result in results:
        print(result.label)
        print("  Records:",result.records)
        print("  Time:",result.total())
        print("  Average:",result.average())
        print("-")

if __name__ == "__main__":
    asyncio.run(main())
    

benchy_oracledb_vs_cx_oracle.py

# Sqlalchemy oracledb Sync 
#  Records: 164
#  Time: 14.694408893585205
#  Average: 0.2938881778717041
# -
# Sqlalchemy cx_oracle sync
#  Records: 164
#  Time: 17.163243293762207
#  Average: 0.34326486587524413
# -
# cx_oracle Sync
#  Records: 164
#  Time: 7.658784627914429
#  Average: 0.15317569255828858
# -
# Oracledb Sync
#  Records: 164
#  Time: 4.6097352504730225
#  Average: 0.09219470500946045


import asyncio
import dataclasses
import time
import oracledb
import cx_Oracle

from sqlalchemy import text, URL
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
from sqlalchemy import create_engine


@dataclasses.dataclass
class DatabaseConfig:
    driver: str | None = None
    username: str | None = None
    password: str | None = None
    host: str | None = None
    port: int | None = None
    database: str | None = None
    schema: str | None = None


@dataclasses.dataclass
class Result():
    label: str | None = None
    times: list[float] = dataclasses.field(default_factory=list)
    records: int = 0

    def average(self) -> float:
        return sum(self.times) / len(self.times)

    def total(self) -> float:
        return sum(self.times)



async def main():
    settings = DatabaseConfig(
        driver="oracle+oracledb",
        username="xxx",
        password="xxx",
        host="xxx",
        schema="xxx",
        port=1521,
        database="xxx"
    )
    query = "SELECT * FROM Table"
    loops = 50

    results: list[Result] = list()

    # ----------------------------------------------------------------------
    # sqlalchemy sync
    # ----------------------------------------------------------------------

    result = Result(label="Sqlalchemy oracledb Sync ")
    engine = create_engine(
        URL.create(
            drivername=settings.driver,
            username=settings.username,
            password=settings.password,
            host=settings.host,
            port=settings.port,
            database=settings.database,
        )
    )
    with engine.connect() as session:
        x = session.execute(text(query))
        x.fetchall()

        for i in range(0, loops):
            start = time.time()
            records = session.execute(text(query))
            r = 0
            for record in records.all():
                r = r + 1
                pass
            result.records = r
            result.times.append(time.time() - start)

    print(result)
    results.append(result)

    # ----------------------------------------------------------------------
    # sqlalchemy sync
    # ----------------------------------------------------------------------

    result = Result(label="Sqlalchemy cx_oracle sync")

    engine = create_engine(
        URL.create(
            drivername="oracle+cx_oracle",
            username=settings.username,
            password=settings.password,
            host=settings.host,
            port=settings.port,
            database=settings.database,
        )
    )
    with engine.connect() as session:
        for i in range(0, loops):
            start = time.time()
            records = session.execute(text(query))
            r = 0
            for record in records.all():
                r = r + 1
                pass
            result.records = r
            result.times.append(time.time() - start)

    print(result)
    results.append(result)


    # ----------------------------------------------------------------------
    # cx_oracle sync
    # ----------------------------------------------------------------------
    result = Result(label="cx_oracle Sync")
    for i in range(0, loops):


        connection = cx_Oracle.connect(
            user=settings.username,
            password=settings.password,
            dsn=f"{settings.host}:{settings.port}/{settings.database}"
        )
        with connection:
            with connection.cursor() as cursor:
                start = time.time()
                records = cursor.execute(query)
                r = 0
                for record in records:
                    r = r + 1
                    pass
                result.records = r
                result.times.append(time.time() - start)
    print(result)
    results.append(result)

    # ----------------------------------------------------------------------
    # oracledb sync
    # ----------------------------------------------------------------------
    result = Result(label="Oracledb Sync")
    for i in range(0, loops):
        connection = oracledb.connect(
            user=settings.username,
            password=settings.password,
            host=settings.host,
            port=settings.port,
            service_name=settings.database
        )
        with connection:
            with connection.cursor() as cursor:
                cursor.execute(query)

                start = time.time()
                records = cursor.execute(query)
                r = 0
                for record in records:
                    r = r + 1
                    pass
                result.records = r
                result.times.append(time.time() - start)
    print(result)
    results.append(result)




    print("-"*80)
    for result in results:
        print(result.label)
        print("  Records:",result.records)
        print("  Time:",result.total())
        print("  Average:",result.average())
        print("-")

if __name__ == "__main__":
    asyncio.run(main())