Repository navigation
Add create_window_function() to sqlite3.Connection #79097
Description
| BPO | 34916 |
|---|---|
| Nosy | @ronaldoussoren, @berkerpeksag, @coleifer, @erlend-aasland |
| PRs |
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 = 'https://github.com/berkerpeksag'
closed_at = None
created_at = <Date 2018-10-06.17:29:26.073>
labels = ['extension-modules', 'type-feature', '3.8']
title = 'Add create_window_function() to sqlite3.Connection'
updated_at = <Date 2020-06-15.21:48:40.567>
user = 'https://bugs.python.org/BigStone'bugs.python.org fields:
activity = <Date 2020-06-15.21:48:40.567>
actor = 'erlendaasland'
assignee = 'berker.peksag'
closed = False
closed_date = None
closer = None
components = ['Extension Modules']
creation = <Date 2018-10-06.17:29:26.073>
creator = 'Big Stone'
dependencies = []
files = []
hgrepos = []
issue_num = 34916
keywords = ['patch']
message_count = 8.0
messages = ['327253', '327387', '327429', '327433', '331948', '342206', '349506', '370967']
nosy_count = 7.0
nosy_names = ['ghaering', 'ronaldoussoren', 'berker.peksag', 'Big Stone', 'Scott Stevens', 'coleifer', 'erlendaasland']
pr_nums = ['20903']
priority = 'normal'
resolution = None
stage = 'patch review'
status = 'open'
superseder = None
type = 'enhancement'
url = 'https://bugs.python.org/issue34916'
versions = ['Python 3.8']
Activity
BigStone commentedon Oct 6, 2018
sqlite-3.25 now includes window functions, something very important in modern SQL.
https://www.sqlite.org/windowfunctions.html
Could it be included in next Python maintenance release ?
ronaldoussoren commentedon Oct 9, 2018
To completely do this requires two things:
Upgrade SQLite included in a number of binary distributions
Update the sqlite extension to allow creating custom window functions in Python
The latter is definitely something that cannot be done in a maintenance release. The former is up to the release managers.
BigStone commentedon Oct 9, 2018
hum. On Windows at least, just drop-in replace the sqlite3.dll per the new sqlite-3.25 dll and the window functions just works.
==> I don't see the need for your latter point, at least on Windows.
checked with sqlite_bro on Python-3.6.7rc1 with example:
CREATE TABLE t2(a, b);
INSERT INTO t2 VALUES('a', 'one'),
('a', 'two'),
('a', 'three'),
('b', 'four'),
('c', 'five'),
('c', 'six');
SELECT a AS a,
row_number() OVER win AS row_number,
rank() OVER win AS rank,
dense_rank() OVER win AS dense_rank,
percent_rank() OVER win AS percent_rank,
cume_dist() OVER win AS cume_dist
FROM t2
WINDOW win AS (ORDER BY a);
ronaldoussoren commentedon Oct 9, 2018
My latter points makes it possible to define custom windowing functions in Python, similar to how it is already possible to define other SQLite functions in Python.
ScottStevens commentedon Dec 17, 2018
Due to the SQLite "Magellan" bug, I'd suggest this be 3.26+.
Details: https://blade.tencent.com/magellan/index_en.html
See also: https://bugs.python.org/issue35360
berkerpeksag commentedon May 11, 2019
bpo-35360 will cover updating our Windows and macOS installers. I'm retargeting this issue to add a create_window_function() method to the Connection object. I already have a WIP branch and am going to submit a PR later this weekend.
[-]include sqlite-3.25+ (with window functions)[/-][+]Add create_window_function() to sqlite3.Connection[/+]coleifer commentedon Aug 12, 2019
I've implemented this in a fork / standalone packaging of the Python 3.x sqlite3 module. You can find the relevant portions (and a couple unrelated changes) in this commit:
coleifer/pysqlite3@91c2001
erlend-aasland commentedon Jun 8, 2020
Berker, are you still working on this? If not, I've got a proof-of-concept version of this w/unit tests ready for review. If you'd like to have a look at it, I can create a draft PR.
gh-79097: Add support for aggregate window functions in sqlite3 (GH-2…
Sync with main bco. pythongh-79097