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).
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/e408bc7b-efcd-407b-88f0-216fc9de1fc9%40app.fastmail.com.
