public inbox for [email protected]
help / color / mirror / Atom feedFrom: Michael P. McDonnell <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: [email protected]
Subject: Re: Consecutive Inserts Freeze Execution of Psycopg3
Date: Sun, 23 Apr 2023 19:26:37 -0500
Message-ID: <CAHmCLHq5hHaDfVi_=p9PAaLjdsKEiZvDZVqUOgTd46+UizHBaQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAHmCLHrztOYi=uLqc6jKrytz5=e4ZKhYP1LTyLfTi-KGC1pR9w@mail.gmail.com>
<[email protected]>
<CAHmCLHrdxjeuRDxS7w2P-9LZ_C5wxsoVyLrVJwRRwrMOXVu4dQ@mail.gmail.com>
<[email protected]>
Thanks Adrian -
I appreciate it; and I've been pouring through documentation to try and get
to this point.
I can't help but feel I'm doing it "wrong" but no website I can find
recently seems to have a "right" way of doing things that's reasonably kept
up.
It would be nice if "wrong" had a way of shooting me in the foot with
verbose errors or warnings.
-Mike
On Sun, Apr 23, 2023 at 7:15 PM Adrian Klaver <[email protected]>
wrote:
> 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: <CAHmCLHq5hHaDfVi_=p9PAaLjdsKEiZvDZVqUOgTd46+UizHBaQ@mail.gmail.com>
* 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