[Python] Encoding Issue in PostgreSQL for Persian/Arabic Characters [closed]

I'm working on a Python desktop application on windows for an exchange company that processes remittance data from Excel files and stores them in a PostgreSQL database. The problem I'm facing is with encoding issues for Persian and Arabic characters when storing text fields like names, brokers, etc.

I have set the database and client encoding to UTF-8, but I still get corrupted characters in my PostgreSQL table. Here's a glimpse of the issue: Glimpse of the table

I'm trying to handle this by normalizing the characters using a Python function before inserting them into the database:

replacement_map = {
'\u06cc': '\u064a', # Persian 'ی' to Arabic 'ي'
'\u06a9': '\u0643', # Persian 'ک' to Arabic 'ك'
'\u06F0': '\u0660', # Persian '۰' to Arabic '٠'
'\u06F1': '\u0661', # Persian '۱' to Arabic '١'
'\u06F2': '\u0662', # Persian '۲' to Arabic '٢'
'\u06F3': '\u0663', # Persian '۳' to Arabic '٣'
'\u06F4': '\u0664', # Persian '۴' to Arabic '٤'
'\u06F5': '\u0665', # Persian '۵' to Arabic '٥'
'\u06F6': '\u0666', # Persian '۶' to Arabic '٦'
'\u06F7': '\u0667', # Persian '۷' to Arabic '٧'
'\u06F8': '\u0668', # Persian '۸' to Arabic '٨'
'\u06F9': '\u0669' # Persian '۹' to Arabic '٩'
}

def replace_problematic_characters(text):
if text: # Check if text is not None
for persian_char, arabic_char in replacement_map.items():
text = text.replace(persian_char, arabic_char)
return text



The insert function:

def insert_data_to_db(conn, data):
cursor = conn.cursor()
query = """
INSERT INTO variziha (name, amount, transaction_number, seri, broker)
VALUES (%s, %s, %s, %s, %s)
"""
try:
for item in data:
name = replace_problematic_characters(item[0])
broker = replace_problematic_characters(item[4])
amt = item[1]
t_id = item[2]
seri = item[3]

modified_item = (name, amt, t_id, seri, broker)

try:
cursor.execute(query, modified_item)
conn.commit()
except psycopg2.Error as e:
conn.rollback()
print(f"Error inserting row: {modified_item}")
print(f"Error message: {e}")
print(f"Finished inserting data. Total rows processed: {len(data)}")
except psycopg2.Error as e:
conn.rollback()
print(f"An error occurred while inserting data: {e}")
finally:
cursor.close()



Despite normalizing these characters before inserting the data, the output in the PostgreSQL table is still corrupted. Has anyone faced this issue before? How can I resolve this encoding problem?

Any help is greatly appreciated!

Continue reading...