How to use python functions in sql select statement

Question

I have one function(token_sort_ratio()) in python which compares two strings and gives the output as percentage match between them. I want to use that function in sql query which i'm running in python using pyodbc. But i cannot achieve it. Can anyone help me doing that.? Here is my code

cursor = conn.cursor() 
sql_query ="""select """+fuzz.token_sort_ratio("""[MAT DESC],MAT_GROUP_DESC""")+""" as percentage_match ,[MAT DESC] MAT_GROUP_DESC from Sample_data """  
cursor.execute(sql_query)  
rows = cursor.fetchall()

Is there any work around to use python user defined function on columns of a table? — Er_Faizan
– Er_Faizan, Commented Apr 26, 2017 at 8:26
Welcome to Stack Overflow. You can either read back all the records from the database and work on them in Python or you can write your percentage match in SQL from the start. I would recommend rewriting it in SQL as it will be more efficient. You cannot ask SQL to run your Python commands. — Dr Rob Lang
– Dr Rob Lang, Commented Apr 26, 2017 at 9:48
Rob, What can be the best way to read all records in python and apply that function? I'm new to python I don't know much about it. — Er_Faizan
– Er_Faizan, Commented Apr 26, 2017 at 10:22

nimdil · Accepted Answer · 2017-04-26 13:14:55Z

1

You can't. You can't even through IronPython compiled into DLL Assembly and such tricks. Your best bet is to rewrite it into T-SQL or - maybe a bit easier stuff - into one of the .NET languages and load them through .dll to SQL Server. I think the F# is perhaps most similar to Python but on the other hand the documentation on writing .dll for SQL Server is mostly for C#/VB.net so consider writing more complex stuff in C#

answered Apr 26, 2017 at 13:14

nimdil

1,38111 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

How to use python functions in sql select statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related