Register a custom function with SQLAlchemy's sql.func

Question

How to apply a customized filter in sqlalchemy? I have tried @hybrid_property and @hybrid_method. However, they are giving errors. Here is my code:

class Product(db.Model):
    __tablename__ = 'products'
    id = db.Column(db.Integer, primary_key=True)
    owner_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    keywords  = db.Column(db.String(64)) # keywords for ease of browsing
    details = db.Column(db.Text)
    removed = db.Column(db.Boolean, default=False)
    @hybrid_method
    def toKeywordLevenshteinDistance(self, keywords):
        """
        :param user input product keywords
        :return Levenshtein(user_input_keywords, product_keywords)
        """
        return fuzz.partial_ratio(self.keywords, keywords)
    
    @toKeywordLevenshteinDistance.expression
    def toKeywordLevenshteinDistance(cls, keywords):
        return func.fuzz.partial_ratio(cls.keywords, keywords)

>>> p=similarProducts('jrjk')

Here is the output:

File "C:\Users\AllWatt\App\flask\myStoreEnv\lib\site-packages\sqlalchemy\engine\default.py", 
line 608, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such function: levenshtein
    [SQL: SELECT products.id AS products_id, products.owner_id AS products_owner_id, 
    products.keywords AS products_keywords, products.details AS products_details,
    products.removed AS products_removed, products."when" AS products_when FROM products
    WHERE levenshtein(products.keywords, ?) > ?] [parameters: ('jrjk', 20)]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

(sqlite3.OperationalError) no such function: levenshtein I guess that means it's still trying to call your function inside sqllite — Nick.Mc
– Nick.Mc, Commented Jun 27, 2021 at 3:00
@Z4-tier, it's a function which retrieve all the products with Levenshtein_distance(Product.keywords , given _keywords)>20 — AllWatt
– AllWatt, Commented Jun 27, 2021 at 3:39

snakecharmerb · Accepted Answer · 2021-06-27 08:28:42Z

You can use an event listener to register custom functions with sqlite on connection. Here's a simple example:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import sqlalchemy as sa

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite://'

db = SQLAlchemy(app)


def doubleit(word):
    return word * 2


@sa.event.listens_for(db.engine, 'connect')
def on_connect(dbapi_connection, connection_record):
    dbapi_connection.create_function('doubleit', 1, doubleit)


@app.route('/<word>')
def word_doubler(word):
    result = db.session.query(sa.func.doubleit(word))
    return result.scalar()


if __name__ == '__main__':
    app.run(debug=True)

Related docs:

I'm using an application factory for my Flask app, so I needed to move the def on_connect... block inside my create_app() function, and I also needed to define it within an app context like with app.app_context(): @sa.event...

Collectives™ on Stack Overflow

Register a custom function with SQLAlchemy's sql.func

1 Answer 1

1 Comment

Linked

Hot Network Questions