如何解决"IndexError:元组索引超出范围“？

Question

我有一个sql语句，它的目标是对表的sum列进行求和：

p_id   p_name     atc_code         tax_amt   base_amt    date
2300  |A student |WC158 - EWT 1%  |133.93   |13392.86   |2015-07-01
2300  |A student |WC158 - EWT 1%  |62.50    |6250.00    |2015-07-01
901   |B student |WC158 - EWT 1%  |8.31     |830.58     |2015-06-09
2831  |C student |WC160 - EWT 2%  |2736.84  |136842.11  |2015-06-04
905   |D student |WC158 - EWT 1%  |31.25    |3125.00    |2015-06-16
905   |D student |WC158 - EWT 1%  |31.25    |3125.00    |2015-06-29
905   |D student |WC158 - EWT 1%  |31.25    |3125.00    |2015-06-29
905   |D student |WC158 - EWT 1%  |26.79    |2678.57    |2015-06-16
959   |G student |WC158 - EWT 1%  |114.29   |11428.57   |2015-01-10
959   |G student |WC158 - EWT 1%  |478.90   |47890.18   |2015-01-20
2424  |L student |WC158 - EWT 1%  |45.54    |4553.58    |2015-03-03

我也有这份声明。

...
cr.execute('''insert into student_resource_report_line(partner_id,seq,base_amount,tax_amount,percent,atc_code,nature,create_date,write_date)
        select es.partner_id as partner_id,
            (case when es.name like '%WC158%' then 1
                when es.name like '%WC160%' then 2
                when es.name like '%WC010%' then 3
                when es.name like '%WC140%' then 4
                else 0 end) as seq,
            sum(es.base_amt) as base_amount,
            sum(es.tax_amt) as tax_amount,
            (case when es.name like '%EWT 1%%' then '1.00'
                when es.name like '%EWT 2%%' then '2.00'
                when es.name like '%EWT 3%%' then '3.00'
                when es.name like '%EWT 4%%' then '4.00'
                when es.name like '%EWT 5%%' then '5.00'
                when es.name like '%EWT 6%%' then '6.00'
                when es.name like '%EWT 7%%' then '7.00'
                when es.name like '%EWT 8%%' then '8.00'
                when es.name like '%EWT 9%%' then '9.00'
                when es.name like '%EWT 10%%' then '10.00'
                else null end) as percent,
            (case when es.name like '%WC158%' then 'WC158'
                when es.name like '%WC160%' then 'WC160'
                when es.name like '%WC010%' then 'WC010'
                when es.name like '%WC140%' then 'WC140'
                else null end) as atc_code,
            (case when es.name like '%WC158%' then 'NOTEBOOK'
                when es.name like '%WC160%' then 'BACKPACK'
                when es.name like '%WC010%' then 'COLOR'
                when es.name like '%WC140%' then 'BOOKS' else null end) as nature,
            (now()) as create_date,(now()) as write_date
        from ewt_source es where es.date between ? and ? 
        group by es.partner_id,es.name''',(ewt.date_from,ewt.date_to)) 

其中ewt.date_from和ewt.date_to是用户的输入。我无法理解的是，在执行过程中(在我的视图中调用此方法时)，它会产生一个错误："IndexError: tuple索引超出范围“。这是我在日志中经常看到的，它还说我的查询很糟糕。

group by es.partner_id,es.name''',(ewt.date_from,ewt.date_to))
File "/opt/openerp/server-7/openerp/sql_db.py", line 161, in wrapper
       return f(self, *args, **kwargs)
File "/opt/openerp/server-7/openerp/sql_db.py", line 226, in execute
   res = self._obj.execute(query, params)
IndexError: tuple index out of range

有人能指出我在哪里犯的错误吗？它真的让我头晕。

Answer 1

我犯了一个令人沮丧的错误。因此，以下可能是查询的问题：

1)当您有like时，请确保语句中有两个百分比符号，即like %%WC158%%，因此在执行时，这将转换为%WC158%

2)例如，你有

es.date between ? and ? 
                    group by es.partner_id,es.name'''

在您的代码中尝试使用%s而不是?

3)你的代码中有这一部分

''',(ewt.date_from,ewt.date_to))'''

确保在结尾处有一个额外的逗号。

,(ewt.date_from,ewt.date_to,))

Cur.execute(“插入foo值(%s)”、"bar") #错误 Cur.execute(“插入foo值(%s)"，("bar")) #错误 Cur.execute(“插入foo值(%s)"，("bar"，)) #更正 Cur.execute(“插入foo值(%s)”、"bar") #更正

来源

Answer 2

试着替换你的“？”在openerp sql-s中也使用了'%s‘。

也许可以尝试将SQL格式化为这样的内容：

cr.execute('SELECT id FROM account_move_line \
           WHERE state = %s \
           AND company_id in (%s) \
           ORDER BY id DESC ',\
           ('valid', company_ids))

或可能：

self.cr.execute("SELECT sum(debit-credit) " \
                "FROM account_move_line AS l " \
                "JOIN account_move am ON (am.id = l.move_id)" \
                "WHERE l.account_id IN %s" \
                "AND am.state IN %s",
                ( tuple(self.account_ids), tuple(move_state), ))

希望它能帮上忙！

Answer 3

从查询中删除符号，使用%s，最后删除,并替换为%(ewt.date_from，ewt.date_to)

简而言之，查询看起来是，

qry = "select * from table where field1 = %s and field2=%s" %(value1, value2)
cr.execute(qry)