pyODBC 和 Unicode
我正在使用 pyODBC 与 MS SQL 2005 Express 服务器进行通信。 我尝试保存数据的表由 nvarchar 列组成。
query = u"INSERT INTO tblPersons (name, birthday, gender) VALUES('"
query = query + name + u"', '"
query = query + birthday + u"', '"
query = query + gender + u"')"
cur.execute(query)
变量 name、birthrday 和 gende 是从 Excel 文件中读取的，它们是 Unicode 字符串。 当我执行查询并使用 SQL Server Management Studio 查看表或执行获取刚刚插入的数据的查询时，所有以非英语语言编写的数据都会变成问号。用英语编写的数据将被保留并以正确的方式显示在表中。 我尝试将 CHARSET=UTF16 添加到我的连接字符串中，但没有成功。 我可以使用 UTF-8，它工作得很好，但作为工作约定，我需要将数据库中保存的所有数据都设为 UTF16。
谢谢！
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!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这可能与 pyodbc 正在使用的 odbc 驱动程序有关。如果不支持 unicode，您可能必须自己对参数进行编码，例如
name.encode('utf-16')另外，您应该真的、真的使用查询参数，而不是连接你自己的sql字符串，例如：
我怀疑这会帮助你解决unicode的麻烦，但它更安全，更容易。
（还有另一个不相关的提示：通过 sqlalchemy 使用 pyodbc 更好，即使您仅将其用于简单查询并且不使用对象关系映射内容）
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:
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!