Odoo 14资金模块计算银行余额时出现SQL语法错误求助

我是Odoo新手，在修复资金（treasury）模块计算银行余额的错误时毫无头绪，以下是服务器抛出的错误信息：

Odoo Server Error

Traceback (most recent call last):
File "/home/equipAccounting/equip/odoo/addons/base/models/ir_http.py", line 237, in _dispatch
result = request.dispatch()
File "/home/equipAccounting/equip/odoo/http.py", line 683, in dispatch
result = self._call_function(**self.params)
File "/home/equipAccounting/equip/odoo/http.py", line 359, in _call_function
return checked_call(self.db, args, *kwargs)
File "/home/equipAccounting/equip/odoo/service/model.py", line 94, in wrapper
return f(dbname, args, *kwargs)
File "/home/equipAccounting/equip/odoo/http.py", line 347, in checked_call
result = self.endpoint(*a, **kw)
File "/home/equipAccounting/equip/odoo/http.py", line 912, in call
return self.method(*args, **kw)
File "/home/equipAccounting/equip/odoo/http.py", line 531, in response_wrap
response = f(*args, **kw)
File "/home/equipAccounting/equip/addons/basic/web/controllers/main.py", line 1393, in call_button
action = self._call_kw(model, method, args, kwargs)
File "/home/equipAccounting/equip/addons/basic/web/controllers/main.py", line 1381, in _call_kw
return call_kw(request.env[model], method, args, kwargs)
File "/home/equipAccounting/equip/odoo/api.py", line 396, in call_kw
result = _call_kw_multi(method, model, args, kwargs)
File "/home/equipAccounting/equip/odoo/api.py", line 383, in _call_kw_multi
result = method(recs, *args, **kwargs)
File "/home/equipAccounting/equip/addons/core/treasury_forecast/models/treasury_bank_forecast.py", line 290, in compute_bank_balances
self.env.cr.execute(main_query)
File "/usr/local/lib/python3.8/dist-packages/decorator.py", line 232, in fun
return caller(func, *(extras + args), **kw)
File "/home/equipAccounting/equip/odoo/sql_db.py", line 101, in check
return f(self, *args, **kwargs)
File "/home/equipAccounting/equip/odoo/sql_db.py", line 298, in execute
res = self._obj.execute(query, params)
Exception

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/home/equipAccounting/equip/odoo/http.py", line 639, in _handle_exception
return super(JsonRequest, self)._handle_exception(exception)
File "/home/equipAccounting/equip/odoo/http.py", line 315, in _handle_exception
raise exception.with_traceback(None) from new_cause
psycopg2.errors.SyntaxError: syntax error at or near ")"
LINE 9: WHERE abs.journal_id IN ()

相关代码片段：

def get_bank_fc_query(self, fc_journal_list, date_start, date_end, company_domain):
    query = """                
            UNION
            SELECT CAST('FBK' AS text) AS type, absl.id AS ID, am.date,                    absl.payment_ref as name, am.company_id, absl.amount_main_currency                    as amount, absl.cf_forecast, abs.journal_id, NULL as kind                FROM account_bank_statement_line absl                
            LEFT JOIN account_move am ON (absl.move_id = am.id)                
            LEFT JOIN account_bank_statement abs ON (absl.statement_id = abs.id)
            WHERE abs.journal_id IN {}                    
            AND am.date BETWEEN '{}' AND '{}'                    
            AND am.company_id in {}            """
            .format(str(fc_journal_list), date_start, date_end, company_domain)
            return query

def get_acc_move_query(self, date_start, date_end, company_domain):
    query = """            
            UNION
            SELECT CAST('FPL' AS text) AS type, aml.id AS ID,aml.treasury_date AS date, am.name AS name, aml.company_id,                aml.amount_residual AS amount, NULL AS cf_forecast,                
            NULL AS journal_id, am.move_type as kind            
            FROM account_move_line aml            
            LEFT JOIN account_move am ON (aml.move_id = am.id)            
            WHERE am.state NOT IN ('draft')                
            AND aml.treasury_planning AND aml.amount_residual != 0                
            AND aml.treasury_date BETWEEN '{}' AND '{}'                
            AND aml.company_id in {}        """
            .format(date_start, date_end, company_domain)
            return query

问题原因 #

报错信息明确指出SQL语法错误：WHERE abs.journal_id IN ()，这是因为传入get_bank_fc_query的fc_journal_list是空列表，直接格式化后生成了无效的SQL语句（PostgreSQL不允许IN关键字后面跟空括号）。

修复方法 #

1. 检查fc_journal_list的来源：确认调用get_bank_fc_query时，这个参数是否被正确赋值，是否至少包含一个journal ID。
2. 在方法内添加空值判断：避免生成无效SQL，比如当fc_journal_list为空时，直接返回空字符串（或者根据业务逻辑调整，比如添加永远为假的条件跳过这部分查询）。

修改后的get_bank_fc_query示例：

def get_bank_fc_query(self, fc_journal_list, date_start, date_end, company_domain):
    # 空列表时返回空字符串，避免生成无效UNION语句
    if not fc_journal_list:
        return ""
    query = """                
            UNION
            SELECT CAST('FBK' AS text) AS type, absl.id AS ID, am.date, absl.payment_ref as name, am.company_id, absl.amount_main_currency as amount, absl.cf_forecast, abs.journal_id, NULL as kind
            FROM account_bank_statement_line absl                
            LEFT JOIN account_move am ON (absl.move_id = am.id)                
            LEFT JOIN account_bank_statement abs ON (absl.statement_id = abs.id)
            WHERE abs.journal_id IN {}                    
            AND am.date BETWEEN '{}' AND '{}'                    
            AND am.company_id in {}            """
    return query.format(tuple(fc_journal_list), date_start, date_end, company_domain)

另外注意：直接用str(fc_journal_list)格式化列表，如果列表只有一个元素会变成[1]，而SQL需要(1)，用tuple(fc_journal_list)更稳妥，能正确生成(1)或者(1,2)这样的格式。

内容的提问来源于stack exchange，提问作者Denny