Postgres encoding Error

Question

I am having trouble trying to copy a few tables from a mysql server to postgres server. Here is the code that I am currently using to connect, fetch the data and write the data.

my_sqlconnection = MySQLdb.connect(host='a.b.c.d',
                              user='user',
                              passwd='admin'
                              )
try:
pl_sqlconnection = psycopg2.connect("host='x.y.z.c' dbname='user' 
                   user='uadmin' password='uadmin'" )
except psycopg2.Error as e:
print('PSQL: Unable to connect!\n{0}')
print (e)
print (e.pgcode)
print (e.pgerror)
print (traceback.format_exc())
sys.exit(1)
cursor1 = my_sqlconnection.cursor(MySQLdb.cursors.DictCursor)
cursor2 = pl_sqlconnection.cursor()
for db in db_list.db_set:
    restaurant_name = db[:-3]
    my_sqlconnection.select_db(db)

    sql = "SELECT * FROM Orders"
    cursor1.execute(sql)
    for row in cursor1:
    try:
       cursor2.execute("INSERT INTO Orders (all the values) Values
       (%(all the values)s)", row)
    except psycopg2.Error as e:
        print ("cannot execute that query!!")
        print (e)
        print (e.pgcode)
        print (e.pgerror)
        print (traceback.format_exc())
        sys.exit("Some problem occured with that query! leaving early")
    sql2 = "SELECT * FROM USERS"
    cursor1.execute(sql2)
    for row in cursor1:
    try:
       cursor2.execute("INSERT INTO Users (all the values) Values
       (%(all the values)s)", row)
    except psycopg2.Error as e:
        print ("cannot execute that query!!")
        print (e)
        print (e.pgcode)
        print (e.pgerror)
        print (traceback.format_exc())
        sys.exit("Some problem occured with that query! leaving early")
cursor1.close()
cursor2.close()

pl_sqlconnection.commit()

my_sqlconnection.close()
pl_sqlconnection.close()

Now the error that I am getting is

python backup.py
cannot execute that query!!
invalid byte sequence for encoding "UTF8": 0xeef1e5
HINT:  This error can also happen if the byte sequence does not match   the encoding expected by the server, which is controlled by "client_encoding".

22021 ERROR: invalid byte sequence for encoding "UTF8": 0xeef1e5 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".

This error is shown specifically when I am trying to execute the 2nd query. When I run only the 1st query, everything runs as it is supposed to. Both the tables are present in the same database. Why is it that the encoding error is shown while executing the 2nd query.

It seems that your target DB (Postgres) has charset UTF-8 but your source DB (or table) has a different encoding. Be careful because in MySQL you can have different charsets for the whole database and for single tables. Maybe have a look here https://stackoverflow.com/questions/1049728/how-do-i-see-what-character-set-a-mysql-database-table-column-is — mastazi, Apr 06 '17 at 07:03
PS this is how you convert charset in MySQL, may be handy as well. https://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8 — mastazi, Apr 06 '17 at 07:06

Postgres encoding Error

0 Answers0