import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine("postgresql://scott:tiger@192.168.0.199/test")

# set up test environment
tbl = sa.Table(
    "author",
    sa.MetaData(),
    sa.Column("id", sa.Integer, primary_key=True, autoincrement=False),
    sa.Column("name", sa.Unicode(50)),
    sa.Column("title", sa.Unicode(10)),
)
tbl.drop(engine, checkfirst=True)
tbl.create(engine)
with engine.begin() as conn:
    conn.execute(tbl.insert(), {"id": 1, "name": "Unknown"})

# create test data
df = pd.DataFrame(
    [('Edwin "Buzz" Aldrin', "Mr", 1)], columns=["name", "title", "id"]
)

# run test
df.name = df.name.str.replace("'", "''")
df.title = df.title.str.replace("'", "''")
query = sa.text("""
            INSERT INTO author(name, title, id)
            VALUES %s
            ON CONFLICT (id)
            DO  UPDATE SET name= excluded.name,
                           title= excluded.title
     """ % ','.join([str(i) for i in list(df.to_records(index=False))]).replace('"', "'"))
engine.execute(query)
"""console output:
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "Buzz"
LINE 3:             VALUES ('Edwin 'Buzz' Aldrin', 'Mr', 1)
                                    ^
"""