如何使用SQLAlchemy Core通过子查询插入多个值？

Question

我在使用 SQLAlchemy Core（不是 ORM）时，想通过子查询来一次性插入多行数据。在 MySQL 中，实际的 SQL 语句大概是这样的：

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

但是我发现，只有在使用 values() 方法时，才能用到子查询，而这个方法只能一次插入一行数据。我希望能通过将多个值作为绑定参数的列表，传递给 Connection 的 execute() 方法，来一次性插入多行，但这似乎不被支持。

我想知道，是否可以在一次调用 execute() 的情况下实现我想要的功能？

这里有一个完整的示例。请注意，这个示例使用的是 sqlite 引擎，而 sqlite 的多行插入支持方式与 MySQL 不同，但 SQLAlchemy 的代码在实际运行时仍然会遇到和 MySQL 应用相同的问题。

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

运行这个示例时，最后一次调用 execute() 会出错，错误信息是：

sqlalchemy.exc.InterfaceError: (InterfaceError) 绑定参数 1 时出错 - 可能是不支持的类型。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>))

database mysql sqlalchemy sqlite subquery insert core multiple rows

如何使用SQLAlchemy Core通过子查询插入多个值？

1 个回答

撰写回答