Proper Way to Insert Strings to a SQLAlchemy Unicode Column

Question

I have a SQLAlchemy model with a Unicode column. I sometimes insert unicode values to it (u'Value'), but also sometimes insert ASCII strings. What is the best way to go about this? When I insert ASCII strings with special characters I get this warning:

SAWarning: Unicode type received non-unicode bind param value ...

How do I avoid this? What is the proper way to insert my different types of strings?

Wien

Answer 1

Thre are several options:

1. Disable all SQLAlchemy warnings with warnings.simplefilter('ignore', sqlalchemy.exc.SAWarning).
2. Disable only this warning with more specific filter spec by module and lineno or message, e.g. warnings.filterwarnings('ignore', '^Unicode type received non-unicode bind param value', sqlalchemy.exc.SAWarning).
3. Use String(convert_unicode=True) instead of Unicode type.
4. Rethink the problem and change your code to use unicode even for ASCII strings.

Western

You attemping delete problem instead of solving it.About number 3: Official documentation of sqlalchemy say use Unicode type but you say use String(convert_unicode=True) , i have same problem , but i don't think sqlalchemy solve with disable warning because it has clean code not dirty. – Yand

Answer 2

You should define your table class and constructor as such:

class User(declarative_base()):
    _tablename__ = 'user'
    name = Column(Unicode(200, convert_unicode=False))
    textfield = Column(UnicodeText(convert_unicode=False))

user = Table('user', MetaData(),
Column('name', Unicode(200, convert_unicode=False), nullable=False),
Column('textfield', UnicodeText(convert_unicode=False), nullable=False),
)

Of course, you should not forget to attach URI+"charset=utf8" for create_engine function

Yand