model/skilltable.py

   1 # -*- coding: utf-8 -*-
   2
   3 u"""
   4 スキルIDとスキル名の組み合わせテーブルの作成・アクセスをするモジュール
   5 2013/12/19 written by kei9
   6 """
   7
   8 import sqlite3
   9 import csv
  10
  11 # for skill table
  12 NAME = u"skill_table"
  13 COL_SKILL_ID = u"skill_id"
  14 COL_SKILL_NAME = u"skill_name"
  15 CREATE_SQL = u"""create table if not exists {table}
  16     ({skill_id} integer primary key, {skill_name} varchar unique);""".format(
  17         table=NAME,
  18         skill_id=COL_SKILL_ID,
  19         skill_name=COL_SKILL_NAME)
  20 INSERT_SQL = u"""insert into {table}
  21     ({skill_id}, {skill_col}) values(?,?);""".format(
  22         table=NAME,
  23         skill_id=COL_SKILL_ID,
  24         skill_col=COL_SKILL_NAME)
  25 SELECT_ID2SKILL_SQL = u"""select {skill_col} from {table} where {skill_id}='{{skill_id}}'""".format(
  26         skill_id=COL_SKILL_ID,
  27         skill_col=COL_SKILL_NAME,
  28         table=NAME)
  29 SELECT_SKILL2ID_SQL = u"""select {skill_id} from {table} where {skill_col}='{{skill_name}}'""".format(
  30         skill_id=COL_SKILL_ID,
  31         skill_col=COL_SKILL_NAME,
  32         table=NAME)
  33 SELECT_ALL_SQL = u"""select {skill_id}, {skill_col} from {table}""".format(
  34         skill_id=COL_SKILL_ID,
  35         skill_col=COL_SKILL_NAME,
  36         table=NAME)
  37
  38
  39 class SkillTableGenerator(object):
  40     u""" スキルIDとスキル名の組み合わせテーブルを作成するクラス """
  41     def __init__(self):
  42         u""" コンストラクタ """
  43         pass
  44
  45     def insert_data(self, db_cursor, csv_reader):
  46         u""" csv_readerからデータを読み込み、 db_cursorへデータを挿入する。"""
  47         db_cursor.execute(CREATE_SQL)
  48
  49         csv_reader.next()   # skip header row
  50         for row in csv_reader:  # (id, name)
  51             skill_id, skill_name = int(row[0].strip()), row[1].strip()
  52             try:
  53                 db_cursor.execute(INSERT_SQL, (skill_id, skill_name))
  54             except sqlite3.IntegrityError, e:
  55                 print "not unique:", skill_name.decode("utf-8")
  56                 raise e
  57
  58
  59 class SkillTableAccessor(object):
  60     u""" スキルIDとスキル名の組み合わせテーブルへのアクセス用クラス """
  61     def __init__(self, db_cursor):
  62         u""" db_cursor: cursor of sqlite3 database """
  63         self._cursor = db_cursor
  64         self._id2name_dict, self._name2id_dict = self.get_dict()
  65
  66     def get_dict(self):
  67         u""" スキルIDからスキル名への変換辞書を得る。
  68         return (id2name_dict, name2id_dict)"""
  69         self._cursor.execute(SELECT_ALL_SQL)
  70         id2name_dict, name2id_dict = {}, {}
  71         for row in self._cursor.fetchall(): #(id, name)
  72             skill_id, skill_name = row[0], row[1]
  73             id2name_dict[skill_id] = skill_name
  74             name2id_dict[skill_name] = skill_id
  75
  76         return (id2name_dict, name2id_dict)
  77
  78     def get_id_sorted_names(self, is_desc=False):
  79         u""" Id順でソートされたスキル名のタプルを返す
  80         is_desc=Trueで降順にする。"""
  81         ls = []
  82         for skill_id, name in sorted(self._id2name_dict.items(), key = lambda x: x[0], reverse=is_desc):
  83             ls.append(name)
  84         return tuple(ls)
  85
  86     def get_skill_name(self, skill_id):
  87         u""" スキルIDからスキル名を得る
  88         存在しない場合はNoneを返す"""
  89         if skill_id in self._id2name_dict[skill_id]:
  90             return self._id2name_dict[skill_id]
  91         else:
  92             None
  93
  94     def get_skill_id(self, skill_name):
  95         u""" スキル名からスキルIdを得る
  96         存在しない場合はNoneを返す"""
  97         if skill_name in self._name2id_dict[skill_name]:
  98             return self._name2id_dict[skill_name]
  99         else:
 100             None
 101
 102