Does piccolo-orm automatically use transactions when inserting multiple records? Performing multiple inserts within a single transaction improves performance.

[DKSCIcat]

[sinisaos]

@DKSCIcat No, but you can easily use a transaction for bulk inserts. Something like this.

# using engine instance
async with DB.transaction():
    await Band.insert(
        Band(name="Pythonistas"),
        Band(name="Darts"),
        Band(name="Gophers"),
    )

or

# using thru table meta
async with Band._meta.db.transaction():
    await Band.insert(
        Band(name="Pythonistas"),
        Band(name="Darts"),
        Band(name="Gophers"),
    )

[sinisaos]

@DKSCIcat Sorry, I have to correct myself because I was wrong. For bulk inserts, Piccolo uses a transaction by default because in Postgres or SQLite each statement is executed in a transaction (and a bulk insert is a single statement), so we don't need to explicitly declare a transaction. We only need an explicit DB.transaction() when we have multiple different insert statements (or more than one different read/write statements). Here's a little test

import asyncio
import time
from piccolo.columns import Varchar, Serial
from piccolo.engine.postgres import PostgresEngine
from piccolo.table import Table

DB = PostgresEngine(
    config={
        "host": "localhost",
        "database": "piccolo_project",
        "user": "postgres",
        "password": "postgres",
        "port": 5432,
    }
)


class Manager(Table, db=DB):
    id: Serial
    name = Varchar(length=100)


async def main():
    await Manager.create_table(if_not_exists=True)
    await DB.start_connection_pool(max_size=10)

    # insert bulk
    start = time.perf_counter()
    await Manager.insert(*[Manager(name=f"Manager {i}") for i in range(1, 1000)])
    end = time.perf_counter()
    total = end - start
    print(f"Insert bulk: {total:.4f} seconds")

    # insert bulk (using transaction)
    start = time.perf_counter()
    async with DB.transaction():
        await Manager.insert(*[Manager(name=f"Manager {i}") for i in range(1, 1000)])
    end = time.perf_counter()
    total = end - start
    print(f"Insert bulk (using transaction): {total:.4f} seconds")
    await DB.close_connection_pool()

    # drop table for clean slate
    await Manager.alter().drop_table(if_exists=True)


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

Result is (almost identical)

Insert bulk: 0.0283 seconds
Insert bulk (using transaction): 0.0262 seconds

Once again, I deeply apologize for my mistake.