I have thousands of records to insert to the tables. So want to try the bulkinsert kind of featuare.

[patelronakp]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

Here is the example of sqlalchamy they are providing the bulk_insert_mappings feature which helps to insert bulk records. So is there any option to do this with the SQLModel.

for item in data_bulk.data:
                metadata_entry = {
                    "companyID": user.companyID,
                    "userId": user_id,
                    "filename": item.filename,
                    "CreatedDate": item.CreatedDate,
                }
                metadata_entries.append(metadata_entry)

            db.bulk_insert_mappings(MetaData, metadata_entries)
            db.commit()

Description

Like sqlalchamy has bulk_insert_mappings option which i didn't find in the SQLModel's AsyncSession object. So want to know how to do bulk inserting using AsyncSession of SQLModel.

I tried with the same bulk insert with add_all but it will take lots of time to insert 70K records to the table. But then I created a sample project with sqlalchamy bulk_insert_mapping. It will insert the same data within a few seconds only.

Operating System

Windows

Operating System Details

No response

SQLModel Version

sqlmodel Version: 0.0.8

Python Version

Python 3.10.10

Additional Context

If anyone has any kind of solution to bulk insert efficiently then please share your suggestions.

[gazpachoking]

You should be able to use Session.bulk_insert_mappings. Just pass your model as the 'mapper'.

An example using the tutorial models:

session.bulk_insert_mappings(Hero, [{"name": "Deadpond", "secret_name": "Dive Wilson"}])

[ahsar]

session.commit()

[YuriiMotov]

The syntax is:

    session.exec(insert(MetaData), params=metadata_entries)
    session.commit()

Runnable code example:

from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, insert


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str


engine = create_engine("sqlite:///", echo=True)


SQLModel.metadata.create_all(engine)

with Session(engine) as session:
    heroes_data = [
        {
            "name": f"Hero {i}",
        }
        for i in range(10)
    ]

    session.exec(insert(Hero), params=heroes_data)
    session.commit()

SQL

BEGIN (implicit)
INSERT INTO hero (name) VALUES (?)
[('Hero 0',), ('Hero 1',), ('Hero 2',), ('Hero 3',), ('Hero 4',), ('Hero 5',), ('Hero 6',), ('Hero 7',), ('Hero 8',), ('Hero 9',)]
COMMIT