prevent (raise exceptions) on bytestring values for non-byte types

[Jonathan Vanasco]

I am finally at the tail end of migrating my largest (and hopefully last) Python2 application to Python3.

An issue that has popped up a lot during this transition, is when a py3 bytestring gets submitted into SqlAlchemy.

When that happens, it looks like SqlAlchemy just passes the value into psycopg2, which wraps it in an object, and I get a psycopg exception that bubbles up to SqlAlchemy:

>    sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) operator does not exist: character varying = bytea
>    LINE 3: WHERE foo = '\x626337323133...
>    HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
>    ....
>    WHERE foo = %(foo)s
>    LIMIT %(param_1)s]
>    [parameters: {'foo': <psycopg2.extensions.Binary object at 0x10fe99060>, 'param_1': 1}]
>    (Background on this error at: http://sqlalche.me/e/13/f405)

Is there an easy way to catch this in SQLAlchemy *before* sending this to the driver and executing it on the server?  I'd like to ensure I'm catching everything I should, and nothing is working just by-chance.  

[Mike Bayer]

The Unicode datatype will emit a warning if you pass it a bytestring.  you can use that instead of String, or use a datatype with your own assertions based on https://docs.sqlalchemy.org/en/14/core/custom_types.html#coercing-encoded-strings-to-unicode

--

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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f70bf020-d120-46fb-96d1-d5509ff9b3c3n%40googlegroups.com.

[Jonathan Vanasco]

Mike, thanks for replying but go back to vacation.

Anyone else: I am thinking more about an event that can be used to catch, perhaps log, all bytes that go in.  I only use a few column classes that expect bytestrings, but many that do not.  I've gotten every known bug so far, but I'd like to make sure I'm not just lucky.

[Simon King]

I can think of a couple of options:

1. Create a TypeDecorator for String and Text columns that raises an
error if it sees a bytestring. This will only flag the error when the
session is flushed.
2. Listen for mapper_configured events, iterate over the mapper
properties and add an "AttributeEvents.set" listener for each one.
This should flag the error when a bytestring is assigned to a mapped
attribute.

Hope that helps,

Simon

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/d6f8d50c-9465-41bc-a293-d8295c35ecc1n%40googlegroups.com.

[Jonathan Vanasco]

The second option looks perfect. Will try it!

Thank you so much, Simon!