Support MultiRow Inserts

As far as I can tell, SQLAlchemy Core does not render multirow insert statements such as the following, even when the underlying database supports multirow inserts:

INSERT INTO mytable (col1, col2, col3) VALUES
    (1, 2, "abc"),
    (2, 4, "xyz"),
    (3, 5, "aaa"),
    (4, 7, "bbb");

Postgres has had multiple row inserts since 8.2 and sqlite since 3.7.11.

from sqlalchemy import Column, Integer, MetaData, Table, Unicode, create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

engine = create_engine('sqlite://')

session = scoped_session(sessionmaker(bind=engine))

metadata = MetaData()

table = Table(
    'table', metadata,
    Column('id', Integer, primary_key=True),
    Column('value', Unicode),
)

table.create(bind=engine)

items = [(1, 'a'), (2, 'b'), (3, 'c')]
values = [{'id': a, 'value': b} for a, b in items]

# Ideally this would render a single INSERT statement, rather than three
session.execute(table.insert(), values)

Comments (2)