Plain SQL vs ORM performances

dbtest.py

# Inserting 50000000 rows (commit every 0 inserts):
# Plain query elapsed in: 0:03:44.024872
# PyPika query elapsed in: 0:37:52.236037
# SQLAlchemy ORM Exec query elapsed in: 1:45:11.338434
# SQLAlchemy ORM Object query elapsed in: 3:12:56.100726

# Inserting 50000000 rows (commit every 1000 inserts):
# Plain query elapsed in: 0:17:11.325788
# PyPika query elapsed in: 0:53:51.816650
# SQLAlchemy ORM Exec query elapsed in: 2:33:40.255574
# SQLAlchemy ORM Object query elapsed in: 4:21:54.751410

import os
import sqlite3.dbapi2 as sqlite
from contextlib import closing
from datetime import datetime

import sqlalchemy
from pypika import Query, Column, Table, Parameter
from sqlalchemy import text, create_engine, event
from sqlalchemy.engine import Engine
from sqlalchemy.orm import Session


@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA journal_mode=WAL")
    cursor.close()


def createplaindb():
    create = "CREATE TABLE test_plain(uno integer primary key , due text)"
    db = sqlite.connect("test_plain.db")
    set_sqlite_pragma(db, None)

    with closing(db.cursor()) as cur:
        cur.execute(create)

    return db


def plaindb(db, iterations, commit):
    insert = "INSERT INTO test_plain VALUES(?, ?)"

    with closing(db.cursor()) as cur:
        for i in range(iterations):
            cur.execute(insert, (i, f"str {i}"))
            if commit != 0 and i % commit == 0:
                db.commit()
    db.commit()


def createpika():
    table = Table("test_pypika")
    create = Query.create_table(table).columns(
        Column("uno", "INTEGER", nullable=False),
        Column("due", "TEXT")
    ).primary_key("uno")

    db = sqlite.connect("test_pypika.db")
    set_sqlite_pragma(db, None)
    with closing(db.cursor()) as cur:
        cur.execute(create.get_sql())

    return db


def withpika(db, iterations, commit):
    table = Table("test_pypika")
    insert = Query.into(table).columns("uno", "due").insert(Parameter("?"), Parameter("?"))

    with closing(db.cursor()) as cur:
        for i in range(iterations):
            cur.execute(insert.get_sql(), (i, f"str {i}"))
            if commit != 0 and i % commit == 0:
                db.commit()
    db.commit()


def createormexec():
    create = "CREATE TABLE test_ormexec(uno integer primary key , due text)"
    engine = create_engine("sqlite+pysqlite:///test_ormexec.db")
    engine.execute(text(create))

    return engine


def withormexec(engine, iterations, commit):
    insert = "INSERT INTO test_ormexec VALUES(:id, :val)"

    with Session(engine) as db:
        for i in range(iterations):
            db.execute(text(insert), [{"id": i, "val": f"str {i}"}])
            if commit != 0 and i % commit == 0:
                db.commit()
        db.commit()


def createormobj():
    engine = create_engine("sqlite+pysqlite:///test_ormobj.db")
    metadata = sqlalchemy.MetaData()

    table = sqlalchemy.Table("test_ormobj", metadata, sqlalchemy.Column("uno", sqlalchemy.Integer, primary_key=True),
                             sqlalchemy.Column("due", sqlalchemy.String))

    metadata.create_all(engine)

    return engine, table


def withormobj(engine, table, iterations, commit):
    with Session(engine) as db:
        for i in range(iterations):
            db.execute(table.insert().values(uno=i, due=f"str {i}"))
            if commit != 0 and i % commit == 0:
                db.commit()
        db.commit()


def main(rows, commit):
    print(f"Inserting {rows} rows (commit every {commit} inserts)")

    with createplaindb() as db:
        start = datetime.now()
        plaindb(db, rows, commit)
        stop = datetime.now()
        print("Plain query elapsed in:", stop - start)

    with createpika() as db:
        start = datetime.now()
        withpika(db, rows, commit)
        stop = datetime.now()
        print("PyPika query elapsed in:", stop - start)

    engine = createormexec()
    start = datetime.now()
    withormexec(engine, rows, commit)
    stop = datetime.now()
    print("SQLAlchemy ORM Exec query elapsed in:", stop - start)

    engine, table = createormobj()
    start = datetime.now()
    withormobj(engine, table, rows, commit)
    stop = datetime.now()
    print("SQLAlchemy ORM Object query elapsed in:", stop - start)


if __name__ == "__main__":
    for item in ["test_ormobj.db", "test_ormexec.db", "test_plain.db", "test_pypika.db"]:
        if os.path.exists(item):
            os.remove(item)
    main(rows=1, commit=0)