public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Michael P. McDonnell <[email protected]>
Cc: [email protected]
Subject: Re: Consecutive Inserts Freeze Execution of Psycopg3
Date: Sun, 23 Apr 2023 17:15:32 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAHmCLHrdxjeuRDxS7w2P-9LZ_C5wxsoVyLrVJwRRwrMOXVu4dQ@mail.gmail.com>
References: <CAHmCLHrztOYi=uLqc6jKrytz5=e4ZKhYP1LTyLfTi-KGC1pR9w@mail.gmail.com>
	<[email protected]>
	<CAHmCLHrdxjeuRDxS7w2P-9LZ_C5wxsoVyLrVJwRRwrMOXVu4dQ@mail.gmail.com>

On 4/23/23 14:55, Michael P. McDonnell wrote:
> That helped a ton, I don't understand why I've had to rewrite the crap 
> out of all of this to get it to work (dropping SqlAlchemy, upgrading 
> from psycopg2 to psycopg, etc...) but it's working now and I can work 
> around it. Thank you.

Well:

1) SQLAlchemy is an ORM that tries to make all databases look the same.

2) psycopg2 != psycopg. For details see:

https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html

3) It would have been more of a surprise if you did not have to change 
anything.

4) And this

with self.connection.cursor() as conn:

was just plain wrong. You where trying to make a cursor be a connection 
and that is not going to work.


> 
> 
> On Sun, Apr 23, 2023 at 4:25 PM Adrian Klaver <[email protected] 
> <mailto:[email protected]>> wrote:
> 
>     On 4/23/23 13:45, Michael P. McDonnell wrote:
>      > Python 3.10.6
>      > psycopg library 3.1.8
>      >
>      > Running consecutive inserts sourced in files.
>      > All inserts are of the same format:
>      >
>      > INSERT INTO _____ (field1, field2, field3)
>      > SELECT field1, field2, field3 FROM ____, Join ___, join ___ etc...
>      >
>      > The code I've written is this:
>      >
>      > for qi in range(qlen):
>      >              query = queries[qi]
>      >              qparams = params[qi]
>      >              with self.connection.cursor() as conn:
>      >                  conn.execute(query, qparams)
> 
>     In above you are running the context manager(with) over the cursor not
>     the connection. This will not automatically commit the transaction. You
>     will need to either explicitly do connection.commit() or use the with
>     over the connection per:
> 
>     https://www.psycopg.org/psycopg3/docs/basic/transactions.html
>     <https://www.psycopg.org/psycopg3/docs/basic/transactions.html;
> 
>      >
>      > When I run the queries in dbeaver - the first query takes 120s (it's
>      > 1.9M rows), the second query takes 2s (7000 rows).
>      > When I run the queries in python - it freezes on the second query.
>      >
>      > Any guidance on how to attack this would be awesome as I have
>     re-written
>      > my code a dozen times and am just slinging mud to see what sticks.
> 
>     -- 
>     Adrian Klaver
>     [email protected] <mailto:[email protected]>
> 

-- 
Adrian Klaver
[email protected]

view thread (10+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Consecutive Inserts Freeze Execution of Psycopg3
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox