Postgresq Execute Many with Textual SQL Convenience Issue

[mkmo...@gmail.com]

Hi Mike,

When using Core, we can do a bulk insert and bulk return with Postgresql trivially:

    from sqlalchemy import table, column

    t = table('foo', column('id'), column('bar')

    ins = t.insert().values([{'bar': 'a'}, {'bar': 'b'}]).returning(foo.id)

    results = conn.execute(ins)

   

    ids = results.fetchall()

However, with raw textual SQL, it is a bit more inconvenient.

The following doesn't work:

    results = conn.execute(text(

        'insert into foo values (:bar) returning id

    ), [{'bar': 'a'}, {'bar': 'b'}])

   

    # raises sqlalchemy.exc.ResourceClosedError: This result object does not return rows. It has been closed automatically.

    results.fetchall()

To get it working, we have to do it this way:

    results = conn.execute(text(

        'insert into foo values (:bar0), (:bar1)

    ), {'bar0': 'x', 'bar1': 'y'})

    assert results.fetchall()

This isn't convenient. For example you would have to convert a list of bars like [{'bar': 'a'}, {'bar': 'b'}] into a single dict with uniquely name keys {'bar0': 'a', 'bar1': 'b'}.

I imagine sqlalchemy is doing that under the hood when using core. Is there some convenience function available in sqlalchemy core that I can use to simplify this?

[Mike Bayer]

On Thu, Nov 2, 2023, at 11:24 AM, mkmo...@gmail.com wrote:

Hi Mike,

When using Core, we can do a bulk insert and bulk return with Postgresql trivially:

    from sqlalchemy import table, column

    t = table('foo', column('id'), column('bar')

    ins = t.insert().values([{'bar': 'a'}, {'bar': 'b'}]).returning(foo.id)

    results = conn.execute(ins)

   

    ids = results.fetchall()

However, with raw textual SQL, it is a bit more inconvenient.

The following doesn't work:

    results = conn.execute(text(

        'insert into foo values (:bar) returning id

    ), [{'bar': 'a'}, {'bar': 'b'}])

   

    # raises sqlalchemy.exc.ResourceClosedError: This result object does not return rows. It has been closed automatically.

    results.fetchall()

That's because you're using excecutemany.   pep-249 executemany does not support rows being returned, so if you send a list of params like that, it's an automatic result close.  there's nothing to fetch.

I would also note that the statement you have above might not be as useful as you think since I would assume you would want those "RETURNING" rows to line up with the dictionaries you are sending.   This is not a guarantee for most backends and the SQL Server backend will actually actively rearrange the rows.   Special (extremely inconvenient) forms must be taken to ensure this ordering.

SQLAlchemy 2.0 supports a new form of INSERT called insertmanyvalues (https://docs.sqlalchemy.org/en/20/core/connections.html#insert-many-values-behavior-for-insert-statements ) which can receive a list of parameter dictionaries along with an insert() construct and convert the operation into series of batched single statements that are yielded as a single result set.   That is, it does **not** use DBAPI executemany (except on Oracle which has special support), it uses DBAPI execute.   It also does this very special and inconvenient formatting of the INSERT statement to ensure to the greatest degree possible that RETURNING rows are ordered the same way as the parameter sets.

To get it working, we have to do it this way:

    results = conn.execute(text(

        'insert into foo values (:bar0), (:bar1)

    ), {'bar0': 'x', 'bar1': 'y'})

    assert results.fetchall()

This isn't convenient. For example you would have to convert a list of bars like [{'bar': 'a'}, {'bar': 'b'}] into a single dict with uniquely name keys {'bar0': 'a', 'bar1': 'b'}.

you do, because there's no result set implied from an executemany, you have to use an execute.  

if you want SQLAlchemy to convert your multiple parameters into a series of "INSERT..VALUES", do the execution of them, and then with RETURNING work the results back together, that's exactly what insertmanyvalues does.    We spent many months developing this feature plus figuring out the RETURNING / ordering thing which took an extra month, so you'd be tapping into a great deal of development efforts by using that feature.

However, that feature works only with insert() constructs.     With text(), we have no idea what your statement says and we don't parse SQL.  you'd be on your own there.

I imagine sqlalchemy is doing that under the hood when using core. Is there some convenience function available in sqlalchemy core that I can use to simplify this?

The functions which do this start at https://github.com/sqlalchemy/sqlalchemy/blob/b51cccec6a953555f39c16005cb5a2a49a6f4b21/lib/sqlalchemy/engine/default.py#L758  and then digs into compiler at https://github.com/sqlalchemy/sqlalchemy/blob/b51cccec6a953555f39c16005cb5a2a49a6f4b21/lib/sqlalchemy/sql/compiler.py#L5306 , where you'll note these functions are now huge and complicated, as they must accommodate all cases amongst a wide variety of statements scenarios, typing issues on both the input and output side, and backends.

You can definitely write simple helpers to convert simple INSERT statements on your own here, or maybe look at psycopg2's fast execution helpers also at https://www.psycopg.org/docs/extras.html#fast-execution-helpers which are older / simpler versions of this kind of thing (however still probably not generalizable).