pyodbc is returning binary data in char field

pyodbc with driver “iSeries Access ODBC Driver” is returning binary output, Ex:

original data in the table: B06300

what it returns: b’xc2xf0xf6xf3xf0xf0@@@@@@@@@@@@@@@@@@@@@@@@’

My code:

import pyodbc
connection = pyodbc.connect(
    driver='{iSeries Access ODBC Driver}',
    System='**************',
    port = '****',
    uid='**************',
    pwd='**************')

df = pd.read_sql_query("SELECT * FROM schema_name.F4108")

I tried putting add_output_converter and encoder to connection but didn’t work

Answer

I suspect the problem is that the data is defined on the server as CCSID 65535, which means to not translate the data.

Using the TRANSLATE=1 connection string keyword will cause the ODBC driver to translate the data using the current EBCDIC CCSID for the job. Other ODBC connection properties can be found here: https://www.ibm.com/docs/en/i/7.1?topic=keywords-connection-string-conversion-properties