Gino: Bulk insert

[ape364]

• GINO version: 0.7.5
• Python version: 3.7.0
• asyncpg version: 0.17.0
• aiocontextvars version: -
• PostgreSQL version: 10.5

Description

How can I Insert many rows? Can u show some example? Thanks.

[wwwjfy]

Do you mean to insert multiple rows in one statement?
This may be what you want. Check tests/test_executemany.py

result = await User.insert().gino.all(dict(nickname='1'), dict(nickname='2'))

[wwwjfy]

I'll close because of inactivity. Feel free to reopen. :)

[Arthur264]

@wwwjfy
Have I can get new rows id after create.
result = await User.insert().gino.all(dict(nickname='1'), dict(nickname='2'))

result is None
True

[Pentusha]

@Arthur264, maybe you are looking for bulk upsert.

from datetime import datetime

from sqlalchemy import func
from sqlalchemy.dialects.postgresql import insert, UUID


class User(db.Model):
    __tablename__ = 'user'

    id = db.Column(UUID, primary_key=True, server_default=func.uuid.generate())
    nickname = db.Column(db.Unicode, unique=True)
    updated_at = db.Column(db.DateTime)

    @classmethod
    def bulk_upsert(cls, users):
        qs = (
            insert(cls.__table__)
            .values([
                {'nickname': user['nickname']}
                for user in users
            ])
        )
        return (
            qs
            .on_conflict_do_update(
                index_elements=[cls.nickname],
                set_={'updated_at': datetime.utcnow()},  # or even qs.excluded['some_column']
            )
            .returning(User.__table__)
            .gino
            .all()
        )

async def some_handler():
    inserted1 = await User.bulk_upsert([{'nickname': '1'}, {'nickname': '2'}])
    inserted2 = await User.bulk_upsert([{'nickname': '2'}, {'nickname': '3'}])

[wwwjfy]

@Arthur264 Please take a look at @Pentusha's example.
The key difference here is whether it's a multiple statements.
For multiple statements, that the INSERT INTO will be executed multiple times, it won't return any results;
another way is to insert multiple rows in one statement (the statement is like INSERT INTO users VALUES ("name1"), ("name2")), by which it can return the result using returning()

[Arthur264]

@wwwjfy @Pentusha
I found this asynchronous way to solve the problem.
await insert(model.__table__).values(items).on_conflict_do_nothing().gino.scalar()