TypeError:“dict”对象不支持cur.execu中的索引

Question

我想向数据库发出请求，需要替换一些参数

我的问题是：

    SUBSCRIPTIONS = '''
select
    distinct si.id,
    s.name as service_name,
    si.request_id,
    si.activation_time,
    si.extra->>'MSISDN' as msisdn,
    (case when t.status = 'success' then t.amount else 0 end) as initial_amount
from processing.service_instance si
inner join processing.service s on s.id = si.service_id
inner join processing.payer_payment_source pps on pps.payer_id = si.payer_id
left join processing.service_instance si2 on
  si2.payer_id = pps.payer_id and
  si2.id < si.id and
left join processing.transaction t on t.service_instance_id = si2.id
where
  si.service_id in (
    select s.id from processing.service s
    inner join processing.flow f on f.id = s.flow_id
    inner join processing.contractor c on c.id = s.contractor_id
    where
      c.slug = 'mts_bank'
  )
  and si.instance_details->>'operator' = %operator
'''

我的职能：

def get_subscriptions_by_date(cur, date, operator):
try:
    cur.execute(SUBSCRIPTIONS, {'date': date, 'operator': operator})
except Exception as e:
    log.error(f'{traceback.format_exc()}')
    raise ValueError(str(e))
else:
    while cur.rownumber != cur.rowcount:
        yield cur.fetchone()

我得到这个错误：

    cur.execute(SUBSCRIPTIONS, {'date': date, 'operator': operator})
TypeError: 'dict' object does not support indexing

如何解决这个问题

Answer 1

在将字典传递给execute时，必须按如下方式编写要替换的值：

%(date)s
%(operator)s

关于它的文档是psycopg的here