CODEWITHSUNDEEP
pythonoracle-databasepython-2.7spotfirecodepages
J K
J K

Reputation: 165

Character set issue while writing to oracle DB using python script

i am writing to a oracle table using python script in spotfire. i am picking value from spotfire and inserting it to db using following part of script.

from Spotfire.Dxp.Data.Import import DatabaseDataSource, DatabaseDataSourceSettings, DataTableDataSource
objID = Document.Properties["OBJID"]
objectname = Document.Properties["ObjName"]
sqlIns = sqlIns + "insert into mytablename (OBJ_ID,OBJ_NAME) VALUES ('" + objID + "','" + objectname + "')" 
print sqlIns
dbsettings = DatabaseDataSourceSettings( "System.Data.OracleClient","Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx.com)(PORT=1530))(CONNECT_DATA=(SID=xxx)));User Id=xxxx;Password=xxx",sqlIns)
ds = DatabaseDataSource(dbsettings)
newDataTable = Document.Data.Tables.Add("temp",ds)
Document.Data.Tables.Remove(newDataTable)

This is working fine when values in OBJ_NAME is in English,when i am inserting OBJ_NAME in other languages such as korean data base is showing values like "¿¿¿¿¿¿ ¿¿¿ ¿ MD -¿¿¿". But when i printed above python code i can see that it displayed correct korean character. please see the image below.

enter image description here

But In oracle its not showing correctly. See screenshot of oracle table below enter image description here

As data type of OBJ_NAME in oracle is Nvarchar ,if i insert this Korean value from oracle itself ,it will insert correct Korean character.

Upvotes: 1

Views: 433

Answers (2)

Gul Saeed Khattak
Gul Saeed Khattak

Reputation: 1

This issue can be resolved in two stages. First, ensure that you load the data in Python correctly, and secondly, write the data to the destination database accurately. Here's how you can achieve this:

  1. Loading Data in Python:

In your Python code, when establishing a connection to your database, use the character set utf8mb4 in the connection string. This character set supports all languages, ensuring that the data is loaded properly.

For example, if you are loading data from MySQL:

connection_string = f"mysql+pyodbc://@{dsn}?charset=utf8mb4"

utf8mb4 Character Set: This character set is a comprehensive version of UTF-8, capable of encoding all Unicode characters, including those outside the Basic Multilingual Plane (BMP). It is essential for correctly handling characters from languages like Korean, Chinese, Japanese, and emojis.

  1. Writing Data to Oracle from Python:

When writing data to Oracle from Python, set the data type of the relevant columns to NVARCHAR2. This ensures that your Korean characters (or any other Unicode characters) are written exactly as they are stored in the source database or loaded in your Python code.

Upvotes: -1

Leo
Leo

Reputation: 898

Can you try encoding to utf-8 in python script.

Upvotes: 0

Related Questions