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

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

• scharfmn: Implicit in the good connection advice mentioned above is an idea I didn't try: take the unicode examples from pypyodbc (if you must) and try pyodbc (if you haven't).

Answer 2

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 

• user568021: I was almost done writing my own question about this, but just before publishing I tried your solution. In fact I only used "unicodedata.normalize('NFKD', somestring.decode())" and everything works. Sir, you are awesome!

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

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]

Answer 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

• EFraim: So you essentially destroyed all the characters which don't fit into latin-1. Not exactly what the question had in mind