Encoding Error

[Racer_x]

Hi Good People,

How many times do I get to say I'm new to Python, man much to learn: I consistently am getting the following error:

Error:
'latin-1' codec can't encode character '\u2019' in position 281: ordinal not in range(256)

I've tried adding the following to no avail. Any help would be appreciated,

connection.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
connection.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
connection.setencoding(encoding='utf-8')

Here's my code:

import pandas as pd
import pyodbc
import xport.v56

# Trusted Connection to Named Instance
try:
    connection = pyodbc.connect('DRIVER={SQL Server};SERVER=MyServer\MS_SQLEXPRESS;DATABASE=CheckData;Trusted_Connection=yes;')
except Exception as err:
    print('Exception occured while trying to create a connection ', err)
else:
    try:

        sql_query = pd.read_sql_query('''EXEC sp_Rtest''', connection) 
        # here, the 'connection' is the variable that contains your database connection information from step 2

        df = pd.DataFrame(sql_query)
        ds = xport.Dataset(df, name='DATA', label='Wonderful data')


        ds = ds.rename(columns={k: k.upper()[:8] for k in ds})

        # Other SAS metadata can be set on the columns themselves.
        for k, v in ds.items():
            v.label = k.title()
         
            if v.dtype == 'object':
                v.format = '$CHAR20.'
            else:
                v.format = '10.2'

        # Libraries can have multiple datasets.
        library = xport.Library({'DATA': ds})
        

        with open('c:/users/cneal/exasssmple.xpt', 'wb') as f:
            xport.v56.dump(library, f)


    except Exception as err:
       print('Exception occured while fethcing records ', err)
    else:
        print('')
    finally:
      print('')
finally:
    connection.close()

[Gribouillis]

• Which version of Python are you using? Use only Python 3.
  
• Please post the entire error traceback so that we can see exactly which part of the code throws the error.
  

Observe that the following code style is terrible

try:
     a lot of python statements here
except Exception as err:
    print("We had an error but we are doing everything we can to hide where it comes from", err)
else:
    print("")

Don't hide exceptions in Python!

[deanhystad]

As the error message clearly states, you cannot encode \u2019 as an 8 bit character. But that doesn't provide a lot of help.
I don't think your database contains U2019, so the question is why does pyodbc think it does, and your error message doesn't give any information about that.

It is good that you print the error message, but you should also print the traceback, at least while you are doing development. I would just remove the try/except until I got things working, but it you want to do try/except you can print the traceback like this.

import traceback
...
    except Exception as err:
       print('Exception occured while fethcing records ')
       print(traceback.format_exc())

And what are you running: Which OS? Which database? Which version of Python?

[Racer_x]

Thanks!! Hope this helps,

Error:
Traceback (most recent call last):
  File "c:\Users\myuser\Test Files\tempCodeRunnerFile.py", line 38, in <module>
    xport.v56.dump(library, f)
  File "C:\Users\myuser-d\AppData\Local\Programs\Python\Python310\lib\site-packages\xport\v56.py", line 932, in dump
    fp.write(dumps(library))
  File "C:\Users\myuser-d\AppData\Local\Programs\Python\Python310\lib\site-packages\xport\v56.py", line 951, in dumps
    return bytes(Library(library))
  File "C:\Users\myuser-d\AppData\Local\Programs\Python\Python310\lib\site-packages\xport\v56.py", line 727, in __bytes__
    b'members': b''.join(bytes(Member(member)) for member in self.values()),
  File "C:\Users\myuser-d\AppData\Local\Programs\Python\Python310\lib\site-packages\xport\v56.py", line 727, in <genexpr>
    b'members': b''.join(bytes(Member(member)) for member in self.values()),
  File "C:\Users\myuser-d\AppData\Local\Programs\Python\Python310\lib\site-packages\xport\v56.py", line 639, in __bytes__
    observations = bytes(Observations.from_dataset(self))
  File "C:\Users\myuser-d\AppData\Local\Programs\Python\Python310\lib\site-packages\xport\v56.py", line 546, in __bytes__
    observations = b''.join(self.to_bytes())
  File "C:\Users\myuser-d\AppData\Local\Programs\Python\Python310\lib\site-packages\xport\v56.py", line 539, in to_bytes
    yield struct.pack(fmt, *g)
  File "C:\Users\myuser-d\AppData\Local\Programs\Python\Python310\lib\site-packages\xport\v56.py", line 538, in <genexpr>
    g = (f(v) for f, v in zip(converters, t))
  File "C:\Users\myuser-d\AppData\Local\Programs\Python\Python310\lib\site-packages\xport\v56.py", line 521, in encoder
    return s.encode('ISO-8859-1').ljust(length)
UnicodeEncodeError: 'latin-1' codec can't encode character '\u2019' in position 281: ordinal not in range(256)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "c:\Users\myuser\Desktop\Test Files\tempCodeRunnerFile.py", line 42, in <module>
    traceback.TracebackException(err)
TypeError: TracebackException.__init__() missing 2 required positional arguments: 'exc_value' and 'exc_traceback'

[deanhystad]

Crashing in the exception handler is an interesting way to get a traceback. You might want to fix that. You should also post your modified code.

And where's the info about your OS, database and Python version? I'm thinking that the byte order for your platform does not match the byte order of your database.

[Gribouillis]

I don't know xport but it seems that the dataset contains invalid data for this file format. Try to build a minimal dataset that has the same error and check the format's specification.

[Racer_x]

Windown 10,
SQL Server 18,
Python 3.10.0

[deanhystad]

XPORT is an old binary format. A database containing XPORT format data is not going to have any Unicode characters. That's what leads me to think there is a fundamental problem like byte order or word size. Or it could be that the data is not in XPORT format at all. Seems odd that there would be XPORT format data in a SQL Server database.