Feedback-ID: i76984098:Fastmail
Message-ID: <04fea413-8776-dd4e-7f0e-4b235b454f19@aklaver.com>
Date: Sun, 23 Apr 2023 17:59:38 -0700
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.10.0
Subject: Re: Consecutive Inserts Freeze Execution of Psycopg3
Content-Language: en-US
To: "Michael P. McDonnell" <bzaks1424@gmail.com>
Cc: psycopg@postgresql.org
References: 
 <CAHmCLHrztOYi=uLqc6jKrytz5=e4ZKhYP1LTyLfTi-KGC1pR9w@mail.gmail.com>
 <237c1e3f-4118-6ffc-6e0b-ddce824f1f55@aklaver.com>
 <CAHmCLHrdxjeuRDxS7w2P-9LZ_C5wxsoVyLrVJwRRwrMOXVu4dQ@mail.gmail.com>
 <a565b356-d3d4-21e6-054c-7eca541daee6@aklaver.com>
 <CAHmCLHq5hHaDfVi_=p9PAaLjdsKEiZvDZVqUOgTd46+UizHBaQ@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: 
 <CAHmCLHq5hHaDfVi_=p9PAaLjdsKEiZvDZVqUOgTd46+UizHBaQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/04fea413-8776-dd4e-7f0e-4b235b454f19%40aklaver.com>
Precedence: bulk

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.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>      > <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>     <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>      >
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com