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.
-Mike On Sun, Apr 23, 2023 at 7:15 PM Adrian Klaver <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 > > 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>> 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> > > > > > > > > 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> > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
