Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pqkt6-0000br-TK for psycopg@arkaria.postgresql.org; Mon, 24 Apr 2023 01:21:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pqkt5-0006Og-R5 for psycopg@arkaria.postgresql.org; Mon, 24 Apr 2023 01:21:03 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pqkt4-0006OX-CQ for psycopg@lists.postgresql.org; Mon, 24 Apr 2023 01:21:03 +0000 Received: from wout5-smtp.messagingengine.com ([64.147.123.21]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pqksv-004i5t-Rz for psycopg@postgresql.org; Mon, 24 Apr 2023 01:21:01 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.west.internal (Postfix) with ESMTP id 4EA853200912; Sun, 23 Apr 2023 21:20:50 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute3.internal (MEProxy); Sun, 23 Apr 2023 21:20:50 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to; s=fm2; t= 1682299249; x=1682385649; bh=wzrZvB0pQY6LlInEls7uc6GdCanKo70ryGQ FodPVmqc=; b=BXSte5Ih21yCK7DI5WKo1HuzL3asx2qYEIsWSJmvK3Q+DkG2lrW i4ULJUEJrPa8IJlj7uEDE96iyh6gjOG7e6kQzay2zEZpeSjQirNJWzYFvKY86i8R /x+D1+2tuAfnRYCvaGsKHq3KzkoEMYsK10g2LYSOLldNL1VcqzdoO6P4pUAc51NP 1zKkGF82q28wS1giIXwbKhrRfoddgXi/k+k3BWlWks03KAKoajQqsCP2YGgN3v3E g+6ZgKN4eumusVT/EeR3kTizQRPIOu7ZjlE7iqfYy1NQ98UNevqj1YM+gL591v0Q z/xF/azGKyT6CV6pjpd4GgcpC4ArMVdjenw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm3; t= 1682299249; x=1682385649; bh=wzrZvB0pQY6LlInEls7uc6GdCanKo70ryGQ FodPVmqc=; b=KYyf79WbowsLZ7FUQ6CwbQOrGzptAKskF6Xnc4djsj2zpEPVXkl 6O0EH1jlxO2yZG+cD7hOpIE7pSNptb3hhFmQ27iPzqzols0BGsBG8mbsJZmPnqqv Z2aP4vZWHtPeLeQB2kc1iixZPAZC0H+zBXB9Qvy3p/uVfm9Ibzcx8w+AUDeR0dd6 JAKn1Rmp9g5feMhmrVhv+qJlwdKJv/lyLTYYhGsWkrGuNirdCkvMsidaAlz50oiz OCbwvFh411Jj09tHyG4eiRjU5RkGKj2CO3Jp8wIEC2BtLpiy8CbgindVrqgnoiRn j35m8IJwZrX9IISXj5qgyDCavlgIKnwZI6g== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrfedtledggeehucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpefkffggfgfuvfevfhfhjggtgfesth ekredttdefjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdr khhlrghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpefgfedvge fgieegtdffueffffevtdektdeghfekgeeigedvkefhueffkeeffeefheenucffohhmrghi nhepphhshigtohhpghdrohhrghenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmh epmhgrihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Sun, 23 Apr 2023 21:20:49 -0400 (EDT) Message-ID: <021e900b-53d0-c5a0-2750-a0fe60230e68@aklaver.com> Date: Sun, 23 Apr 2023 18:20:48 -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" Cc: psycopg@postgresql.org References: <237c1e3f-4118-6ffc-6e0b-ddce824f1f55@aklaver.com> <04fea413-8776-dd4e-7f0e-4b235b454f19@aklaver.com> From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 > 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 > > > and work through the sections in order. > > > > > -Mike > > > > On Sun, Apr 23, 2023 at 7:15 PM Adrian Klaver > > > >> 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 > >      > > > >      > > >      >>> 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 > > > >   > > >      > > > >   > > >   >> > >      > > >      >      > > >      >      > 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 > > > >      >> > >      > > > > >     -- > >     Adrian Klaver > > adrian.klaver@aklaver.com > > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com