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?

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ğĞü');" — Kimvais
– Kimvais, Commented Jan 25, 2011 at 8:19
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) — Hellnar
– Hellnar, Commented Jan 25, 2011 at 8:23

David G · Accepted Answer · 2012-11-25 13:17:44Z

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'))

edited Nov 25, 2012 at 13:17

David G

97.6k41 gold badges173 silver badges258 bronze badges

answered Apr 5, 2011 at 10:03

Tieying Zhang

111 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

python to mssql encoding problem

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related