python - postgresql 如何插入含 ‘ 数据？

Question

cur.execute("UPDATE scholars SET name='{}' WHERE id={} and name is null".format(author, scholar_id))

psycopg2.ProgrammingError: syntax error at or near "Neill"
LINE 1: UPDATE scholars SET name='O'Neill, Kevin' WHERE id=12403 and...

数据应该是:O'Neill, Kevin

Answer 1

cur.execute(
    """UPDATE scholars SET name = %s WHERE id = %s and name is null""", (author, scholar_id))

Answer 2

对插入的数据先做转义处理，一个单引号变成两个单引号，如O'Neill, Kevin 处理成O''Neill, Kevin

Answer 3

单引号冲突了吧

Answer 4

cur.execute('''UPDATE scholars
               SET name = "%s"
               WHERE id = %s and name is null
               ''' % (author, scholar_id))