Re: Consecutive Inserts Freeze Execution of Psycopg3 - Mailing list psycopg

On 4/23/23 18:06, Michael P. McDonnell wrote:
> So I appreciate you're trying to point to the official source of the 
> documentation; but one place where FastAPI might be a place of 

This is the first mention of using FastAPI.

> inspiration is the consistent building of a single use case example to 
> show how one might do something more complex. Given the docs available - 
> I have no idea how I might take a dict and generate an UPDATE statement 
> using the sql.SQL API you have.

Some pseudo-code showing what you are trying to achieve would be useful.

> 
> And I'm genuinely not trying to nitpick here, I'm relatively new to 
> Python and am more or less winging it and the documentation immediately 
> available doesn't exactly spell out *all the things*; So again - thank 
> you for your help in getting me over the original hump; I appreciate it.

It is tough for documentation to cover all possible user cases. I have 
found  the best way for me is to write a flow chart of what I want to do 
and then search for the commands/code that gets me there.

> 
> On Sun, Apr 23, 2023 at 7:59 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     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
>     <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>
>      > <mailto: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>
>      >     <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>>
>      >      > <mailto: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>>
>      >      >
>      >     
>       <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>>
>      >     <mailto: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>
>     <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