CODEWITHSUNDEEP
pythonsqlalchemy
Houy Narun
Houy Narun

Reputation: 1725

How to Call Function in SQLAlchment Query Statement?

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.

Upvotes: 2

Views: 1197

Answers (1)

Sergey Shubin
Sergey Shubin

Reputation: 3257

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.

Upvotes: 1

Related Questions