Bulk inserts for postgres

pg_insert.py

#!/usr/bin/env python3

from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.dialects.postgresql import insert as pg_insert
from sqlalchemy.ext.declarative import declarative_base

from os import environ

# In docker-compose, DATABASE_URL will point to postgres
url = environ.get('DATABASE_URL', 'sqlite:///:memory:')
engine = create_engine(url, echo=True, executemany_mode='values_only')

Base = declarative_base()
metadata = Base.metadata

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String)

metadata.drop_all(engine)
metadata.create_all(engine)

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()

results = session.execute(pg_insert(User).values([{'name': 'Alice'}, {'name': 'Bob'}]).returning(User.id))
print(list(results))
results = session.execute(pg_insert(User).returning(User.id), [{'name': 'Carol'}, {'name': 'Dan'}])
print(list(results))
results = session.execute(User.__table__.insert().returning(User.id).values([{'name': 'Eve'}, {'name': 'Frank'}]))
print(list(results))
results = session.execute(User.__table__.insert().returning(User.id), [{'name': 'Grace'}, {'name': 'Heidi'}])
print(list(results))
session.rollback()