How do I encode Unicode strings using pyodbc to save to a SAS dataset?

Question

I'm using Python to read and write SAS datasets, using pyodbc and the SAS ODBC drivers. I can load the data perfectly well, but when I save the data, using something like:

cursor.execute('insert into dataset.test VALUES (?)', u'testing')

... I get a pyodbc.Error: ('HY004', '[HY004] [Microsoft][ODBC Driver Manager] SQL data type out of range (0) (SQLBindParameter)') error.

The problem seems to be the fact I'm passing a unicode string; what do I need to do to handle this?

Answer 1

Do you know what character encoding your database is expecting? If so, you could try encoding your Unicode string before executing the query. So if your database is expecting utf-8 strings, you could try something like:

encoding = 'utf-8' # or latin1 or cp1252 or something
s = u'testing'.encode(encoding)
cursor.execute('insert into dataset.test VALUES (?)', s)

Answer 2

You say "I can load the data perfectly well" ... does this mean that you can load data that contains characters that are NOT in the native encoding used on your platform (presumably cp1252 on Windows, but please confirm)? What is the SAS datatype of the first column of your SAS dataset?

This article in the SAS docs purports to show how you can find out the encoding used in a SAS dataset.

Encoding is mentioned in the SAS ODBC documentation. However you don't appear to be using SAS ODBC (i.e. SAS-language script accessing non-SAS data).