1 # -*- coding: utf-8 -*-
4 IDとお守り名の組み合わせテーブルの作成・アクセスをするモジュール
5 2013/12/19 written by kei9
12 NAME = u"amulet_table"
13 COL_AMULET_NAME = u"amulet_name"
14 CREATE_SQL = u"""create table if not exists {table}
15 (id integer primary key, {amulet_name} varchar unique);""".format(
17 amulet_name=COL_AMULET_NAME)
18 INSERT_SQL = u"""insert into {table}
19 ({amulet_col}) values(?);""".format(
21 amulet_col=COL_AMULET_NAME)
22 SELECT_ID2NAME_SQL = u"""select {amulet_col} from {table} where id='{{id}}'""".format(
23 amulet_col=COL_AMULET_NAME,
25 SELECT_NAME2ID_SQL = u"""select id from {table} where {amulet_col}='{{amulet_name}}'""".format(
26 amulet_col=COL_AMULET_NAME,
28 SELECT_ALL_SQL = u"""select id, {amulet_col} from {table}""".format(
29 amulet_col=COL_AMULET_NAME,
32 class AmuletTableGenerator(object):
33 u""" IDとお守り名の組み合わせテーブルを作成するクラス """
38 def insert_data(self, db_cursor, csv_reader):
39 u""" csv_readerからデータを読み込み、 db_cursorへデータを挿入する。"""
40 db_cursor.execute(CREATE_SQL)
42 csv_reader.next() # skip header row
43 for row in csv_reader: # (name)
44 amulet_name = row[0].strip()
46 db_cursor.execute(INSERT_SQL, (amulet_name,))
47 except sqlite3.IntegrityError, e:
48 print "not unique:", amulet_name.decode("utf-8")
52 class AmuletTableAccessor(object):
53 u""" スキルIDとスキル名の組み合わせテーブルへのアクセス用クラス """
54 def __init__(self, db_cursor):
55 u""" db_cursor: cursor of sqlite3 database """
56 self._cursor = db_cursor
57 self._id2name_dict, self._name2id_dict = self.get_dict()
60 u""" スキルIDからスキル名への変換辞書を得る。
61 return (id2name_dict, name2id_dict)"""
62 self._cursor.execute(SELECT_ALL_SQL)
63 id2name_dict, name2id_dict = {}, {}
64 for row in self._cursor.fetchall(): #(id, name)
65 skill_id, skill_name = row[0], row[1]
66 id2name_dict[skill_id] = skill_name
67 name2id_dict[skill_name] = skill_id
69 return (id2name_dict, name2id_dict)
71 def get_id_sorted_names(self, is_desc=False):
72 u""" Id順でソートされたお守り名のタプルを返す
73 is_desc=Trueで降順にする。"""
75 for amu_id, name in sorted(self._id2name_dict.items(), key = lambda x: x[0], reverse=is_desc):
79 def get_amulet_name(self, skill_id):
82 if skill_id in self._id2name_dict[skill_id]:
83 return self._id2name_dict[skill_id]
87 def get_amulet_id(self, skill_name):
90 if skill_name in self._name2id_dict[skill_name]:
91 return self._name2id_dict[skill_name]