Re: [sqlalchemy] Differences between TableClause insert and Model.__table__ inserts?

[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).





> 
> 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 

Re: [sqlalchemy] Differences between TableClause insert and Model.__table__ inserts?

[Kata Char]

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

class Foo(db.Model):
id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
test = db.Column(db.String(80), nullable=False)

db.session.execute(Foo.__table__.insert(), [{'test': 'foo1'}, {'test': 
'foo2'}])



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')

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'))`

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
>  
> 
> .
>
>
>
> -- 
> 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
>  
> 
> .
>
>
>

-- 
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 

Re: [sqlalchemy] Differences between TableClause insert and Model.__table__ inserts?

[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.

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
  
 .
>>> 
> 

> -- 
> 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/89002280-96e7-45e9-a11a-f104d8e2aa3fn%40googlegroups.com
>  
> .

-- 
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/3d5cf9e9-ce2e-4ded-9c04-3e46d2fbc0ff%40www.fastmail.com.

Re: [sqlalchemy] Differences between TableClause insert and Model.__table__ inserts?

['Jonathan Vanasco' via sqlalchemy]

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
>>  
>> 
>> .
>>
>>
>>

-- 
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/89002280-96e7-45e9-a11a-f104d8e2aa3fn%40googlegroups.com.

Re: [sqlalchemy] Differences between TableClause insert and Model.__table__ inserts?

[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(), 
[{...}, ...])`

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
>  
> 
> .
>
>
>

-- 
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/ea6c2037-613c-4ebf-a4d3-e6fba46cf743n%40googlegroups.com.

Re: [sqlalchemy] Differences between TableClause insert and Model.__table__ inserts?

[Mike Bayer]

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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/1ffe48c6-4124-40ab-902f-ffa86885ea94n%40googlegroups.com
>  
> .

-- 
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/2019f1cc-3e29-4b24-b1d5-180788c83d8a%40www.fastmail.com.

[sqlalchemy] 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? 

-- 
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/1ffe48c6-4124-40ab-902f-ffa86885ea94n%40googlegroups.com.