[sqlalchemy] confused on optimal use of engine vs connection

[Krishnakant]

Dear all,
I am currently reading
<http://docs.sqlalchemy.org/en/latest/core/connections.html>
and one thing is confusing, rather not clearly mentioned, unless I
missed it.
Which is a better thing to do, specially when executing raw sql queries?
engine.execute or con = engine.connect() and then con.execute()?
Where there is a chance of multiple executes with a set of different
queries to be run one after the other or in a loop, should
engine.execute be used and should I disable pooling for such heavy
select queries being fired one after the other?
I have encountered pool overflow problems in such situations.
Changing max limit solved it for now, but I guess this is not a good
idea in the long run at production level.
Kindly guide me on this as I am totally new in this context.
happy hacking.
Krishnakant.

[Jonathan Vanasco]

as the docs state, `engine.execute(foo)` is shorthand for "connection =
engine.connect()" + "connection.execute(foo)". you can verify this in the
source.

for what you describe, it's usually best to grab an explicit connection and
re-use it.

[Krishnakant]

Post by Jonathan Vanasco
as the docs state, `engine.execute(foo)` is shorthand for "connection
= engine.connect()" + "connection.execute(foo)". you can verify this
in the source.

So it will have no performance difference is it?

Post by Jonathan Vanasco
for what you describe, it's usually best to grab an explicit
connection and re-use it.

If both do same thing then how and why will explicit connection help me
better?
Happy hacking.
Krishnakant.

[Jonathan Vanasco]

Post by Krishnakant
So it will have no performance difference is it?
If both do same thing then how and why will explicit connection help me
better?

the `engine.execute()` will be slower, because you will be
creating/checking-out a different connection for each operation in the
for-loop and (needlessly) leveraging the sqlalchemy connection pool. if
you use `connection.execute()` with a single connection, then you don't
have as much overhead involved with the connection pool management.

Your errors are probably from improperly implementing the connection pool.
I suggest re-reading the connection pool docs and faq to get a better
understanding of what it does and why it does that.

[Krishnakant]

Post by Krishnakant
So it will have no performance difference is it?
If both do same thing then how and why will explicit connection help me
better?
the `engine.execute()` will be slower, because you will be
creating/checking-out a different connection for each operation in the
for-loop and (needlessly) leveraging the sqlalchemy connection pool.
if you use `connection.execute()` with a single connection, then you
don't have as much overhead involved with the connection pool management.
Your errors are probably from improperly implementing the connection
pool. I suggest re-reading the connection pool docs and faq to get a
better understanding of what it does and why it does that.

Thank you Jonathan,
I had guessed about your reply, and it came out correct.
So essentially opening a connection at the start of a class method/
function, doing number of queries and then closing it just before return
would be the right strategy I think?
And is there some kind of cashing available for sql expression as well?
I see a lot of articles on things like memcash etc but they all talk
about ORM.
Happy hacking.
Krishnakant.

[Jonathan Vanasco]

Post by Krishnakant
So essentially opening a connection at the start of a class method/
function, doing number of queries and then closing it just before return
would be the right strategy I think?

No.

The scope should last for one or more "units of work". If you are doing a
website, the scope should be a web request. If you are running a
commandline script, the scope would be the script itself. The connection
may span multiple transactions, which are sqlalchemy's internal unit of
work.

Post by Krishnakant
And is there some kind of cashing available for sql expression as well?

Baked Queries
- http://docs.sqlalchemy.org/en/latest/orm/extensions/baked.html