[PYTHON/PYSQLITE] Connection 클래스 : create_aggregate 메소드를 사용해 사용자 정의 집계 함수 사용하기

icodebroker PYSQLITE 2024-06-18

■ Connection 클래스의 create_aggregate 메소드를 사용해 사용자 정의 집계 함수를 정의하는 방법을 보여준다.

▶ 예제 코드 (PY)


import sqlite3

class Average:
    def __init__(self):
        self.summary = 0
        self.count   = 0

    def step(self, value):
        self.summary += value
        self.count   += 1

    def finalize(self):
        return self.summary / self.count

connection = sqlite3.connect(":memory:")

connection.create_aggregate("average", 1, Average)

cursor = connection.cursor()

cursor.execute("CREATE TABLE PHONE_BOOK (NAME TEXT, AGE INTEGER);")

tuple1 = (("Tom", 24), ("Derick", 30), ("Peter", 53), ("Jane", 29))

cursor.executemany("INSERT INTO PHONE_BOOK VALUES (?, ?);", tuple1)

cursor.execute("SELECT AVERAGE(AGE) FROM PHONE_BOOK;")

for rowTuple in cursor:
    print(rowTuple)

"""
(34.0,)
"""

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

import sqlite3

class Average:

def __init__(self):

self.summary = 0

self.count = 0

def step(self, value):

self.summary += value

self.count += 1

def finalize(self):

return self.summary / self.count

connection = sqlite3.connect(":memory:")

connection.create_aggregate("average", 1, Average)

cursor = connection.cursor()

cursor.execute("CREATE TABLE PHONE_BOOK (NAME TEXT, AGE INTEGER);")

tuple1 = (("Tom", 24), ("Derick", 30), ("Peter", 53), ("Jane", 29))

cursor.executemany("INSERT INTO PHONE_BOOK VALUES (?, ?);", tuple1)

cursor.execute("SELECT AVERAGE(AGE) FROM PHONE_BOOK;")

for rowTuple in cursor:

print(rowTuple)

"""

(34.0,)

"""