[Windows] include Math extension in SQlite

[BigStone]

BPO	30952
Nosy	@pfmoore, @vstinner, @tjguk, @zware, @zooba, @erlend-aasland
Superseder	bpo-42686: include built-in Math functions in SQLite to 3.35.0 of march 2021

^{Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.}

Show more details

GitHub fields:

assignee = None
closed_at = <Date 2021-08-11.20:48:35.945>
created_at = <Date 2017-07-17.20:40:19.402>
labels = ['extension-modules', 'type-feature', '3.7', 'OS-windows']
title = '[Windows] include Math extension in SQlite'
updated_at = <Date 2021-08-11.20:48:35.943>
user = 'https://bugs.python.org/BigStone'

bugs.python.org fields:

activity = <Date 2021-08-11.20:48:35.943>
actor = 'erlendaasland'
assignee = 'none'
closed = True
closed_date = <Date 2021-08-11.20:48:35.945>
closer = 'erlendaasland'
components = ['Extension Modules', 'Windows']
creation = <Date 2017-07-17.20:40:19.402>
creator = 'Big Stone'
dependencies = []
files = []
hgrepos = []
issue_num = 30952
keywords = []
message_count = 11.0
messages = ['298555', '305445', '305447', '305660', '305681', '305688', '305920', '305937', '305974', '305988', '399416']
nosy_count = 8.0
nosy_names = ['paul.moore', 'peter.otten', 'vstinner', 'tim.golden', 'zach.ware', 'steve.dower', 'Big Stone', 'erlendaasland']
pr_nums = []
priority = 'normal'
resolution = 'duplicate'
stage = 'resolved'
status = 'closed'
superseder = '42686'
type = 'enhancement'
url = 'https://bugs.python.org/issue30952'
versions = ['Python 3.7']

[BigStone]

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)))

[zware]

Would you like to supply a PR?

[BigStone]

Sorry, I'm not skilled enough to do PR on Python core.

[vstinner]

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?

[BigStone]

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.

[peterotten]

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,)

[BigStone]

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.

[zware]

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 :)

[vstinner]

"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 ;-)

[BigStone]

Please apologize. I indeed went off-topic. I can understand few person find this request usefull, and over-react to defend it.

Sorry again.

[erlend-aasland]

This was fixed in bpo-42686. Closing as duplicate.