pyODBC and Unicode

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 wri开发者_运维技巧tten 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!

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)

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

Thanks!