Differences between TableClause insert and Model.__table__ inserts?

[Kata Char]

Hi, sorry if this post is a duplicate, my first one didn't seem to make it.

I was reading the documentation:

- https://docs.sqlalchemy.org/en/13/core/tutorial.html#execute-multiple

- https://docs.sqlalchemy.org/en/13/_modules/examples/performance/bulk_inserts.html

Is there any difference between conn.execute(TableClause.insert(), [...]) vs conn.execute(Model.__table__.insert(), [...])?

The first one is documented to use execumany(), but what about the second one? 

[Mike Bayer]

Any conn.execute() that passes a list of dictionaries as the second argument, where there is more than one entry in the list, will use the executemany() style with the DBAPI connection.

With the ORM the Model.__table__ attribute is a Table object.  That tutorial seems to be referencing TableClause which is the base class for Table, but all the examples there are using Table objects.

--

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/1ffe48c6-4124-40ab-902f-ffa86885ea94n%40googlegroups.com.

[Kata Char]

I see, does that mean there is no difference in performance if one or the other is used? In other words

    from sqlalchemy.sql import table

    _foo = table(...)

    conn.execute(_foo.insert(), [{...}, ...])

Would have the same performance as `conn.execute(Foo.__table__.insert(), [{...}, ...])`

[Jonathan Vanasco]

This was not clear enough in Mike's post: `Foo.__table__` is the same type of object as `_foo = table(...)`.  SQLAlchemy ORM is built on top of SQLAlchemy's Core, so the  ORM's `.__table__` attribute is the Core's `table()` object.

Since they're the same, the two will have the same performance within `conn.execute(`.

[Mike Bayer]

technically Table and TableClause are a little bit different but I don't think there's any behavioral difference at the level of execute(obj.insert()).   what matters more is if the Column objects have datatypes or defaults that incur some kind of Python-side processing or not.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/89002280-96e7-45e9-a11a-f104d8e2aa3fn%40googlegroups.com.

[Mike Bayer]

On Thu, Nov 26, 2020, at 5:05 PM, Kata Char wrote:

Is the documentation up-to-date?

yup

I printed out a query and there were two insert statements, but the documentation shows one - am I doing something wrong?

I see in the postgresql logs two insert statements

LOG:  statement: INSERT INTO foo (test) VALUES ('foo1')

LOG:  statement: INSERT INTO foo (test) VALUES ('foo2')

that's what PostgreSQL drivers do by default when you use executemany().   See https://www.psycopg.org/docs/cursor.html#cursor.executemany  for a description of how this works.   the psycopg2 driver also binds values into the SQL statement directly when it passes to the DB so that's why you see parameters rendered.   The "loop" that it runs is *slightly* faster than doing it in pure Python as psycopg2 is written in C, however, it's widely known that psycopg2's executemany() implementation is very slow. 

That's why as you'll note in the docs for it, it recommends the use of "fast execution helpers", which can be used to pass the INSERT statement(s) in two different ways, either as many INSERT statements together with a semicolon or as a single INSERT with VALUES.  You're looking for the latter.    SQLAlchemy supports these modes as documented at https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#psycopg2-executemany-mode, which in version 1.3 have to be opted-into using create_engine options.  However, in version 1.4, a better set of modes are added and the default behavior now defaults to the fastest form for INSERT.. VALUES as documented at https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#psycopg2-executemany-mode.

Short answer, use 1.4.0b1 or set the appropriate flags with 1.3 with psycopg2.   The two other drivers we support, pg8000 and asyncpg, both use prepared statements, so the entire mechanism by which these parameters are passed is different and they should perform better than default psycopg2 at the level of how the statement is passed to the database (although pg8000 overall performs more slowly than both because it's not written in C).

This happens for other variants like table(...), bulk_insert_mappings, etc. According to the documentation, it should be something like `INSERT INTO foo (test) VALUES (?) (('foo1'), ('foo2'))`

That documentation indicates 1. the SQL statement and then 2. the individual parameters being passed in a list to a single call to cursor.executemany().    The DBAPI should be responsible for optimizing this but unfortunately the psycopg2 driver does not optimize it well since it does not use prepared statements, SQLAlchemy has to make use of a more awkward API to make it work (and this API is used much more effectively in 1.4.0).

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/9dbd9e55-96b5-4d28-8561-0da19ab815dbn%40googlegroups.com.