Use string format methods

[jiffyclub]

By using the string .format() method we can take advantage of the new-ish string formatting syntax. This makes for somewhat more readable query strings, because instead of seeing a bunch of %s placeholders you can use named placeholders like {table} and {col}.

For example:

exec_sql(
    ("UPDATE {tname} "
     "SET {tfield} = (SELECT SUM(ST_Area(ST_Intersection({tname}.geom, {oname}.geom))) "
     "FROM {oname} "
     "WHERE ST_Intersects({tname}.geom, {oname}.geom)) / {tname}.calc_area;"
     ).format(
        tname=target_table_name, tfield=target_field,
        oname=overlapping_table_name))

Note also I'm using parentheses to write strings across multiple lines so that the queries don't have to go all on one line. You can break it up for readability. (Python basically ignores newlines within parentheses and brackets, and it'll concatenate separate strings it finds within parentheses.)

[jiffyclub]

As noted by @daradib, this style of string formatting is for substituting in table and column names, things like that. If you're passing in variables for insertion use psycopg2's parameter passing.