Update statement using Inner join in sql server using pyodbc in Python

Refactoring your code for clarity,

def fun(cur,tablename):

    cur.execute("""UPDATE sam
        SET
        sam.agreement_id = ?
        FROM '{0}' sam
        INNER JOIN dbo.agreement_mapping am ON sam.agreement_id = ?;
        """.format(tablename.replace('\'', '\'\'')),am.newagreementid,am.oldagreementid)

becomes

def fun(cur,tablename):
    stmt = """\
        UPDATE sam
        SET
        sam.agreement_id = ?
        FROM '{0}' sam
        INNER JOIN dbo.agreement_mapping am ON sam.agreement_id = ?;
        """.format(tablename.replace('\'', '\'\''))
    cur.execute(stmt, am.newagreementid, am.oldagreementid)

There are two problems:

First, '{0}' wraps the table name in single quotes, turning it into a string literal. SQL Server table names are delimited by square brackets.

Second, the am.newagreementid and am.oldagreementid references in the execute call are evaluated in Python. Your Python code does not declare any Python variable or object named am, so those references are invalid.

So instead, you need to do something like this:

def fun(cur,tablename):
    stmt = """\
        UPDATE sam
        SET
        sam.agreement_id = am.newagreementid
        FROM [{0}] sam
        INNER JOIN dbo.agreement_mapping am ON sam.agreement_id = am.oldagreementid;
        """.format(tablename.replace('\'', '\'\''))
    cur.execute(stmt)