python to mssql encoding problem

Question

Greetings

By using pymssql library, I want to write data to a MSSQL database however I encounter encoding issues. Here is my sample code to write to the DB:

# -*- coding: utf-8 -*-
import _mssql
....
Connection info data here
....
def mssql_connect():
 return _mssql.connect(server=HOST, user=USERNAME, password=PASS, database=DB, charset="utf-8")
con = mssql_connect()
INSERT_EX_SQL = "INSERT INTO myDatabsae (Id, ProgramName, ProgramDetail) VALUES (1, 'Test Characters ÜŞiçÇÖö', 'löşüIIğĞü');"
con.execute_non_query(INSERT_EX_SQL)
con.close()

Sadly the data that was written to DB is corrupted:

enter image description here

The Collacation of my mssql db is: Turkish_CI_AS How can this be solved?

• Kimvais: Does specifying the string explicitly as unicode help? e.g. INSERT_EX_SQL = u"INSERT INTO myDatabsae (Id, ProgramName, ProgramDetail) VALUES (1, 'Test Characters ÜŞiçÇÖö', 'löşüIIğĞü');"
• Hellnar: If I unicode the query, I get such error: UnicodeEncodeError: 'ascii' codec can't encode characters in position 84-85: ordinal not in range(128)

Answer 1

Here is a possible solution:

The key is INSERT_EX_SQ.encode('your language encoder'). Try this instead:

con.execute_non_query(INSERT_EX_SQ.encode('your language encoder'))