log() in sqlite3

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

log() in sqlite3

Peng Yu
Hi,

I see that many math functions (like log()) are not in sqlite3. It
seems that SQL standard doesn't have them.

https://www.sqlite.org/lang_corefunc.html

But since sqlite3 contains non-standard functions anyway. Would it be
considered to add those functions?

Given the current version of sqlite3, is the only choice of computing
log() to get the data into another language (e.g., python) and compute
the log over there? Thanks.

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: log() in sqlite3

David Raymond
The core functions of the library are fairly unlikely to get expanded upon at this point, so it's up to the application using the SQLite library to expand on them.

You can load an extension which has those functions. Or the C API gives you means to add your own functions.

For Python, the built in sqlite3 module has the create_function() method for a connection which will register a function.
https://docs.python.org/3.8/library/sqlite3.html#sqlite3.Connection.create_function

Once you register the function you can use it right in your sql text like any other function,
"update tbl set field2 = log(field1);"

And you don't need to do the more tedious:
a) Get original data with statement 1
b) Do calculations in the outside program
c) Use the result in statement 2


-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Peng Yu
Sent: Friday, January 31, 2020 12:42 PM
To: SQLite mailing list <[hidden email]>
Subject: [sqlite] log() in sqlite3

Hi,

I see that many math functions (like log()) are not in sqlite3. It
seems that SQL standard doesn't have them.

https://www.sqlite.org/lang_corefunc.html

But since sqlite3 contains non-standard functions anyway. Would it be
considered to add those functions?

Given the current version of sqlite3, is the only choice of computing
log() to get the data into another language (e.g., python) and compute
the log over there? Thanks.

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: log() in sqlite3

Chris Brody
The log function does seem to be supported by extension-functions.c which
is available from here: https://www.sqlite.org/contrib

On Fri, Jan 31, 2020 at 1:30 PM David Raymond <[hidden email]>
wrote:

> The core functions of the library are fairly unlikely to get expanded upon
> at this point, so it's up to the application using the SQLite library to
> expand on them.
>
> You can load an extension which has those functions. Or the C API gives
> you means to add your own functions.
>
> For Python, the built in sqlite3 module has the create_function() method
> for a connection which will register a function.
>
> https://docs.python.org/3.8/library/sqlite3.html#sqlite3.Connection.create_function
>
> Once you register the function you can use it right in your sql text like
> any other function,
> "update tbl set field2 = log(field1);"
>
> And you don't need to do the more tedious:
> a) Get original data with statement 1
> b) Do calculations in the outside program
> c) Use the result in statement 2
>
>
> -----Original Message-----
> From: sqlite-users <[hidden email]> On
> Behalf Of Peng Yu
> Sent: Friday, January 31, 2020 12:42 PM
> To: SQLite mailing list <[hidden email]>
> Subject: [sqlite] log() in sqlite3
>
> Hi,
>
> I see that many math functions (like log()) are not in sqlite3. It
> seems that SQL standard doesn't have them.
>
> https://www.sqlite.org/lang_corefunc.html
>
> But since sqlite3 contains non-standard functions anyway. Would it be
> considered to add those functions?
>
> Given the current version of sqlite3, is the only choice of computing
> log() to get the data into another language (e.g., python) and compute
> the log over there? Thanks.
>
> --
> Regards,
> Peng
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: log() in sqlite3

Peng Yu
How to use extension-functions.c? It means that I have to compile it?
How to use it with python?

For python, create_function should be a better solution? Thanks.

On 1/31/20, Chris Brody <[hidden email]> wrote:

> The log function does seem to be supported by extension-functions.c which
> is available from here: https://www.sqlite.org/contrib
>
> On Fri, Jan 31, 2020 at 1:30 PM David Raymond <[hidden email]>
> wrote:
>
>> The core functions of the library are fairly unlikely to get expanded upon
>> at this point, so it's up to the application using the SQLite library to
>> expand on them.
>>
>> You can load an extension which has those functions. Or the C API gives
>> you means to add your own functions.
>>
>> For Python, the built in sqlite3 module has the create_function() method
>> for a connection which will register a function.
>>
>> https://docs.python.org/3.8/library/sqlite3.html#sqlite3.Connection.create_function
>>
>> Once you register the function you can use it right in your sql text like
>> any other function,
>> "update tbl set field2 = log(field1);"
>>
>> And you don't need to do the more tedious:
>> a) Get original data with statement 1
>> b) Do calculations in the outside program
>> c) Use the result in statement 2
>>
>>
>> -----Original Message-----
>> From: sqlite-users <[hidden email]> On
>> Behalf Of Peng Yu
>> Sent: Friday, January 31, 2020 12:42 PM
>> To: SQLite mailing list <[hidden email]>
>> Subject: [sqlite] log() in sqlite3
>>
>> Hi,
>>
>> I see that many math functions (like log()) are not in sqlite3. It
>> seems that SQL standard doesn't have them.
>>
>> https://www.sqlite.org/lang_corefunc.html
>>
>> But since sqlite3 contains non-standard functions anyway. Would it be
>> considered to add those functions?
>>
>> Given the current version of sqlite3, is the only choice of computing
>> log() to get the data into another language (e.g., python) and compute
>> the log over there? Thanks.
>>
>> --
>> Regards,
>> Peng
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: log() in sqlite3

Keith Medcalf

On Friday, 31 January, 2020 17:59, Peng Yu <[hidden email]> wrote:

>How to use extension-functions.c? It means that I have to compile it?

Yes.  Either as a loadable extension or as core builtin functions extending the amalgamation.

>How to use it with python?

db.load_extension(<file name containing the compiled extension load library>) for each connection db into which you want to load the extension.

>For python, create_function should be a better solution? Thanks.

Mayhaps yes, mayhaps no.  

Depends on your definition of "better".  If you mean "simpler" then the answer is yes.  If you mean many orders of magnitude slower than the C version, then the answer is also yes.

>>> import sqlite3
>>> db=sqlite3.connect(':memory:')
>>> def log(*arg):
...     from math import log
...     return log(*arg)
...
>>> db.create_function('log', -1, log)
>>> import math
>>> db.execute('select log(1000)').fetchone()
(6.907755278982137,)
>>> math.log(1000)
6.907755278982137
>>> db.execute('select log(1000,10)').fetchone()
(2.9999999999999996,)
>>> math.log(1000,10)
2.9999999999999996
>>> db.execute('select log(1000,2)').fetchone()
(9.965784284662087,)
>>> math.log(1000,2)
9.965784284662087

sqlite3 also does not let you set a function as deterministic, while APSW does, functions defined in python using the sqlite3 wrapper are somewhat limited in where you can use them.  APSW also lets you write virtual tables and vfs's in python, should you wish.  I don't think sqlite3 can do that.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users