How to Call Function in SQLAlchment Query Statement?

Question

Can I call function in SQLAlchemy like this?

db.session.query(User).update({'spotify_date':format_date(User.spotify_date)})
db.session.commit()

Here I tried to call function format_date() to format date in column spotify_date of relation table User.

Here is user models

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    spotify_id = db.Column(db.String(20), unique=False, nullable=True)
    spotify_token = db.Column(db.String(20), unique=False, nullable=True)
    spotify_date = db.Column(db.String(20), unique=False, nullable=True)

And function fomate_date() and call of function

from app import app, db, User

def format_date(date):
    """Format date to local format: dd-mm-yyyy
    
    Args:
        date (String): spotify_date
    
    Returns:
        string: date formatted in local

    Examples:
    >>> formate_date('2020-07-18')
    '18-07-2020'
    
    """
    dateList = date.split('-')
    dateList.reverse()

    return '-'.join(dateList)



db.session.query(User).update({'spotify_date':format_date(User.spotify_date)})
db.session.commit()

After that I got an exception as:

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with User.spotify_date has an attribute 'split'

Anyway, how I can correctly do this in SQLAlchemy query statement? Thanks

P.S: The database I am using is Postgresql; the function is the python function, not database function. Thanks.

What database do you use? In short: what you are trying to achieve is possible but it requires using SQL functions not Python. — Sergey Shubin
– Sergey Shubin, Commented Jul 23, 2020 at 10:28
@SergeyShubin, I have editted to add more detail on question. I am using database Postgresql, and the function is python function. The reason I decided to use this function in database query statement because I would like this task to be handled by database rather than by python script because there would thousand of record which would slow down the server. Thanks — Houy Narun
– Houy Narun, Commented Jul 23, 2020 at 10:39

Sergey Shubin · Accepted Answer · 2020-07-23 12:09:34Z

1

+50

If you want to make an UPDATE query which updates several rows you need to pass a single expression (value, function etc.) to this query. Python function won't do because your database doesn't know this particular function. So you need to convert your format_date function to the SQL expression and pass it to your query:

from app import db, User

format_date = (db.func.substr(User.spotify_date, 9, 2) + '-' +
               db.func.substr(User.spotify_date, 6, 2) + '-' +
               db.func.substr(User.spotify_date, 1, 4))
db.session.query(User).update({'spotify_date': format_date}, synchronize_session=False)
db.session.commit()

SQLAlchemy func function allow you to use internal database functions. format_date function looks like this in pure SQL:

SUBSTR("user"."spotify_date", 9, 2) || '-' || SUBSTR("user"."spotify_date", 6, 2) || '-' || SUBSTR("user"."spotify_date", 1, 4)

Also note that this function works for columns of String (VARCHAR) type. For DateTime (TIMESTAMP) type it would be different.

In order to apply Python function to the values from the table you need to get these values first. After that you have to apply the function and make an UPDATE for every single row. It would be rather long as you have noticed.

edited Jul 23, 2020 at 12:09

answered Jul 23, 2020 at 11:53

Sergey Shubin

3,2684 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Houy Narun Over a year ago

thank you very much, I have tested your answer and it worked just as expected.

Collectives™ on Stack Overflow

How to Call Function in SQLAlchment Query Statement?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related