copy LONGTEXT from MySQL to CITEXT in PostgreSQL using Python

Question

I have data in MySQL table which I want to copy to a PostgreSQL table. Everything works except when the MySQL contains a string with " and/or '

For example: The data in MySQL:

When I run my code I get:

ProgrammingError: ERROR:  syntax error at or near "t"

(the t of the can't)

This is my code:

postgre = pg.connect(dbname=DB,user=USR,passwd=PASSWD,host=HOST, port=PORT)
crs = db_remote.cursor(MySQLdb.cursors.DictCursor)
crs.execute ("""select post_id, post_excerpt from tabl""")
data = crs.fetchall ()
for row in data :  
    postgre.query("""INSERT INTO importfrommysql(id,message)
    VALUES ('%s','%s')"""%(row["post_id"],row["post_excerpt"]))

the connection pg.connect is from PygreSQL package. What can I do? Is it possible to get the text as it is? or the only solution is to drop all " / ' before the insert?

Answer 1

Use the Psycopg cursor.execute parameter passing:

import psycopg2
conn = psycopg2.connect(database='DB')
cursor = conn.cursor()

for row in data :

    cursor.execute ("""
        INSERT INTO importfrommysql (id,message)
        VALUES (%s,%s)
        """,
        (row["post_id"],row["post_excerpt"])
    )

It will escape and quote as necessary.