1 # -*- coding: utf-8 -*-
4 スキルIDとスキル名の組み合わせテーブルの作成・アクセスをするモジュール
5 2013/12/19 written by kei9
15 COL_SKILL_ID = u"skill_id"
16 COL_SKILL_NAME = u"skill_name"
17 CREATE_SQL = u"""create table if not exists {table}
18 ({skill_id} integer primary key, {skill_name} varchar unique);""".format(
20 skill_id=COL_SKILL_ID,
21 skill_name=COL_SKILL_NAME)
22 INSERT_SQL = u"""insert into {table}
23 ({skill_id}, {skill_col}) values(?,?);""".format(
25 skill_id=COL_SKILL_ID,
26 skill_col=COL_SKILL_NAME)
27 SELECT_ID2SKILL_SQL = u"""select {skill_col} from {table} where {skill_id}='{{skill_id}}'""".format(
28 skill_id=COL_SKILL_ID,
29 skill_col=COL_SKILL_NAME,
31 SELECT_SKILL2ID_SQL = u"""select {skill_id} from {table} where {skill_col}='{{skill_name}}'""".format(
32 skill_id=COL_SKILL_ID,
33 skill_col=COL_SKILL_NAME,
35 SELECT_ALL_SQL = u"""select {skill_id}, {skill_col} from {table}""".format(
36 skill_id=COL_SKILL_ID,
37 skill_col=COL_SKILL_NAME,
41 class SkillTableGenerator(object):
42 u""" スキルIDとスキル名の組み合わせテーブルを作成するクラス """
47 def insert_data(self, db_cursor, csv_reader):
48 u""" csv_readerからデータを読み込み、 db_cursorへデータを挿入する。"""
49 db_cursor.execute(CREATE_SQL)
51 csv_reader.next() # skip header row
52 for row in csv_reader: # (id, name)
53 skill_id, skill_name = int(row[0].strip()), row[1].strip()
55 db_cursor.execute(INSERT_SQL, (skill_id, skill_name))
56 except sqlite3.IntegrityError, e:
57 print "not unique:", skill_name.decode("utf-8")
61 class SkillTableAccessor(object):
62 u""" スキルIDとスキル名の組み合わせテーブルへのアクセス用クラス """
63 __metaclass__ = singleton.Singleton
64 def __init__(self, db_cursor):
65 u""" db_cursor: cursor of sqlite3 database """
66 self._cursor = db_cursor
67 self._id2name_dict, self._name2id_dict = self._get_dict()
70 u""" スキルIDからスキル名への変換辞書を得る。
71 return (id2name_dict, name2id_dict)"""
72 self._cursor.execute(SELECT_ALL_SQL)
73 id2name_dict, name2id_dict = {}, {}
74 for row in self._cursor.fetchall(): #(id, name)
75 skill_id, skill_name = row[0], row[1]
76 id2name_dict[skill_id] = skill_name
77 name2id_dict[skill_name] = skill_id
79 return (id2name_dict, name2id_dict)
81 def get_dict(self, copied=False):
82 u""" スキルIDからスキル名への変換辞書を得る。
83 return (id2name_dict, name2id_dict)"""
85 return (self._id2name_dict.copy(), self._name2id_dict.copy())
87 return (self._id2name_dict, self._name2id_dict)
89 def get_id_sorted_names(self, is_desc=False):
90 u""" Id順でソートされたスキル名のタプルを返す
91 is_desc=Trueで降順にする。"""
93 for skill_id, name in sorted(self._id2name_dict.items(), key = lambda x: x[0], reverse=is_desc):
97 def get_skill_name(self, skill_id):
100 if skill_id in self._id2name_dict[skill_id]:
101 return self._id2name_dict[skill_id]
105 def get_skill_id(self, skill_name):
108 if skill_name in self._name2id_dict[skill_name]:
109 return self._name2id_dict[skill_name]