This issue tracker has been migrated to GitHub, and is currently read-only.
For more information, see the GitHub FAQs in the Python's Developer Guide.

Title: Add create_window_function() to sqlite3.Connection
Type: enhancement Stage: patch review
Components: Extension Modules Versions: Python 3.8
Status: open Resolution:
Dependencies: Superseder:
Assigned To: berker.peksag Nosy List: Big Stone, Scott Stevens, berker.peksag, coleifer, erlendaasland, ghaering, ronaldoussoren
Priority: normal Keywords: patch

Created on 2018-10-06 17:29 by Big Stone, last changed 2022-04-11 14:59 by admin.

Pull Requests
URL Status Linked Edit
PR 20903 open erlendaasland, 2020-06-15 21:48
Messages (8)
msg327253 - (view) Author: Big Stone (Big Stone) Date: 2018-10-06 17:29
sqlite-3.25 now includes window functions, something very important in modern SQL.

Could it be included in next Python maintenance release ?
msg327387 - (view) Author: Ronald Oussoren (ronaldoussoren) * (Python committer) Date: 2018-10-09 09:40
To completely do this requires two things:

1) Upgrade SQLite included in a number of binary distributions

2) 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.
msg327429 - (view) Author: Big Stone (Big Stone) Date: 2018-10-09 18:19
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
msg327433 - (view) Author: Ronald Oussoren (ronaldoussoren) * (Python committer) Date: 2018-10-09 19:43
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.
msg331948 - (view) Author: Scott Stevens (Scott Stevens) Date: 2018-12-17 07:10
Due to the SQLite "Magellan" bug, I'd suggest this be 3.26+.

See also:
msg342206 - (view) Author: Berker Peksag (berker.peksag) * (Python committer) Date: 2019-05-11 16:34
Issue 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.
msg349506 - (view) Author: Charles (coleifer) * Date: 2019-08-12 23:18
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:
msg370967 - (view) Author: Erlend E. Aasland (erlendaasland) * (Python triager) Date: 2020-06-08 08:46
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.
Date User Action Args
2022-04-11 14:59:06adminsetgithub: 79097
2021-05-05 19:32:17erlendaaslandlinkissue40617 superseder
2020-06-15 21:48:40erlendaaslandsetkeywords: + patch
stage: needs patch -> patch review
pull_requests: + pull_request20086
2020-06-08 08:46:30erlendaaslandsetnosy: + erlendaasland
messages: + msg370967
2019-08-12 23:18:09coleifersetnosy: + coleifer
messages: + msg349506
2019-05-11 16:34:49berker.peksagsetassignee: berker.peksag
type: behavior -> enhancement

components: + Extension Modules
title: include sqlite-3.25+ (with window functions) -> Add create_window_function() to sqlite3.Connection
nosy: + berker.peksag
versions: + Python 3.8
messages: + msg342206
stage: needs patch
2018-12-17 07:10:35Scott Stevenssetnosy: + Scott Stevens
messages: + msg331948
2018-10-09 19:43:04ronaldoussorensetmessages: + msg327433
2018-10-09 18:19:14Big Stonesetmessages: + msg327429
2018-10-09 09:40:41ronaldoussorensetnosy: + ghaering, ronaldoussoren
type: behavior
messages: + msg327387
2018-10-06 17:29:26Big Stonecreate