How to insert multiple values with subquery using SQLAlchemy Core?

Question

Using SQLAlchemy Core (not ORM), I'm trying to INSERT multiple rows using subqueries in the values. For MySQL, the actual SQL would look something like this:

INSERT INTO widgets (name, type) VALUES
('Melon', (SELECT type FROM widgetTypes WHERE type='Squidgy')),
('Durian', (SELECT type FROM widgetTypes WHERE type='Spiky'))

But I only seem to be able to use subqueries when using the values() method on an insert() clause which only allows me to do one insert at a time. I'd like to insert multiple values at once by passing them all to the Connection's execute() method as a list of bind parameters, but this doesn't seem to be supported.

Is it possible to do what I want in a single call to execute()?

Here's a self contained demonstration. Note this uses the sqlite engine which doesn't support multiple inserts in the same way as MySQL, but the SQLAlchemy code still fails in the same way as the real MySQL app.

from sqlalchemy import *

if __name__ == "__main__":
    # Construct database
    metadata = MetaData()
    widgetTypes = Table('widgetTypes', metadata,
        Column('id', INTEGER(), primary_key=True),
        Column('type', VARCHAR(), nullable=False),
    )
    widgets = Table('widgets', metadata,
        Column('id', INTEGER(), primary_key=True),
        Column('name', VARCHAR(), nullable=False),
        Column('type', INTEGER(), nullable=False),
        ForeignKeyConstraint(['type'], ['widgetTypes.id']),
    )
    engine = create_engine("sqlite://")
    metadata.create_all(engine)

    # Connect and populate db for testing
    conn = engine.connect()
    conn.execute(widgetTypes.insert(), [
        {'type': 'Spiky'},
        {'type': 'Squidgy'},
    ])

    # Some select queries for later use.
    select_squidgy_id = select([widgetTypes.c.id]).where(
        widgetTypes.c['type']=='Squidgy'
    ).limit(1)
    select_spiky_id = select([widgetTypes.c.id]).where(
        widgetTypes.c['type']=='Squidgy'
    ).limit(1)

    # One at a time works via values()
    conn.execute(widgets.insert().values(
        {'name': 'Tomato', 'type': select_squidgy_id},
    ))

    # And multiple values work if we avoid subqueries
    conn.execute(
        widgets.insert(),
        {'name': 'Melon',  'type': 2},
        {'name': 'Durian', 'type': 1},
    )

    # Check above inserts did actually work
    print conn.execute(widgets.select()).fetchall()

    # But attempting to insert many at once with subqueries does not work.
    conn.execute(
        widgets.insert(),
        {'name': 'Raspberry', 'type': select_squidgy_id},
        {'name': 'Lychee',    'type': select_spiky_id},
    )

Run it and it dies on the last execute() call with:

sqlalchemy.exc.InterfaceError: (InterfaceError) Error binding parameter 1 - probably unsupported type. u'INSERT INTO widgets (name, type) VALUES (?, ?)' (('Raspberry', <sqlalchemy.sql.expression.Select at 0x19f14d0; Select object>), ('Lychee', <sqlalchemy.sql.expression.Select at 0x19f1a50; Select object>))

Answer 1

Instead of providing subselect statement as parameter value, you have to embed it into INSERT statement:

type_select = select([widgetTypes.c.id]).where(
        widgetTypes.c.type==bindparam('type_name'))

insert = widgets.insert({'type': type_select})

conn.execute(insert, [
    {'name': 'Melon',  'type_name': 'Squidgy'},
    {'name': 'Lychee', 'type_name': 'Spiky'},
])