How to use variable in sql query odoo

Question

I want to add branch_id variable in sql query. How can I use?When I use below code,I got psycopg2.ProgrammingError: column reference "branch_id" is ambiguous.

        branch_id = self.env.user.branch_id.id
        query = '''
            SELECT DISTINCT l.partner_id, res_partner.name AS name, UPPER(res_partner.name) AS UPNAME, CASE WHEN prop.value_text IS NULL THEN 'normal' ELSE prop.value_text END AS trust
            FROM account_move_line AS l
              LEFT JOIN res_partner ON l.partner_id = res_partner.id
              LEFT JOIN ir_property prop ON (prop.res_id = 'res.partner,'||res_partner.id AND prop.name='trust' AND prop.company_id=%s),
              account_account, account_move am
            WHERE (l.account_id = account_account.id)
                AND (l.move_id = am.id)
                AND (am.state IN %s)
                AND (account_account.internal_type IN %s)
                AND (
                        l.reconciled IS NOT TRUE
                        OR l.id IN(
                            SELECT credit_move_id FROM account_partial_reconcile where max_date > %s
                            UNION ALL
                            SELECT debit_move_id FROM account_partial_reconcile where max_date > %s
                        )
                    )
                    ''' + partner_clause + '''
                AND (l.date <= %s)
                AND (l.branch_id = branch_id)
                AND l.company_id IN %s
            ORDER BY UPPER(res_partner.name)'''
        arg_list = (self.env.company.id,) + arg_list
        cr.execute(query, arg_list)

Answer 1

I would prefer variable substitute using name for your query, which will make this more understandable. For example:

    branch_id = self.env.user.branch_id.id
    query = '''
        SELECT DISTINCT l.partner_id, res_partner.name AS name, UPPER(res_partner.name) AS UPNAME, CASE WHEN prop.value_text IS NULL THEN 'normal' ELSE prop.value_text END AS trust
        FROM account_move_line AS l
          LEFT JOIN res_partner ON l.partner_id = res_partner.id
          LEFT JOIN ir_property prop ON (prop.res_id = 'res.partner,'||res_partner.id AND prop.name='trust' AND prop.company_id=%(company_id)s),
          account_account, account_move am
        WHERE (l.account_id = account_account.id)
            AND (l.move_id = am.id)
            AND (am.state IN %(state)s)
            AND (account_account.internal_type IN %(internal_types)s
            AND (
                    l.reconciled IS NOT TRUE
                    OR l.id IN(
                        SELECT credit_move_id FROM account_partial_reconcile where max_date > %(max_date)s
                        UNION ALL
                        SELECT debit_move_id FROM account_partial_reconcile where max_date > %(max_date)s
                    )
                )
                ''' + partner_clause + '''
            AND (l.date <= %(date)s)
            AND (l.branch_id = %(branch_id)s)
            AND l.company_id IN %(company_id)s
        ORDER BY UPPER(res_partner.name)'''
    args = {
        'company_id': ...,
        'internal_types': ...,
        'max_date': ...,
        'date': ...,
        'branch_id': ...,
    }
    cr.execute(query, args)