Unable to send Unicode to SQL Server using pymssql

问题:

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

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.

回答1:

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.

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

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.

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]

回答2:

Ended up using pypyodbc instead. Needed some assistance to connect, then used the doc recipe for executing statements:

import pypyodbc
conn = pypyodbc.connect("DRIVER={SQL Server};SERVER=my_server;UID=MyUserName;PWD=MyPassword;DATABASE=MyDB")
cur = conn.cursor
cur.execute('''INSERT INTO MyDB(rank,text,author) VALUES(?,?,?)''', (1, u'Monsieur le Curé of the «Notre-Dame-de-Grâce» neighborhood', 'Charles S.'))
cur.commit()

回答3:

Ran into the same issue with pymssql and did not want to switch to pypyodbc

For me, there was no issue in removing any accents seeing that I only needed first names as a reference. So this solution may not be for everyone.

import unicodedate
firstName = u'René'
firstName = unicodedata.normalize('NFKD', firstName).encode('ascii', 'ignore')
print firstName 

回答4:

Here is something which worked for me:

# -*- coding: utf-8 -*-
import pymssql
conn = pymssql.connect(host='hostname', user='me', password='password', database='db')
cursor = conn.cursor()

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

cursor.execute("INSERT INTO MyTable(col1) VALUES(%s)", s.encode('latin-1', "ignore"))
conn.commit()
cursor.close()
conn.close()

MyTable is of collation: Latin1_General_CI_AS and the column col1 in it is of type varchar(MAX)

My environment is: SQL Server 2008 & Python 2.7.10