Calling SQL Functions from Blaze

Question

In particular, I would like to name the Postgres levenshtein function. I would like to write a blaze query to return words similar to the word "similar", that is, the equivalent:

 select word from wordtable where levenshtein(word, 'similar') < 3; 

In Blaze, it should look something like this:

 db.wordtable.word[levenshtein(db.wordtable.word, 'similar') < 3] 

but levenshtein not defined in any module I import on the python side. Where / how do I get a levenshtein definition for use in Blaze expressions on the Python side?

---

I found the sqlalchemy.sql.func package that provides Python descriptors for SQL functions for use with SqlAlchemy, but they do not work in Blaze expressions. Is there an equivalent Blaze package, or how can I use sqlalchemy.sql.func.levenshtein inside a Blaze expression?

Answer 1

Blaze is only Frontend; and restructures and then queries the SQL Backend, which collects data from your database.

From the doku doc :

What operations work with SQL databases? Most table operations, but not all. SQLAlchemy translation is a high priority. Failures include an array of operations such as slicing and dot products that don't make sense in SQL. In addition, some operations, such as accessing a time date, are not yet supported through SQLAlchemy. Finally, some databases, such as SQLite, have limited support for common math functions, such as sin.

Blaze is designed to forget about the backend and have the same syntax for all backends. Therefore, it supports only general operations. In my opinion, there is no way to specify sqlalchemy or postgresql functions to use blaze, so I think this is not possible now.

... BUT Blaze has version 0.10, this is a kind of beta with an active contribution last year. I am sure that this will be realized in some time. You can always track changes in notes.

Supplement: To further explain UDF (user-defined functions) in SQL and work with them in blaze, compare the link provided by beldaz (very similar to this question).

Answer 2

PyPi is your friend. Search there finds the python-Levenshtein package. If you are on Windows and want a precompiled version, use the Christoph Gohlke wheel (its assemblies are safe, reliable and correct; using numpy uses MKL libraries, so they are fast too!).

Answer 3

If you want to see these words and perform a few simple steps on them, you can try loading the filtered data into Blaze from a Postgres request.

 from blaze import data import sqlalchemy as sa engine = sa.create_engine('postgresql://...') result = engine.execute('''select word from wordtable where levenshtein(word, 'similar') < 3;''') rows = result.fetchall() wordtable = data(rows) # Now you may work with wordtable as a blaze table wordtable[wordtable.word.like('a*')] # all words starting with 'a'