From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | "Michael P(dot) McDonnell" <bzaks1424(at)gmail(dot)com> |
Cc: | psycopg(at)postgresql(dot)org |
Subject: | Re: Consecutive Inserts Freeze Execution of Psycopg3 |
Date: | 2023-04-24 01:20:48 |
Message-ID: | 021e900b-53d0-c5a0-2750-a0fe60230e68@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(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 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(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>>
> > > <mailto: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>>
> > >
> >
> <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>>
> > <mailto: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>
> <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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael P. McDonnell | 2023-04-24 21:16:42 | Re: Consecutive Inserts Freeze Execution of Psycopg3 |
Previous Message | Michael P. McDonnell | 2023-04-24 01:06:20 | Re: Consecutive Inserts Freeze Execution of Psycopg3 |