1 # -*- coding: utf-8 -*-
4 IDとお守り名の組み合わせテーブルの作成・アクセスをするモジュール
5 2013/12/19 written by kei9
14 NAME = u"amulet_table"
15 COL_AMULET_NAME = u"amulet_name"
16 CREATE_SQL = u"""create table if not exists {table}
17 (id integer primary key, {amulet_name} varchar unique);""".format(
19 amulet_name=COL_AMULET_NAME)
20 INSERT_SQL = u"""insert into {table}
21 ({amulet_col}) values(?);""".format(
23 amulet_col=COL_AMULET_NAME)
24 SELECT_ID2NAME_SQL = u"""select {amulet_col} from {table} where id='{{id}}'""".format(
25 amulet_col=COL_AMULET_NAME,
27 SELECT_NAME2ID_SQL = u"""select id from {table} where {amulet_col}='{{amulet_name}}'""".format(
28 amulet_col=COL_AMULET_NAME,
30 SELECT_ALL_SQL = u"""select id, {amulet_col} from {table}""".format(
31 amulet_col=COL_AMULET_NAME,
34 class AmuletTableGenerator(object):
35 u""" IDとお守り名の組み合わせテーブルを作成するクラス """
40 def insert_data(self, db_cursor, csv_reader):
41 u""" csv_readerからデータを読み込み、 db_cursorへデータを挿入する。"""
42 db_cursor.execute(CREATE_SQL)
44 csv_reader.next() # skip header row
45 for row in csv_reader: # (name)
46 amulet_name = row[0].strip()
48 db_cursor.execute(INSERT_SQL, (amulet_name,))
49 except sqlite3.IntegrityError, e:
50 print "not unique:", amulet_name.decode("utf-8")
54 class AmuletTableAccessor(object):
55 u""" スキルIDとスキル名の組み合わせテーブルへのアクセス用クラス """
56 __metaclass__ = singleton.Singleton
57 def __init__(self, db_cursor):
58 u""" db_cursor: cursor of sqlite3 database """
59 self._cursor = db_cursor
60 self._id2name_dict, self._name2id_dict = self._get_dict()
63 u""" スキルIDからスキル名への変換辞書を得る。
64 return (id2name_dict, name2id_dict)"""
65 self._cursor.execute(SELECT_ALL_SQL)
66 id2name_dict, name2id_dict = {}, {}
67 for row in self._cursor.fetchall(): #(id, name)
68 skill_id, skill_name = row[0], row[1]
69 id2name_dict[skill_id] = skill_name
70 name2id_dict[skill_name] = skill_id
72 return (id2name_dict, name2id_dict)
74 def get_dict(self, copied=False):
75 u""" スキルIDからスキル名への変換辞書を得る。
76 return (id2name_dict, name2id_dict)"""
78 return (self._id2name_dict.copy(), self._name2id_dict.copy())
80 return (self._id2name_dict, self._name2id_dict)
82 def get_id_sorted_names(self, is_desc=False):
83 u""" Id順でソートされたお守り名のタプルを返す
84 is_desc=Trueで降順にする。"""
86 for amu_id, name in sorted(self._id2name_dict.items(), key = lambda x: x[0], reverse=is_desc):
90 def get_amulet_name(self, skill_id):
93 if skill_id in self._id2name_dict[skill_id]:
94 return self._id2name_dict[skill_id]
98 def get_amulet_id(self, skill_name):
101 if skill_name in self._name2id_dict[skill_name]:
102 return self._name2id_dict[skill_name]