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 1kr7tT-0006Jj-PL for psycopg@arkaria.postgresql.org; Sun, 20 Dec 2020 23:13:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kr7tS-0003v1-Oi for psycopg@arkaria.postgresql.org; Sun, 20 Dec 2020 23:13:38 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kr7tS-0003uu-FF for psycopg@lists.postgresql.org; Sun, 20 Dec 2020 23:13:38 +0000 Received: from p3plsmtpa09-06.prod.phx3.secureserver.net ([173.201.193.235]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kr7tP-00025w-S6 for psycopg@postgresql.org; Sun, 20 Dec 2020 23:13:37 +0000 Received: from [192.168.1.54] ([98.38.100.21]) by :SMTPAUTH: with ESMTPSA id r7tNkw6LRk95Hr7tNkGeLd; Sun, 20 Dec 2020 16:13:34 -0700 X-CMAE-Analysis: v=2.4 cv=YMFadTKx c=1 sm=1 tr=0 ts=5fdfda9e a=tZXnaF/PLMojNGgMD7Zm7A==:117 a=tZXnaF/PLMojNGgMD7Zm7A==:17 a=r77TgQKjGQsHNAKrUKIA:9 a=C-sdMEEzAAAA:8 a=SNL7wKpgAAAA:8 a=epTmVMiNAAAA:8 a=nN1qTskwAAAA:8 a=ZNgP2aYdxVm0ygq346gA:9 a=QEXdDO2ut3YA:10 a=l8-UVUhtTF0A:10 a=Gua-9QImm5IA:10 a=is9xvi7BltNmu7VccY4A:9 a=wY0HdqirV8E3f7zo:21 a=_W_S_7VecoQA:10 a=7WAne9lm5b0o3WrbkWlm:22 a=uEXi9pYaZPHPrIM342IW:22 a=ndEWmUVY6Yapc0oHF_P4:22 a=nYCRw11HfzszU9rNbXoz:22 a=pHzHmUro8NiASowvMSCR:22 a=n87TN5wuljxrRezIQYnT:22 X-SECURESERVER-ACCT: hagen@datasundae.com Subject: BACK: Inserting a variable into cur.execute statement To: Adrian Klaver , psycopg@lists.postgresql.org, psycopg@postgresql.org References: <0bef01d6cce4$987d8120$c9788360$@datasundae.com> <5c55fb91-0b03-2077-fce7-38f2dcc0d402@aklaver.com> <0bf801d6cce8$011969d0$034c3d70$@datasundae.com> <63f9baec-b185-3278-e8a7-84692579f9d8@aklaver.com> From: Hagen Finley Message-ID: Date: Sun, 20 Dec 2020 16:13:33 -0700 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 In-Reply-To: <63f9baec-b185-3278-e8a7-84692579f9d8@aklaver.com> Content-Type: multipart/alternative; boundary="------------10B28A8F0720A5F3F81F77C7" Content-Language: en-US X-CMAE-Envelope: MS4xfD6XUP9zGk9QY/5GIRNYThLccap74LajKGFy5YcI3KPB6XsBoOPeg3kbOnUp6JR2rK3RuqQvmcdJD+ZFNuGr/OQcK6Pc++oyWHcdG8ntnvNRImRlxpsd JMRYdWYBVvwSfz1sjfNG40dZoZJNn4PJXsrjWDBZmPr5LRHLhtL2kMe1N00KP3PRYj8wNivsXZeyZXWcZkNtG2myh3BFev9+H4xUcggEN1R/27fZV67SyCQf ea5XKEQUGMKUeUin5ogUcfqffQgggvhODnIuAfchJgs= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------10B28A8F0720A5F3F81F77C7 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Hello, I finally got around to trying to implement this code and I am running into an "IndexError: tuple index out of range" problem. I am running a function with parameters from a list: def def_acct_analysis(sht,acct): print(param[par][0]) print(param[par][1]) sheet ="sheet"+str(sht) print(sheet) account = acct print(account) par =0 param = [(1,'ACCT0'),(2,'ACCT1'),(3,'ACCT2'),] for pin param: def_acct_analysis(param[par][0], param[par][1]) par +=1 #Print statements above output: 1 ACCT0 sheet1 ACCT0 I want to insert the account name 'ACCT0' into my cur.execute but I get an error with this code: cur.execute("SELECT COALESCE(SUM(revusd),0) FROM sfdc where stage LIKE 'Win%' AND saccount = %s", (account,)) wind1 = cur.fetchone() conn.commit() > Traceback (most recent call last): >   File > "/home/datasundae/PycharmProjects/Registration_Reports/sfdc_Account_Tab_Analysis_Function.py", > line 333, in > def_acct_analysis(param[par][0], param[par][1]) > File > "/home/datasundae/PycharmProjects/Registration_Reports/sfdc_Account_Tab_Analysis_Function.py", > line 96, in def_acct_analysis >   cur.execute("SELECT COALESCE(SUM(revusd),0) FROM sfdc where stage > LIKE 'Win%' AND saccount = %s", (account,)) > *IndexError: tuple index out of range* I've returned to the psycopg docs but I don't see my error. Can someone else see it? Best, Hagen On 12/7/20 3:31 PM, Adrian Klaver wrote: > On 12/7/20 2:26 PM, hagen@datasundae.com wrote: >> So if I understand this correctly my new cur.execute would read: >> >> account = 'JPMC' >> >>   cur.execute("SELECT SUM(revusd) FROM sfdc where saccount = %s AND >> stage LIKE 'Commit%';",(account )) > > Since you are using a tuple this (account ) would need to be > (account,) per the docs at link previously posted: > > "For positional variables binding, the second argument must always be > a sequence, even if it contains a single variable (remember that > Python requires a comma to create a single element tuple):" > > > >> >> and that would translate to >> >> cur.execute("SELECT SUM(revusd) FROM sfdc where saccount = 'JPMC' AND >> stage LIKE 'Commit%';") >> >> is that right? >> >> > > Not sure what below is supposed to be about? > >> >> Note You can use a Python list as the argument of the IN operator >> using the PostgreSQL ANY operator. >> ids = [10, 20, 30] >> cur.execute("SELECT * FROM data WHERE id = ANY(%s);", (ids,)) >> Furthermore ANY can also work with empty lists, whereas IN () is a >> SQL syntax error. >> >> -----Original Message----- >> From: Adrian Klaver >> Sent: Monday, December 7, 2020 3:04 PM >> To: hagen@datasundae.com; psycopg@lists.postgresql.org; >> psycopg@postgresql.org >> Subject: Re: Inserting variable into >> >> On 12/7/20 2:02 PM, hagen@datasundae.com wrote: >>> Hello, >>> >>> I'd like to use a variable for 'Big Company' (e.g. account) or where >>> = statements generally in my cur.execute statements: >>> >>> cur.execute("SELECT SUM(revusd) FROM sfdc where saccount = 'Big >>> Company' AND stage LIKE 'Commit%';") >>> commitd1 = cur.fetchone() >>> conn.commit() >>> >>> but I don't know the proper syntax with the cur.execute statement to >>> use a variable. >> >> https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries >> >> >>> >>> I imagine others do  - thanks! >>> >>> Best, >>> >>> Hagen >>> >>> >>> >> >> > > --------------10B28A8F0720A5F3F81F77C7 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

Hello,

I finally got around to trying to implement this code and I am running into an "IndexError: tuple index out of range" problem.

I am running a function with parameters from a list:

def def_acct_analysis(sht,acct):
    print(param[par][0])
    print(param[par][1])
    sheet = "sheet"+str(sht)
    print(sheet)
    account = acct
    print(account)


par = 0
param = [(1,'ACCT0'),(2,'ACCT1'),(3,'ACCT2'),]

for p in param:
    def_acct_analysis(param[par][0], param[par][1])

    par += 1

#Print statements above output:

1
ACCT0
sheet1
ACCT0
I want to insert the account name 'ACCT0' into my cur.execute but I get an error with this code:

cur.execute("SELECT COALESCE(SUM(revusd),0) FROM sfdc where stage LIKE 'Win%' AND saccount = %s", (account,))
wind1 = cur.fetchone()
conn.commit()
Traceback (most recent call last):
  File "/home/datasundae/PycharmProjects/Registration_Reports/sfdc_Account_Tab_Analysis_Function.py", line 333, in <module>
    def_acct_analysis(param[par][0], param[par][1])
  File "/home/datasundae/PycharmProjects/Registration_Reports/sfdc_Account_Tab_Analysis_Function.py", line 96, in def_acct_analysis
    cur.execute("SELECT COALESCE(SUM(revusd),0) FROM sfdc where stage LIKE 'Win%' AND saccount = %s", (account,))
IndexError: tuple index out of range
I've returned to the psycopg docs but I don't see my error. Can someone else see it?

Best,

Hagen

On 12/7/20 3:31 PM, Adrian Klaver wrote:
On 12/7/20 2:26 PM, hagen@datasundae.com wrote:
So if I understand this correctly my new cur.execute would read:

account = 'JPMC'

  cur.execute("SELECT SUM(revusd) FROM sfdc where saccount = %s AND stage LIKE 'Commit%';",(account ))

Since you are using a tuple this (account ) would need to be (account,) per the docs at link previously posted:

"For positional variables binding, the second argument must always be a sequence, even if it contains a single variable (remember that Python requires a comma to create a single element tuple):"




and that would translate to

cur.execute("SELECT SUM(revusd) FROM sfdc where saccount = 'JPMC' AND stage LIKE 'Commit%';")

is that right?



Not sure what below is supposed to be about?


Note You can use a Python list as the argument of the IN operator using the PostgreSQL ANY operator.
ids = [10, 20, 30]
cur.execute("SELECT * FROM data WHERE id = ANY(%s);", (ids,))
Furthermore ANY can also work with empty lists, whereas IN () is a SQL syntax error.

-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Monday, December 7, 2020 3:04 PM
To: hagen@datasundae.com; psycopg@lists.postgresql.org; psycopg@postgresql.org
Subject: Re: Inserting variable into

On 12/7/20 2:02 PM, hagen@datasundae.com wrote:
Hello,

I'd like to use a variable for 'Big Company' (e.g. account) or where = statements generally in my cur.execute statements:

cur.execute("SELECT SUM(revusd) FROM sfdc where saccount = 'Big Company' AND stage LIKE 'Commit%';")
commitd1 = cur.fetchone()
conn.commit()

but I don't know the proper syntax with the cur.execute statement to use a variable.

https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries


I imagine others do  - thanks!

Best,

Hagen







--------------10B28A8F0720A5F3F81F77C7--