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.

score 3 · Answer 1 · edited May 23 '17 at 12:08

3

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

edited May 23 '17 at 12:08

Community

1
1

answered Apr 15 '13 at 22:03

scharfmn

3,561
7
38
53

Implicit in the [good connection advice](http://stackoverflow.com/a/16025030/1599229) 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). – scharfmn Apr 24 '14 at 21:19

score 2 · Answer 2 · answered Jan 30 '15 at 21:25

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

answered Jan 30 '15 at 21:25

Dengar

423
5
7

1

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! – user568021 Aug 09 '16 at 07:30

Gord Thompson · Accepted Answer · 2015-11-27T03:01:31.143

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]

score 0 · Answer 4 · answered Nov 25 '15 at 06:42

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

So you essentially destroyed all the characters which don't fit into latin-1. Not exactly what the question had in mind — EFraim, Dec 09 '17 at 22:27

Unable to send Unicode to SQL Server using pymssql

4 Answers4

Linked