PostgreSQL + Python 3.9 查询如果更改会出现问题 – 为什么?

huangapple go评论67阅读模式
英文:

PostgeSQL + Python 3.9 Query breaks if changed - why?

问题

In my code, if I change this (working code - so the variables are working just fine)

cross_cross_OFR_SQL = ''' SELECT DISTINCT ON (racedate) * FROM testview
                    WHERE horsename = %s
                    AND racedate > %s
                    ORDER BY racedate ASC
                    LIMIT 2
'''
cur.execute(cross_cross_OFR_SQL, (cross_detailedhorse, past_racedate,))
cross_cross_OFR_SQLR = cur.fetchall()

to this

cross_cross_OFR_SQL = ''' SELECT DISTINCT ON (racedate) * FROM testview
                    WHERE track NOT LIKE '%KSA%'
                    AND horsename = %s
                    AND racedate > %s
                    ORDER BY racedate ASC
                    LIMIT 2
'''
cur.execute(cross_cross_OFR_SQL, (cross_detailedhorse, past_racedate,))
cross_cross_OFR_SQLR = cur.fetchall()

it breaks with this error: 'IndexError: tuple index out of range'

If I change it to

SELECT DISTINCT ON (racedate) * FROM testview
WHERE track NOT LIKE '%(KSA)%'
AND horsename = %s
AND racedate > %s
ORDER BY racedate ASC
LIMIT 2

it breaks with the error: 'TypeError: tuple indices must be integers or slices, not str'

I can not figure out why it breaks inside the Python code.
Any ideas?

I want to exclude any tracks that have (KSA) in them.

It works fine in PGadmin4.

英文:

In my code, if I change this (working code - so the variables are working just fine)

cross_cross_OFR_SQL = '''
                    SELECT DISTINCT ON (racedate) * FROM testview
                    WHERE horsename = %s
                    AND racedate > %s
                    ORDER BY racedate ASC
                    LIMIT 2
'''
cur.execute(cross_cross_OFR_SQL, (cross_detailedhorse, past_racedate,))
cross_cross_OFR_SQLR = cur.fetchall()

to this

cross_cross_OFR_SQL = '''
                    SELECT DISTINCT ON (racedate) * FROM testview
                    WHERE track NOT LIKE '%KSA%'
                    AND horsename = %s
                    AND racedate > %s
                    ORDER BY racedate ASC
                    LIMIT 2
'''
cur.execute(cross_cross_OFR_SQL, (cross_detailedhorse, past_racedate,))
cross_cross_OFR_SQLR = cur.fetchall()

it breaks with this erro 'IndexError: tuple index out of range'

If I change it to

SELECT DISTINCT ON (racedate) * FROM testview
WHERE track NOT LIKE '%(KSA)%'
AND horsename = %s
AND racedate > %s
ORDER BY racedate ASC
LIMIT 2

it breaks with the error: 'TypeError: tuple indices must be integers or slices, not str'

I can not figure out why it breaks inside the python code.
Any ideas?

I want to exclude any tracks that have (KSA) in them.

It works fine in PGadmin4

答案1

得分: 0

请看以下翻译:

Tim RobertsAdrian Klaver指出:

> 这里的%字符是特殊的,因为它是一个替代标记(就像您正在使用的%s一样)。要使一个简单的%字符通过,您必须将其加倍。

英文:

As Tim Roberts and Adrian Klaver pointed out:

> The % character is special here, because it is a substitution marker (as in the %s you are using). To get a simple % character to pass through, you have to double it.

huangapple
  • 本文由 发表于 2023年5月14日 07:36:05
  • 转载请务必保留本文链接:https://go.coder-hub.com/76245267.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定