pyODBC and Unicode Problem

Question

Hey guys,

I'm working with pyODBC communicate with a MS SQL 2005 Express server. The table to which i'm trying to save the data consists of nvarchar columns.

query = u"INSERT INTO tblPersons (name, birthday, gender) VALUES('" query = query + name + u"', '" query = query + birthday + u"', '" query = query + gender + u"')"

cur.execute(query )

The variables name, birthrday and gende are read from an Excel file and they are Unicode strings. When I execute the query and either look at the table with SQL Server Management Studio or execute a query that fetches the data that was just inserted, all the data that was written in a non-English languages turn into question marks. The data that was written in English is preserved and appears in the table in the correct way. I tried adding CHARSET=UTF16 to my connection string, but had no luck with that. I can use UTF-8 which works fine but as a working convention, I need all the data saved in my DB to be UTF16.

Thanks!

Answer 1

It could be something related to the odbc driver that pyodbc is using. If that doesn't support unicode, you will probably have to encode the params yourself, like name.encode('utf-16')

Also, you should really, really use query parameters, instead of concatenating the sql string yourself, for example:

query = "INSERT INTO tblPersons (name, birthday, gender) VALUES (?, ?, ?)"
cur.execute(query, [name, birthday, gender])

I doubt that this will help with your unicode trouble, but it is a lot safer, and easier.

(and another unrelated tip: using pyodbc via sqlalchemy is a lot nicer, even if you use it just for simple queries and do not use the object-relational mapping stuff)

Answer 2

I'm so stupid.. The problem was with the server (had to change my collation to the language I needed)

Thanks!