Unable to send Unicode to SQL Server using pymssql

Question

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.

Answer 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]