Best way to force values to uppercase in sqlalchemy field

Question

I am pretty new to python and sqlalchemy and would like to ask for an advice.

What would be the best way to force uppercase values in a SqlAlchemy field when the record is inserted/updated? Should I use events for that? Here I am using flask-sqlalchemy version, but I believe it is something similar to SQLAlchemy.

from flask.ext.sqlalchemy import SQLAlchemy
db = SQLAlchemy()

class Item(db.Model):
    # I need to ensure the code column converts values to uppercase automatically
    code = db.Column(db.String(30), primary_key=True)
    name = db.Column(db.String(250), nullable=False)
    ...

Thanks for the advice

You mean, you have user input which should be forced to be uppercase? — MaxNoe, Dec 16 '15 at 21:08
Yes, exactly, but I would like to have a custom type and use it. I dug a bit and found a TypeDecorator, and it almost working with the exception that the values are converted only on db.session.commit(). Please see my answer. Thanks — Darius, Dec 16 '15 at 22:20

ShadowRanger · Accepted Answer · 2020-12-22T15:13:23.887

29

Validators can do this fairly easily:

from sqlalchemy.orm import validates

class Item(db.Model):
    # I need to ensure the code column converts values to uppercase automatically
    code = db.Column(db.String(30), primary_key=True)
    name = db.Column(db.String(250), nullable=False)
    
    @validates('code', 'name')
    def convert_upper(self, key, value):
        return value.upper()

A similar approach can be taken with descriptors or hybrids, though it's not quite as easy to apply the same mutations to multiple columns at once. Hybrids do provide some additional benefits, in that using them in queries can delegate work to the DBMS instead of performing the work on the Python side (though for this particular case, doing the work on the Python side doesn't really cost you anything).

To be clear, the validator is applied at the model level; you must be constructing Item classes to benefit from it (for example, using Item.__table__ to directly manipulate the table will bypass the model's validator).

edited Dec 22 '20 at 15:13

answered Dec 16 '15 at 21:21

ShadowRanger

143,180
12
188
271

I would consider throwing a warning, when applying this function. The user should now that he is supposed to enter uppercase strings. This also minimizes the surprises a user can get. – MaxNoe Dec 17 '15 at 02:59
@MaxNoe: Depends on what you view the purpose of this to be. Seamlessly converting to uppercase as a design feature to save the user's `Shift`/`Caps Lock` key some wear is not unreasonable. – ShadowRanger Dec 17 '15 at 03:07
@MaxNoe: Your suggested edit was wrong in any event (`str.isupper` will return `False` if no cased characters are in the string, so it would have warned if you passed it `"123"`, even though no transformation was needed). While this is using the validation feature, validation can be used for seamless transformation too; convenience features that actually get used are a good thing; warning if they get used is silly. – ShadowRanger Dec 17 '15 at 03:11
Ah, ok. I only tried`'TEST1234'.isupper()` which returns True. I, as a user, would want to know if the data I enter is somehow converted. – MaxNoe Dec 17 '15 at 03:15
Didn't worked for me. ORM just ignores it – mr_bulrathi Dec 22 '20 at 10:54
1

@mr_bulrathi: You're going to need to be more specific on how it's failing if you want a fix. [The docs](https://docs.sqlalchemy.org/en/14/orm/mapped_attributes.html#simple-validators) say it should still work, though there are specific cases (e.g. server-generated primary keys) where it doesn't get applied as of SQLAlchemy 1.0.0. – ShadowRanger Dec 22 '20 at 11:59
I've described my issue in more detail - https://stackoverflow.com/questions/65408938/sqlalchemy-save-value-as-uppercase-automatically – mr_bulrathi Dec 22 '20 at 12:42
1

@mr_bulrathi: Seems like you bypassed the model object by using `User.__table__` directly, instead of constructing a `User`; `User.__table__` usage doesn't actually see `User`. Without `User` being directly involved, you bypassed validators as well. Don't do that. :-) (I'd appreciate if you retracted the downvote) – ShadowRanger Dec 22 '20 at 13:11
sure, but so writes: You last voted on this answer 3 hours ago. Your vote is now locked in unless this answer is edited. :( – mr_bulrathi Dec 22 '20 at 14:25
@mr_bulrathi: Just added a clarifying note for anyone who has the same problem you have, so it should be unlocked now. :-) – ShadowRanger Dec 22 '20 at 15:13

score 2 · Answer 2 · answered Dec 16 '15 at 21:11

Have a look at events

YOu can easily add a listener for a save/update and just uppercase the string:

def uppercase_code(target, value, oldvalue, initiator):
    return value.upper()

# setup listener on Item.code attribute, instructing
# it to use the return value
listen(Item.code, 'set', uppercase_code, retval=True)

score 0 · Answer 3 · answered Jan 12 '21 at 13:36

The best way is to use descriptors

In you case you can do:

from flask.ext.sqlalchemy import SQLAlchemy
db = SQLAlchemy()

class Item(db.Model):
    # I need to ensure the code column converts values to uppercase automatically
    _code = db.Column("code", db.String(30), primary_key=True)
    _name = db.Column("name", db.String(250), nullable=False)

    @property
    def name(self):
        return self._name

    @name.setter
    def name(self, name):
        self._name = name.upper()

    @property
    def code(self):
        return self._code

    @code.setter
    def name(self, code):
        self._code = code.upper()

Best way to force values to uppercase in sqlalchemy field

3 Answers3

Linked

Related