python中mysql查询如何引用dict变量值

Question

我正在使用python开发mysql连接器我正在使用dict{'invoice.approved_on': {'to': '2018-1-11 23:59:00', 'from': '2017-5-13 00:00:00'}，但是我不确定如何在查询中引用它我尝试了%(invoice.approved_on[from])s AND %(invoice.approved_on[to])s)这样做，但是它不起作用请让我知道如何在python查询中引用dict值

cursor = self.conn.cursor()
cursor.execute('SELECT invoice.`id` AS `invoice.id` from invoice where invoice.`approved_on` BETWEEN %(invoice.approved_on[from])s AND %(invoice.approved_on[to])s) GROUP BY invoice.id', {'invoice.approved_on': {'to': '2018-1-11 23:59:00', 'from': '2017-5-13 00:00:00'})

这里我需要知道如何在查询中引用from和to，比如invoice.approved\u on[from]或invoice.approved\u on.from或invoice.approved\u on.0

它抛出一些错误如下

 [ERROR] 2018-06-24 16:03:06,701 <erp.accounts.reportsQueryBuilder> 'invoice.approved_on.0'
Traceback (most recent call last):
  File "/home/charles/PycharmProjects/2.8/XSerp/util/reportsQueryBuilder.py", line 183, in select
    ), where)
  File "/home/charles/.virtualenvs/venvpython/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 185, in execute
    for key, item in args.iteritems())
KeyError: 'invoice.approved_on.0'
[ERROR] 2018-06-24 16:03:06,703 <erp.middleware> Exception Handling: local variable 'response' referenced before assignment

Answer 1

your_dict = {'invoice.approved_on': {'to': '2018-1-11 23:59:00', 'from': '2017-5-13 00:00:00'}
cursor.execute(f'SELECT invoice.`id` AS `invoice.id` from invoice where invoice.`approved_on` BETWEEN {your_dict['invoice.approved_on']['from']} AND {your_dict['invoice.approved_on']['to']} ) GROUP BY invoice.id')

如果您使用的是python3，那么可以使用上面列出的fstrings

如果您正在使用python2（看起来是），或者需要向后兼容，请使用.format（）

your_dict = {'invoice.approved_on': {'to': '2018-1-11 23:59:00', 'from': '2017-5-13 00:00:00'}
cursor.execute('SELECT invoice.`id` AS `invoice.id` from invoice where invoice.`approved_on` BETWEEN {} AND {} ) GROUP BY invoice.id'.format(your_dict['invoice.approved_on']['from'], your_dict['invoice.approved_on']['to'])