SQLAlchemy batch insert with database functions

Question

Is it possible to use database functions when inserting multiple values into a table using SQLAlchemy?

If I insert a single row, the following code works fine:

conn.execute(
    example_table.insert().values(
        id=17,
        example_field=db.func.concat("foo", "bar")
    )
)

The field example_field contains the string foobar. However, trying to insert multiple rows this way produces unexpected results:

conn.execute(example_table.insert(), [{
    "id": 17,
    "example_field": db.func.concat("foo", "bar")
}, {
    "id": 18,
    "example_field": db.func.concat("bar", "baz")
}])

The field example_field is filled with the string concat(:concat_1, :concat_2).

What is the proper way to insert multiple rows into a table with functions?

Ilja Everilä Ilja Everilä · Accepted Answer · 2018-03-14T22:16:55

This is covered in "Inserts, Updates and Deletes":

However, if we wish to use explicitly targeted named parameters with composed expressions, we need to use the bindparam() construct.

Create the insert statement with the function call, but with bindparam() constructs as arguments:

stmt = example_table.insert().\
    values(example_field=func.concat(bindparam('a'), bindparam('b')))

conn.execute(stmt, [
    {"id": 17, "a": "foo", "b": "bar"},
    {"id": 18, "a": "bar", "b": "baz"}])

The concat(:concat_1, :concat_2) strings are (probably) the result of your DB-API driver converting the function expression objects to strings implicitly:

In [5]: str(func.concat("foo", "bar"))
Out[5]: 'concat(:concat_1, :concat_2)'

As a comparison psycopg2 errors out, because it doesn't know what to do with them.

SQLAlchemy batch insert with database functions

1 Answers