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