Re: Consecutive Inserts Freeze Execution of Psycopg3

On 4/23/23 17:26, Michael P. McDonnell wrote:
> 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.

Start here:

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

and work through the sections in order.

>
> -Mike
>
> On Sun, Apr 23, 2023 at 7:15 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> 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
> <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
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>> 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>
> >
>  <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
> > adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
> >
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com