libs.data.loader

lib/data/loader.py

View Source

  1"""
  2lib/data/loader.py
  3"""
  4
  5import logging
  6import re
  7from datetime import datetime
  8from importlib.resources import files
  9
 10import pandas as pd
 11
 12import libs.global_value as g
 13from libs.utils import dbutil
 14
 15
 16def load_query(filepath: str) -> str:
 17    """外部ファイルからクエリを読み込む
 18
 19    Args:
 20        filepath (str): 読み込むSQLファイルパス
 21
 22    Returns:
 23        str: SQL
 24    """
 25
 26    query_path = str(files("files.queries").joinpath(filepath))
 27    with open(query_path, "r", encoding="utf-8") as queryfile:
 28        sql = queryfile.read().strip()
 29
 30    return sql
 31
 32
 33def read_data(filepath: str) -> pd.DataFrame:
 34    """データベースからデータを取得する
 35
 36    Args:
 37        filepath (str): SQLファイルパス
 38
 39    Returns:
 40        pd.DataFrame: 集計結果
 41    """
 42
 43    g.params.update(starttime=g.params["starttime"].format("sql"))
 44    g.params.update(endtime=g.params["endtime"].format("sql"))
 45
 46    sql = query_modification(load_query(filepath))
 47    df = pd.read_sql(
 48        sql=sql,
 49        con=dbutil.get_connection(),
 50        params=g.params,
 51    )
 52
 53    # デバッグ用
 54    pd.set_option("display.max_rows", None)
 55    pd.set_option("display.max_columns", None)
 56    logging.trace("prm: %s", g.params)  # type: ignore
 57    logging.trace("sql: %s", named_query(sql))  # type: ignore
 58    logging.trace(df)  # type: ignore
 59
 60    return df
 61
 62
 63def query_modification(sql: str) -> str:
 64    """クエリをオプションの内容で修正する
 65
 66    Args:
 67        sql (str): 修正するクエリ
 68
 69    Returns:
 70        str: 修正後のクエリ
 71    """
 72
 73    if g.params.get("individual"):  # 個人集計
 74        sql = sql.replace("--[individual] ", "")
 75        # ゲスト関連フラグ
 76        if g.params.get("unregistered_replace"):
 77            sql = sql.replace("--[unregistered_replace] ", "")
 78            if g.params.get("guest_skip"):
 79                sql = sql.replace("--[guest_not_skip] ", "")
 80            else:
 81                sql = sql.replace("--[guest_skip] ", "")
 82        else:
 83            sql = sql.replace("--[unregistered_not_replace] ", "")
 84    else:  # チーム集計
 85        g.params.update(unregistered_replace=False)
 86        g.params.update(guest_skip=True)
 87        sql = sql.replace("--[team] ", "")
 88        if not g.params.get("friendly_fire"):
 89            sql = sql.replace("--[friendly_fire] ", "")
 90
 91    # 集約集計
 92    match g.params.get("collection"):
 93        case "daily":
 94            sql = sql.replace("--[collection_daily] ", "")
 95            sql = sql.replace("--[collection] ", "")
 96        case "monthly":
 97            sql = sql.replace("--[collection_monthly] ", "")
 98            sql = sql.replace("--[collection] ", "")
 99        case "yearly":
