Title: Add create_window_function() to sqlite3.Connection
Type: enhancement Stage: needs patch
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, ghaering, ronaldoussoren
Priority: normal Keywords:

Created on 2018-10-06 17:29 by Big Stone, last changed 2019-08-12 23:18 by coleifer.

Messages (7)
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:
Date User Action Args
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