classification
Title: [Windows] include Math extension in SQlite
Type: enhancement Stage:
Components: Extension Modules, Windows Versions: Python 3.7
process
Status: open Resolution:
Dependencies: Superseder:
Assigned To: Nosy List: Big Stone, paul.moore, peter.otten, steve.dower, tim.golden, vstinner, zach.ware
Priority: normal Keywords:

Created on 2017-07-17 20:40 by Big Stone, last changed 2017-11-09 22:00 by Big Stone.

Messages (10)
msg298555 - (view) Author: Big Stone (Big Stone) Date: 2017-07-17 20:40
I would be interested in having SQLite shipped with the math extension in python-3.7.
 
The log / exponential function are important for some sql use case like 
 exp(sum(log(x)))
msg305445 - (view) Author: Zachary Ware (zach.ware) * (Python committer) Date: 2017-11-02 19:12
Would you like to supply a PR?
msg305447 - (view) Author: Big Stone (Big Stone) Date: 2017-11-02 20:43
Sorry, I'm not skilled enough to do PR on Python core.
msg305660 - (view) Author: STINNER Victor (vstinner) * (Python committer) Date: 2017-11-06 17:09
> I would be interested in having SQLite shipped with the math extension

Python uses the SQLite library installed on the system, at least on Linux. To load an extension, I found:
https://sqlite.org/loadext.html
https://sqlite.org/c3ref/load_extension.html

How am I supposed the math extension? The CLI ".load" command requires the name or even the full path to a shared library (".so" file on Linux).

I see that the issue was tagged as Windows. Are you talking about the SQLite bundled with Python installer on Windows?
msg305681 - (view) Author: Big Stone (Big Stone) Date: 2017-11-06 21:29
Hi Victor,

I would like to use math functions, specifically "exp(sum(log(x)))", on the Sqlite motor shipped embedded with Python on Windows.

It's an important corner case of what I miss in Sqlite motor, that forced me to (wait to) rely on SQLServer (or other big iron).

In my other hobby of Python-in-Scientific-cursus of France, having not a fully enabled SQLite (in its mathematic abilities) is also a problem.

Maybe there are other more important outstanding issues about embedded-SQlite integration with Python, but that one is hard to workaround.
msg305688 - (view) Author: Peter Otten (peter.otten) * Date: 2017-11-06 23:27
A possible workaround is to use create_function():

>>> import sqlite3, math
>>> db = sqlite3.connect(":memory:")
>>> db.execute("select sin(?);", (math.pi,)).fetchone()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: no such function: sin
>>> db.create_function("sin", 1, math.sin)
>>> db.execute("select sin(?);", (math.pi,)).fetchone()
(1.2246467991473532e-16,)
msg305920 - (view) Author: Big Stone (Big Stone) Date: 2017-11-08 23:00
I know you can do create_function(): https://raw.githubusercontent.com/stonebig/sqlite_bro/master/docs/sqlite_bro.GIF

But the performance is crappy at best because of string conversion in the API.

If you want SQlite because of its performance, recent version starts to do multi-threading, and/or fear to leave other langages a stupid sizeable performance advantage, then you may perhaps don't leave the situation as it is.

Didn't Victor spoke about the need to 2X CPython performance recently, for it to stay in the game ? 

Look at latest Intel cpu shooting for quadcore, Iphone 8 being 6 cores, latest stable nodejs gaining a big speed bump, ... etc ... etc... and the need of efficiency to keep the planet cool enough.
msg305937 - (view) Author: Zachary Ware (zach.ware) * (Python committer) Date: 2017-11-09 05:15
I, for one, don't have the time to work on this, especially since I don't regularly use the sqlite3 module.  If this is something you want, submit a PR.  Lack of skill is not a legitimate reason not to do so; the devguide provides instructions on the mechanics of submitting a patch and any questions you have along the way can be asked here, on the core-mentorship mailing list, or on the #python-dev channel on freenode.

It's much easier to justify 2 minutes to answer a question, even if that 2 minutes happens 10 times, than 2 hours to add a feature that I won't use and will be expected to maintain :)
msg305974 - (view) Author: STINNER Victor (vstinner) * (Python committer) Date: 2017-11-09 17:45
"If you want SQlite because of its performance, recent version starts to do multi-threading, and/or fear to leave other langages a stupid sizeable performance advantage, then you may perhaps don't leave the situation as it is."

I'm not sure of what you are asking here. Is it related to the math module? If not, you might open a different issue.


"then you may perhaps don't leave the situation as it is"

Reminder: Python is developed by volunteers working in their free time. It's not a product for which you pay a commercial support.

"Look at latest Intel cpu shooting for quadcore, Iphone 8 being 6 cores, latest stable nodejs gaining a big speed bump, ... etc ... etc... and the need of efficiency to keep the planet cool enough."

Ok, now you are really off-topic.

*I* don't know anything about sqlite. That's why I asked how this feature is supposed to be implemented.

If nobody proposes a pull request or explain how to implement it, this issue is not going to make progress ;-)
msg305988 - (view) Author: Big Stone (Big Stone) Date: 2017-11-09 22:00
Please apologize. I indeed went off-topic. I can understand few person find this request usefull, and over-react to defend it.

Sorry again.
History
Date User Action Args
2017-11-09 22:00:33Big Stonesetstatus: pending -> open

messages: + msg305988
2017-11-09 17:54:51serhiy.storchakasetstatus: open -> pending
2017-11-09 17:45:45vstinnersetmessages: + msg305974
2017-11-09 05:15:32zach.waresetmessages: + msg305937
2017-11-08 23:00:55Big Stonesetmessages: + msg305920
2017-11-06 23:27:12peter.ottensetnosy: + peter.otten
messages: + msg305688
2017-11-06 22:03:18vstinnersettitle: include Math extension in SQlite -> [Windows] include Math extension in SQlite
2017-11-06 21:29:37Big Stonesetmessages: + msg305681
2017-11-06 17:09:05vstinnersetnosy: + vstinner
messages: + msg305660
2017-11-02 20:43:25Big Stonesetmessages: + msg305447
2017-11-02 19:12:23zach.waresetmessages: + msg305445
2017-07-17 21:32:51r.david.murraysetnosy: + paul.moore, tim.golden, zach.ware, steve.dower
components: + Windows
2017-07-17 20:40:19Big Stonecreate