100            sql = sql.replace("--[collection_yearly] ", "")
101            sql = sql.replace("--[collection] ", "")
102        case "all":
103            sql = sql.replace("--[collection_all] ", "")
104            sql = sql.replace("--[collection] ", "")
105        case _:
106            sql = sql.replace("--[not_collection] ", "")
107
108    # コメント検索
109    if g.params.get("search_word") or g.params.get("group_length"):
110        sql = sql.replace("--[group_by] ", "")
111    else:
112        sql = sql.replace("--[not_group_by] ", "")
113
114    if g.params.get("search_word"):
115        sql = sql.replace("--[search_word] ", "")
116    else:
117        sql = sql.replace("--[not_search_word] ", "")
118
119    if g.params.get("group_length"):
120        sql = sql.replace("--[group_length] ", "")
121    else:
122        sql = sql.replace("--[not_group_length] ", "")
123        if g.params.get("search_word"):
124            sql = sql.replace("--[comment] ", "")
125        else:
126            sql = sql.replace("--[not_comment] ", "")
127
128    # 直近N検索用（全範囲取得してから絞る）
129    if g.params.get("target_count") != 0:
130        sql = sql.replace(
131            "and my.playtime between",
132            "-- and my.playtime between"
133        )
134
135    # プレイヤーリスト
136    if g.params.get("player_name"):
137        sql = sql.replace("--[player_name] ", "")
138        sql = sql.replace(
139            "<<player_list>>",
140            ":" + ", :".join(g.params["player_list"])
141        )
142    sql = sql.replace("<<guest_mark>>", g.cfg.setting.guest_mark)
143
144    # フラグの処理
145    match g.cfg.aggregate_unit:
146        case "M":
147            sql = sql.replace("<<collection>>", "substr(collection_daily, 1, 7) as 集計")
148            sql = sql.replace("<<group by>>", "group by 集計")
149        case "Y":
150            sql = sql.replace("<<collection>>", "substr(collection_daily, 1, 4) as 集計")
151            sql = sql.replace("<<group by>>", "group by 集計")
152        case "A":
153            sql = sql.replace("<<collection>>", "'合計' as 集計")
154            sql = sql.replace("<<group by>>", "")
155
156    if g.params.get("interval") is not None:
157        if g.params.get("interval") == 0:
158            sql = sql.replace("<<Calculation Formula>>", ":interval")
159        else:
160            sql = sql.replace(
161                "<<Calculation Formula>>",
162                "(row_number() over (order by total_count desc) - 1) / :interval"
163            )
164    if g.params.get("kind") is not None:
165        if g.params.get("kind") == "grandslam":
166            if g.cfg.undefined_word == 0:
167                sql = sql.replace("<<where_string>>", "and (words.type is null or words.type = 0)")
168            else:
169                sql = sql.replace("<<where_string>>", "and words.type = 0")
170        else:
171            match g.cfg.undefined_word:
172                case 1:
173                    sql = sql.replace("<<where_string>>", "and (words.type is null or words.type = 1)")
174                case 2:
175                    sql = sql.replace("<<where_string>>", "and (words.type is null or words.type = 2)")
176                case _:
177                    sql = sql.replace("<<where_string>>", "and (words.type = 1 or words.type = 2)")
178
179    # SQLコメント削除
180    sql = re.sub(r"^ *--\[.*$", "", sql, flags=re.MULTILINE)
181    sql = re.sub(r"\n+", "\n", sql, flags=re.MULTILINE)
182
183    return sql
184
185
186def named_query(query: str) -> str:
187    """クエリにパラメータをバインドして返す
188
189    Args:
190        query (str): SQL
191
192    Returns:
193        str: バインド済みSQL
194    """
195
196    for k, v in g.params.items():
197        if isinstance(v, datetime):
198            g.params[k] = v.strftime("%Y-%m-%d %H:%M:%S")
199
200    return re.sub(r":(\w+)", lambda m: repr(g.params.get(m.group(1), m.group(0))), query)

def load_query(filepath: str) -> str: View Source

17def load_query(filepath: str) -> str:
18    """外部ファイルからクエリを読み込む
19
20    Args:
21        filepath (str): 読み込むSQLファイルパス
22
23    Returns:
24        str: SQL
25    """
26
27    query_path = str(files("files.queries").joinpath(filepath))
28    with open(query_path, "r", encoding="utf-8") as queryfile:
29        sql = queryfile.read().strip()
30
31    return sql

外部ファイルからクエリを読み込む

Arguments:

filepath (str): 読み込むSQLファイルパス

Returns:

str: SQL

def read_data(filepath: str) -> pandas.core.frame.DataFrame: View Source

34def read_data(filepath: str) -> pd.DataFrame:
35    """データベースからデータを取得する
36
37    Args:
38        filepath (str): SQLファイルパス
39
40    Returns:
41        pd.DataFrame: 集計結果
42    """
43
44    g.params.update(starttime=g.params["starttime"].format("sql"))
45    g.params.update(endtime=g.params["endtime"].format("sql"))
46
47    sql = query_modification(load_query(filepath))
48    df = pd.read_sql(
49        sql=sql,
50        con=dbutil.get_connection(),
51        params=g.params,
52    )
53
54    # デバッグ用
55    pd.set_option("display.max_rows", None)
56    pd.set_option("display.max_columns", None)
57    logging.trace("prm: %s", g.params)  # type: ignore
58    logging.trace("sql: %s", named_query(sql))  # type: ignore
59    logging.trace(df)  # type: ignore
60
61    return df

データベースからデータを取得する

Arguments:

filepath (str): SQLファイルパス

Returns:

pd.DataFrame: 集計結果

def query_modification(sql: str) -> str: View Source

 64def query_modification(sql: str) -> str:
 65    """クエリをオプションの内容で修正する
 66
 67    Args:
 68        sql (str): 修正するクエリ
 69
 70    Returns:
 71        str: 修正後のクエリ
 72    """
 73
 74    if g.params.get("individual"):  # 個人集計
 75        sql = sql.replace("--[individual] ", "")
 76        # ゲスト関連フラグ
 77        if g.params.get("unregistered_replace"):
 78            sql = sql.replace("--[unregistered_replace] ", "")
 79            if g.params.get("guest_skip"):
 80                sql = sql.replace("--[guest_not_skip] ", "")
 81            else:
 82                sql = sql.replace("--[guest_skip] ", "")
 83        else:
 84            sql = sql.replace("--[unregistered_not_replace] ", "")
 85    else:  # チーム集計
 86        g.params.update(unregistered_replace=False)
 87        g.params.update(guest_skip=True)
 88        sql = sql.replace("--[team] ", "")
 89        if not g.params.get("friendly_fire"):
 90            sql = sql.replace("--[friendly_fire] ", "")
 91
 92    # 集約集計
 93    match g.params.get("collection"):
 94        case "daily":
 95            sql = sql.replace("--[collection_daily] ", "")
 96            sql = sql.replace("--[collection] ", "")
 97        case "monthly":
 98            sql = sql.replace("--[collection_monthly] ", "")
 99            sql = sql.replace("--[collection] ", "")
