UTF-8 Error when trying to insert a string into a PostgresQL BYTEA column with SQLAlchemy

Question

I have a column in a PostgresQL table of type BYTEA. The model class defines the column as a LargeBinary field, which the documentation says "The Binary type generates BLOB or BYTEA when tables are created, and also converts incoming values using the Binary callable provided by each DB-API."

I have a Python string which I would like to insert into this table.

The Python string is:

'\x83\x8a\x13,\x96G\xfd9ae\xc2\xaa\xc3syn\xd1\x94b\x1cq\xfa\xeby$\xf8\xfe\xfe\xc5\xb1\xf5\xb5Q\xaf\xc3i\xe3\xe4\x02+\x00ke\xf5\x9c\xcbA8\x8c\x89\x13\x00\x07T\xeb3\xbcp\x1b\xff\xd0\x00I\xb9'

The relevant snippet of my SQLAlchemy code is:

    migrate_engine.execute(
        """
        UPDATE table
        SET x=%(x)s
        WHERE id=%(id)s
        """,
        x=the_string_above,
        id='1')

I am getting the error:

sqlalchemy.exc.DataError: (DataError) invalid byte sequence for encoding "UTF8": 0x83
'\n            UPDATE table\n            SET x=%(x)s\n            WHERE id=%(id)s\n            ' {'x': '\x83\x8a\x13,\x96G\xfd9ae\xc2\xaa\xc3syn\xd1\x94b\x1cq\xfa\xeby$\xf8\xfe\xfe\xc5\xb1\xf5\xb5Q\xaf\xc3i\xe3\xe4\x02+\x00ke\xf5\x9c\xcbA8\x8c\x89\x13\x00\x07T\xeb3\xbcp\x1b\xff\xd0\x00I\xb9', 'id': '1',}

If I go into the pgadmin3 console and enter the UPDATE command directly, the update works fine. The error is clearly from SQLAlchemy. The string is a valid Python2 string. The column has type BYTEA. The query works without SQLAlchemy. Can anyone see why Python thinks this byte string is in UTF-8?

Answer 1

Try wrapping the data in a buffer:

migrate_engine.execute(
    """
    UPDATE table
    SET x=%(x)s
    WHERE id=%(id)s
    """,
    x=buffer(the_string_above),
    id='1')