How to Assign Blob Field?

[Guy Zehavi]

When I use sqlalchemy using mysql-connector-python to read unicode from a blob I am getting:

Error closing cursor
UnicodeDecodeError: 'utf-8' codec can't decode byte 0x9a in position 3: invalid start byte

Now, I can solve this by doing , connect_args={'use_pure': True} to the create_engine. But that smells bad and I wanted to know the 'proper' way to do this; notably how do I put a Blob using SqlAlchemy? I have:

my_blob = Column(BLOB, nullable=True)

Does anyone know how to assign bytes the the above column? I assume my_object.my_blob = the_bytes however that does not work. Specifically, my Blob is of type `bytes` (I get it from a protobuff being serialized). Is there anything else I need to do?

[Guy Zehavi]

Just as a note, the full stack trace is:

Error closing cursor
UnicodeDecodeError: 'utf-8' codec can

't decode byte 0x9a in position 3: invalid start byte

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.5/dist-packages/mysql/connector/cursor_cext.py", line 272, in execute
    self._handle_result(result)
  File "/usr/local/lib/python3.5/dist-packages/mysql/connector/cursor_cext.py", line 163, in _handle_result
    self._handle_resultset()
  File "/usr/local/lib/python3.5/dist-packages/mysql/connector/cursor_cext.py", line 651, in _handle_resultset
    self._rows = self._cnx.get_rows()[0]
  File "/usr/local/lib/python3.5/dist-packages/mysql/connector/connection_cext.py", line 276, in get_rows
    row = self._cmysql.fetch_row()
SystemError: <built-in method fetch_row of _mysql_connector.MySQL object at 0x3a1bce0> returned a result with an error set

[Mike Bayer]

On Tue, Jun 18, 2019, at 9:14 AM, Guy Zehavi wrote:

When I use sqlalchemy using mysql-connector-python to read unicode from a blob I am getting:

Hi there -

While this issue may be impacted if you are using an old version of mysql-connector-python or of SQLAlchemy, assuming you are on the latest version of everything, then we have to conclude this driver has issues in this area.

Please try a driver such as mysqlclient or pymysql.   mysql-connector-python is known to have lots of ongoing issues particularly in the area of unicode translation;  the previous two drivers will accept BLOB values as bytes without issue.

Error closing cursor
UnicodeDecodeError: 'utf-8' codec can't decode byte 0x9a in position 3: invalid start byte

Now, I can solve this by doing , connect_args={'use_pure': True} to the create_engine. But that smells bad and I wanted to know the 'proper' way to do this; notably how do I put a Blob using SqlAlchemy? I have:

my_blob = Column(BLOB, nullable=True)

Does anyone know how to assign bytes the the above column? I assume my_object.my_blob = the_bytes however that does not work. Specifically, my Blob is of type `bytes` (I get it from a protobuff being serialized). Is there anything else I need to do?

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b5982571-7098-4bdd-8670-5102dd955ee1%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.