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>))

I have a gut feeling that you're doing it wrong - currently you want to execute a subquery for every single record. And because you are using SQLAlchemy Core, it means that all the SQL will be executed exactly as you supply it. — plaes, Nov 05 '11 at 00:12
@plaes yep that's exactly what I want to do (execute a subquery for every single record), you'll just have to trust me that it makes more sense in the real application `:)`. But the problem is that SQLAlchemy **isn't** executing exactly what I supply, and is refusing to process the `Select` expression `:(` — Day, Nov 05 '11 at 02:00

score 5 · Accepted Answer · answered Nov 07 '11 at 09:25

5

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'},
])

answered Nov 07 '11 at 09:25

Denis Otkidach

28,521
8
72
93

Fantastic that works a treat thanks. Saved me from having to make multiple round trips to the database - I can now do everything in a single query which saves a **lot** of time when your database is on the other side of the world. – Day Nov 07 '11 at 14:48
3

What if `type_select` has two fields? – kawing-chiu Jun 06 '16 at 09:18

How to insert multiple values with subquery using SQLAlchemy Core?

1 Answers1