Invalid ASCII character in cursor.execute

[ldo...@gmail.com]

Hi everyone,

I'm setting up redshift connected to EC2. I succesfully connected pyodbc to the redshift database, and it's now time for a Hello world request.

import pyodbc
def initialize_odbc():
    cnxn = pyodbc.connect('Driver={Amazon Redshift (x64)}; Server=xxxx; Database=name; UID=ldocao; PWD=myspaswd; Port=1111')
    return cnxn

def create_test_table(cursor):
    try:
        cursor.execute('drop table test_table')
    except:
        pass
    cursor.execute("CREATE TABLE test (name varchar(10))")
    cursor.commit()


connection = initialize_odbc()
cursor = connection.cursor()
create_test_table(cursor)

This raises an Error:

      7     except:
      8         pass
----> 9     cursor.execute("CREATE TABLE test (name varchar(10))")
     10     cursor.commit()
     11 
Error: ('HY000', '[HY000] [Amazon][RedShift ODBC] (30) Error occurred while trying to execute a query: ERROR:  Only ASCII characters are allowed in an identifier.Invalid ASCII char: ef bf bd \n (30) (SQLExecDirectW)')

I suspect an encoding problem. Any idea ?

I'm using:

Python 3.5.0

pyodbc==3.0.10

Linux version 4.1.10-17.31.amzn1.x86_64 (mockbuild@gobi-build-60008) (gcc version 4.8.3 20140911 (Red Hat 4.8.3-9) (GCC) )  on EC2

[peterva...@gmail.com]

A bit late probably but that is indeed an encoding issue.  You can try to change:

DriverManagerEncoding=UTF-32

into: 

DriverManagerEncoding=UTF-16

Preferably in the config file: /opt/amazon/redshiftodbc/lib/64/amazon.redshiftodbc.ini 

Works for me on Amazon Linux and the following packages

unixODBC-2.2.14-14.7.amzn1.x86_64

unixODBC-devel-2.2.14-14.7.amzn1.x86_64

AmazonRedshiftODBC-64bit-1.2.7-1.x86_64 (Not in repo's but RPM available from Amazon)

and from pip

pyodbc==3.0.10

[Michael Kleehammer]

Please try version 4.0.2 which has configurable encodings.  See the updated Wiki and/or docs for more details.