2af8a3b644
commit 948d94b6fe7472624f664d7a4cb8e3f0899af77f Author: Julian Freeman <freemanjulian372@gmail.com> Date: Tue Apr 2 10:21:08 2024 -0400 v1.3.0
63 lines
1.8 KiB
Python
63 lines
1.8 KiB
Python
# coding: utf8
|
|
import os
|
|
import sqlite3
|
|
from pathlib import Path
|
|
|
|
|
|
__all__ = ["iqh_init", "iqh_create_conn", "iqh_close_conn",
|
|
"iqh_create_table", "iqh_select_id2group",
|
|
"iqh_select_group2id", "iqh_insert_data"]
|
|
|
|
|
|
def iqh_init() -> Path:
|
|
base_path = Path(os.path.expandvars("%appdata%"), "JnPrograms", "IdQuery")
|
|
if not base_path.exists():
|
|
base_path.mkdir(parents=True, exist_ok=True)
|
|
|
|
return base_path
|
|
|
|
|
|
def iqh_create_conn(base_path: Path, db_name: str) -> sqlite3.Connection:
|
|
db_path = Path(base_path, db_name)
|
|
conn = sqlite3.connect(db_path)
|
|
return conn
|
|
|
|
|
|
def iqh_close_conn(conn: sqlite3.Connection):
|
|
conn.close()
|
|
|
|
|
|
def iqh_create_table(conn: sqlite3.Connection):
|
|
cursor = conn.cursor()
|
|
cursor.execute("""CREATE TABLE IF NOT EXISTS idrepo (
|
|
id_index INTEGER PRIMARY KEY NOT NULL,
|
|
id_num INTEGER NOT NULL,
|
|
group_name TEXT
|
|
)""")
|
|
cursor.close()
|
|
|
|
|
|
def iqh_select_id2group(conn: sqlite3.Connection, id_list: list[int]) -> list[tuple[int, str]]:
|
|
cursor = conn.cursor()
|
|
ids = ",".join(map(str, id_list))
|
|
cursor.execute(f"SELECT id_num, group_name FROM idrepo WHERE id_num IN ({ids})")
|
|
result = cursor.fetchall()
|
|
cursor.close()
|
|
return result
|
|
|
|
|
|
def iqh_select_group2id(conn: sqlite3.Connection, group_name: str) -> list[tuple[int, str]]:
|
|
cursor = conn.cursor()
|
|
cursor.execute(f"SELECT id_num, group_name FROM idrepo WHERE group_name = '{group_name}'")
|
|
result = cursor.fetchall()
|
|
cursor.close()
|
|
return result
|
|
|
|
|
|
def iqh_insert_data(conn: sqlite3.Connection, data: list[tuple[int, str]]):
|
|
cursor = conn.cursor()
|
|
values = ",".join(map(str, data))
|
|
cursor.execute(f"INSERT INTO idrepo (id_num, group_name) VALUES {values}")
|
|
conn.commit()
|
|
cursor.close()
|