无法使用 pymssql 将 Unicode 发送到 SQL Server [英] Unable to send Unicode to SQL Server using pymssql

问题描述

我在通过 pymssql 向 SQL Server 发送 unicode 时遇到问题:

I'm having issues sending unicode to SQL Server via pymssql:

In [1]:     import pymssql
            conn = pymssql.connect(host='hostname', user='me', password='password', database='db')
            cursor = conn.cursor()

In [2]:     s = u'Monsieur le Curé of the «Notre-Dame-de-Grâce» neighborhood'

In [3]:     s
Out [3]:    u'Monsieur le Cur\xe9 of the \xabNotre-Dame-de-Gr\xe2ce\xbb neighborhood'

In [4]:     cursor.execute("INSERT INTO MyTable VALUES(%s)", s.encode('utf-8'))
            cursor.execute("INSERT INTO MyTable VALUES(" + s.encode('utf-8') + "')")
            conn.commit()

两个 execute 语句在 SQL Server 端产生相同的乱码:

Both execute statements yield the same garbled text on the SQL Server side:

'Monsieur le Curé of the «Notre-Dame-de-Grâce» neighborhood'

也许我的编码方式或语法有问题.有人建议使用存储过程，但我希望不必走那条路.

Maybe something is wrong with the way I'm encoding, or with my syntax. Someone suggested a stored procedure, but I'm hoping not to have to go that route.

这个似乎是一个非常相似的问题，没有真正的回应.

This seems to be a very similar problem, with no real response.

推荐答案

以下代码示例已经过测试和验证，可以使用 pymssql 2.1.1 与 Python 2.7.5 和 Python 3.4.3 一起使用.

The following code samples have been tested and verified to work with both Python 2.7.5 and Python 3.4.3 using pymssql 2.1.1.

对于使用 UTF-8 编码保存的 Python 源文件:

For a Python source file saved with UTF-8 encoding:

# -*- coding: utf-8 -*-
import pymssql

cnxn = pymssql.connect(
    server='localhost',
    port='52865',
    user='sa',
    password='whatever',
    database='myDb')
crsr = cnxn.cursor()
crsr.execute("INSERT INTO MyTable (textcol) VALUES (%s)", (u'Monsieur le Curé of the «Notre-Dame-de-Grâce» neighborhood'))
cnxn.commit()
crsr.close()
cnxn.close()

对于使用ANSI"(Windows-1252)编码保存的 Python 源文件:

For a Python source file saved with "ANSI" (Windows-1252) encoding:

# -*- coding: windows-1252 -*-
import pymssql

cnxn = pymssql.connect(
    server='localhost',
    port='52865',
    user='sa',
    password='whatever',
    database='myDb')
crsr = cnxn.cursor()
crsr.execute("INSERT INTO MyTable (textcol) VALUES (%s)", (u'Monsieur le Curé of the «Notre-Dame-de-Grâce» neighborhood'))
cnxn.commit()
crsr.close()
cnxn.close()

请注意，两个示例之间的唯一区别是声明源文件编码的第一行.

Note that the only difference between the two samples is the very first line to declare the encoding of the source file.

明确地说，接收 INSERT 的表是:

To be clear, the table receiving the INSERT was:

CREATE TABLE [dbo].[MyTable](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [textcol] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

这篇关于无法使用 pymssql 将 Unicode 发送到 SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！