Skip to content

Add create_window_function() to sqlite3.Connection #79097

Closed
#20903
Closed
Add create_window_function() to sqlite3.Connection#79097
#20903
Assignees
berkerpeksag
Labels
3.8 (EOL)end of lifeextension-modulesC modules in the Modules dirtype-featureA feature request or enhancement

Description

@BigStone
BigStone
mannequin
opened on Oct 6, 2018
BPO 34916
Nosy @ronaldoussoren, @berkerpeksag, @coleifer, @erlend-aasland
PRs
  • gh-79097: Add support for aggregate window functions in sqlite3 #20903
    		•

    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

    1. BigStone commented on Oct 6, 2018

      @BigStone
      BigStonemannequin
      on Oct 6, 2018
      MannequinAuthor

      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 ?

    2. ronaldoussoren commented on Oct 9, 2018

      @ronaldoussoren
      ronaldoussoren
      on Oct 9, 2018
      Contributor

      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.

    3. ronaldoussoren
      added
      type-bugAn unexpected behavior, bug, or error
      on Oct 9, 2018

    4. BigStone commented on Oct 9, 2018

      @BigStone
      BigStonemannequin
      on Oct 9, 2018
      MannequinAuthor

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

    5. ronaldoussoren commented on Oct 9, 2018

      @ronaldoussoren
      ronaldoussoren
      on Oct 9, 2018
      Contributor

      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.

    6. ScottStevens commented on Dec 17, 2018

      @ScottStevens
      ScottStevensmannequin
      on Dec 17, 2018
      Mannequin

      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

    7. berkerpeksag commented on May 11, 2019

      @berkerpeksag
      berkerpeksag
      on May 11, 2019
      Member

      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.

    8. berkerpeksag
      added
      3.8 (EOL)end of life
      extension-modulesC modules in the Modules dir
      on May 11, 2019
    9. berkerpeksag
      changed the title [-]include sqlite-3.25+ (with window functions)[/-] [+]Add create_window_function() to sqlite3.Connection[/+] on May 11, 2019
    10. berkerpeksag
      self-assigned this
      on May 11, 2019
    11. berkerpeksag
      added
      type-featureA feature request or enhancement
      and removed
      type-bugAn unexpected behavior, bug, or error
      on May 11, 2019

    12. coleifer commented on Aug 12, 2019

      @coleifer
      coleifermannequin
      on Aug 12, 2019
      Mannequin

      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

    13. erlend-aasland commented on Jun 8, 2020

      @erlend-aasland
      erlend-aasland
      on Jun 8, 2020
      Contributor

      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.

    14. ezio-melotti
      transferred this issue fromon Apr 10, 2022
    15. JelleZijlstra
      closed this as completedin #20903on Apr 12, 2022
    16. JelleZijlstra
      added a commit that references this issue on Apr 12, 2022

      gh-79097: Add support for aggregate window functions in sqlite3 (GH-2…

      9ebcece
    17. erlend-aasland
      added a commit that references this issue on Apr 12, 2022

      Sync with main bco. pythongh-79097

      e15c087
    Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

    Metadata

    Metadata

    Assignees

    Labels

    3.8 (EOL)end of lifeextension-modulesC modules in the Modules dirtype-featureA feature request or enhancement

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions