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>
<https://www.psycopg.org/docs/cursor.html#cursor.executemany>
<https://www.psycopg.org/docs/cursor.html#cursor.executemany>
<https://www.psycopg.org/docs/cursor.html#cursor.executemany>
<https://www.psycopg.org/docs/cursor.html#cursor.executemany>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>
<https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#psycopg2-executemany-mode>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).
>
> On Wednesday, November 25, 2020 at 2:34:36 PM UTC-8 Mike Bayer wrote:
>> __
>> 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.
>>
>> On Wed, Nov 25, 2020, at 4:33 PM, 'Jonathan Vanasco' via sqlalchemy wrote:
>>> 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(`.
>>>
>>> On Wednesday, November 25, 2020 at 4:18:46 PM UTC-5 Kata Char wrote:
>>>> 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(),
>>>> [{...}, ...])`
>>>>
>>>> On Wednesday, November 25, 2020 at 8:27:53 AM UTC-8 Mike Bayer wrote:
>>>>>
>>>>>
>>>>> On Wed, Nov 25, 2020, at 10:30 AM, Kata Char wrote:
>>>>>> 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?
>>>>>
>>>>> 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
>>>>>>
>>>>>> <https://groups.google.com/d/msgid/sqlalchemy/1ffe48c6-4124-40ab-902f-ffa86885ea94n%40googlegroups.com?utm_medium=email&utm_source=footer>.
>>>>>
>>>
>>> --
>>> 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/89002280-96e7-45e9-a11a-f104d8e2aa3fn%40googlegroups.com
>>>
>>> <https://groups.google.com/d/msgid/sqlalchemy/89002280-96e7-45e9-a11a-f104d8e2aa3fn%40googlegroups.com?utm_medium=email&utm_source=footer>.
>>
>
> --
> 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/9dbd9e55-96b5-4d28-8561-0da19ab815dbn%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/9dbd9e55-96b5-4d28-8561-0da19ab815dbn%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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/7a31ba2f-b435-474e-b0f9-906e7afb6de6%40www.fastmail.com.
