如何使用sqlite后端为python blaze提供用户定义的功能? [英] How to provide user defined function for python blaze with sqlite backend?
问题描述
我使用以下命令连接到Blaze中的sqlite数据库
df = bz.Data("sqlite:///<mydatabase>)
一切正常，但我不知道如何在与df的交互中提供用户定义的功能.
我在df中有一个称为IP的列，它是包含IP地址的文本.我还具有toSubnet(x，y)函数，该函数以文本格式获取IP地址(x)并返回其/y子网.例如:
I connect to sqlite database in Blaze using
df = bz.Data("sqlite:///<mydatabase>)
everything works fine but I do not know how to provide user-defined functions in my interaction with df.
I have a column called IP in df which is text containing IP addresses. I also have a function toSubnet (x, y) which takes in an IP address (x) in text format and return its /y subnet. For example:
out = toSubnet('1.1.1.1',24)
out
1.1.1.0/24
现在，如果我想将所有IP映射到它们的/14子网，请使用:
Now if I want to map all IPs to their /14 subnets, I use:
df.IP.map(lambda x:toSubnet(x,14),'string')
当后端为CSV时可以使用.但是使用sqlite后端，我得到NotImplementedError.
怎么了?
Which works when the backend is CSV. But with sqlite backend I get NotImplementedError.
What's wrong here?
推荐答案
NB:这不会告诉您如何准确地执行您想要的操作，但是提供了原因的解释无效，下一步可能是使其与SQLite一起使用.
NB: This doesn't tell you how to do exactly what you want, but it provides an explanation of why it doesn't work and a possible next step to get this to work with SQLite.
您遇到的问题是，很难对任意SQL数据库有效地执行任意Python代码.
The problem you're running into is that it is very difficult to efficiently execute arbitrary Python code against an arbitrary SQL database.
Blaze使用SQLAlchemy来获取用户代码并将其最好地转换为SQL，我认为这没有办法.
Blaze takes user code and translates it to SQL as best it can, using SQLAlchemy, which I don't think has a way to do this.
由于几乎每个数据库都有处理用户定义函数(UDF)的不同方法，因此构建允许以下操作的API的工作量很大:
Since nearly every database has a different way of dealing with user-defined functions (UDFs), it's quite a lot of work to build an API that allows the following:
- 用Python定义函数的用户
- 将纯Python函数转换为数据库固有的UDF.
也就是说，SQLite的Python接口提供了一种注册可以在SQL语句中执行的Python函数的方法:
That said, the Python interface to SQLite has a way to register Python functions that can be executed in a SQL statement:
https://docs.python.org/2/library/sqlite3.html#sqlite3.Connection.create_function
目前还没有一种使用SQL后端使用Blaze表示UDF的方法，尽管可以将其实现为允许用户通过基础数据库的db API注册函数的新表达式类型.
There currently isn't a way to express a UDF with Blaze using the SQL backend, though this could be implemented as new expression type that allows a user to register a function via the underlying database's db API.
这篇关于如何使用sqlite后端为python blaze提供用户定义的功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