100        case "yearly":
101            sql = sql.replace("--[collection_yearly] ", "")
102            sql = sql.replace("--[collection] ", "")
103        case "all":
104            sql = sql.replace("--[collection_all] ", "")
105            sql = sql.replace("--[collection] ", "")
106        case _:
107            sql = sql.replace("--[not_collection] ", "")
108
109    # コメント検索
110    if g.params.get("search_word") or g.params.get("group_length"):
111        sql = sql.replace("--[group_by] ", "")
112    else:
113        sql = sql.replace("--[not_group_by] ", "")
114
115    if g.params.get("search_word"):
116        sql = sql.replace("--[search_word] ", "")
117    else:
118        sql = sql.replace("--[not_search_word] ", "")
119
120    if g.params.get("group_length"):
121        sql = sql.replace("--[group_length] ", "")
122    else:
123        sql = sql.replace("--[not_group_length] ", "")
124        if g.params.get("search_word"):
125            sql = sql.replace("--[comment] ", "")
126        else:
127            sql = sql.replace("--[not_comment] ", "")
128
129    # 直近N検索用（全範囲取得してから絞る）
130    if g.params.get("target_count") != 0:
131        sql = sql.replace(
132            "and my.playtime between",
133            "-- and my.playtime between"
134        )
135
136    # プレイヤーリスト
137    if g.params.get("player_name"):
138        sql = sql.replace("--[player_name] ", "")
139        sql = sql.replace(
140            "<<player_list>>",
141            ":" + ", :".join(g.params["player_list"])
142        )
143    sql = sql.replace("<<guest_mark>>", g.cfg.setting.guest_mark)
144
145    # フラグの処理
146    match g.cfg.aggregate_unit:
147        case "M":
148            sql = sql.replace("<<collection>>", "substr(collection_daily, 1, 7) as 集計")
149            sql = sql.replace("<<group by>>", "group by 集計")
150        case "Y":
151            sql = sql.replace("<<collection>>", "substr(collection_daily, 1, 4) as 集計")
152            sql = sql.replace("<<group by>>", "group by 集計")
153        case "A":
154            sql = sql.replace("<<collection>>", "'合計' as 集計")
155            sql = sql.replace("<<group by>>", "")
156
157    if g.params.get("interval") is not None:
158        if g.params.get("interval") == 0:
159            sql = sql.replace("<<Calculation Formula>>", ":interval")
160        else:
161            sql = sql.replace(
162                "<<Calculation Formula>>",
163                "(row_number() over (order by total_count desc) - 1) / :interval"
164            )
165    if g.params.get("kind") is not None:
166        if g.params.get("kind") == "grandslam":
167            if g.cfg.undefined_word == 0:
168                sql = sql.replace("<<where_string>>", "and (words.type is null or words.type = 0)")
169            else:
170                sql = sql.replace("<<where_string>>", "and words.type = 0")
171        else:
172            match g.cfg.undefined_word:
173                case 1:
174                    sql = sql.replace("<<where_string>>", "and (words.type is null or words.type = 1)")
175                case 2:
176                    sql = sql.replace("<<where_string>>", "and (words.type is null or words.type = 2)")
177                case _:
178                    sql = sql.replace("<<where_string>>", "and (words.type = 1 or words.type = 2)")
179
180    # SQLコメント削除
181    sql = re.sub(r"^ *--\[.*$", "", sql, flags=re.MULTILINE)
182    sql = re.sub(r"\n+", "\n", sql, flags=re.MULTILINE)
183
184    return sql

クエリをオプションの内容で修正する

Arguments:

sql (str): 修正するクエリ

Returns:

str: 修正後のクエリ

def named_query(query: str) -> str: View Source

187def named_query(query: str) -> str:
188    """クエリにパラメータをバインドして返す
189
190    Args:
191        query (str): SQL
192
193    Returns:
194        str: バインド済みSQL
195    """
196
197    for k, v in g.params.items():
198        if isinstance(v, datetime):
199            g.params[k] = v.strftime("%Y-%m-%d %H:%M:%S")
200
201    return re.sub(r":(\w+)", lambda m: repr(g.params.get(m.group(1), m.group(0))), query)

クエリにパラメータをバインドして返す

Arguments:

query (str): SQL

Returns:

str: バインド済みSQL