model/seed2thresholdtable.py

   1 # -*- coding: utf-8 -*-
   2
   3 u"""
   4 Seed2と判定値の組み合わせテーブルの作成・アクセスをするモジュール
   5 2013/12/19 written by kei9
   6 """
   7
   8 import sqlite3
   9 import csv
  10
  11 # for seed2 to threshold1
  12 u""" SEED2から判定値1へのテーブル """
  13 NAME = u"seed2_threshold{index}"
  14 COL_SEED2 = u"seed2"
  15 COL_THRESHOLD1 = u"threshold1"
  16 COL_THRESHOLD2 = u"threshold2"
  17 COL_THRESHOLD3 = u"threshold3"
  18 COL_THRESHOLD4 = u"threshold4"
  19 COL_THRESHOLD5 = u"threshold5"
  20 COL_THRESHOLD6 = u"threshold6"
  21 COL_THRESHOLD7 = u"threshold7"
  22 COL_THRESHOLD_LIST = [COL_THRESHOLD1, COL_THRESHOLD2, COL_THRESHOLD3, COL_THRESHOLD4, COL_THRESHOLD5, COL_THRESHOLD6, COL_THRESHOLD7]
  23 CREATE_SQL = u"""create table if not exists {{table_name}}
  24     (id integer primary key, {seed} integer unique,
  25     {threshold1} integer, {threshold2} integer, {threshold3} integer,
  26     {threshold4} integer, {threshold5} integer, {threshold6} integer, {threshold7} integer);""".format(
  27         seed=COL_SEED2,
  28         threshold1=COL_THRESHOLD1,
  29         threshold2=COL_THRESHOLD2,
  30         threshold3=COL_THRESHOLD3,
  31         threshold4=COL_THRESHOLD4,
  32         threshold5=COL_THRESHOLD5,
  33         threshold6=COL_THRESHOLD6,
  34         threshold7=COL_THRESHOLD7)
  35 INSERT_SQL = u"""insert into {{table_name}}
  36     ({seed}, {threshold1}, {threshold2}, {threshold3}, {threshold4}, {threshold5}, {threshold6}, {threshold7})
  37     values(?,?,?,?,?,?,?,?);""".format(
  38         seed=COL_SEED2,
  39         threshold1=COL_THRESHOLD1,
  40         threshold2=COL_THRESHOLD2,
  41         threshold3=COL_THRESHOLD3,
  42         threshold4=COL_THRESHOLD4,
  43         threshold5=COL_THRESHOLD5,
  44         threshold6=COL_THRESHOLD6,
  45         threshold7=COL_THRESHOLD7)
  46 SELECT_ALL_SQL = u"""select {seed}, {threshold1}, {threshold2},
  47     {threshold3}, {threshold4}, {threshold5}, {threshold6}, {threshold7} from {{table_name}} """.format(
  48         seed=COL_SEED2,
  49         threshold1=COL_THRESHOLD1,
  50         threshold2=COL_THRESHOLD2,
  51         threshold3=COL_THRESHOLD3,
  52         threshold4=COL_THRESHOLD4,
  53         threshold5=COL_THRESHOLD5,
  54         threshold6=COL_THRESHOLD6,
  55         threshold7=COL_THRESHOLD7)
  56 SELECT_SEEDS_FROM_THRESHOLD_SQL = u"""
  57     select {seed} from {{table_name}}
  58     where {threshold1} >= {{threshold}} or
  59     {threshold2} >= {{threshold}} or
  60     {threshold3} >= {{threshold}} or
  61     {threshold4} >= {{threshold}} or
  62     {threshold5} >= {{threshold}} or
  63     {threshold6} >= {{threshold}} or
  64     {threshold7} >= {{threshold}}
  65     """.format(
  66         seed=COL_SEED2,
  67         threshold1=COL_THRESHOLD1,
  68         threshold2=COL_THRESHOLD2,
  69         threshold3=COL_THRESHOLD3,
  70         threshold4=COL_THRESHOLD4,
  71         threshold5=COL_THRESHOLD5,
  72         threshold6=COL_THRESHOLD6,
  73         threshold7=COL_THRESHOLD7)
  74 SELECT_SEEDS_FROM_THRESHOLD_COL_SQL = u"""
  75     select {seed} from {{table_name}}
  76     where {{threshold_col}} >= {{threshold}}
  77     """.format(seed=COL_SEED2)
  78 SELECT_THRESHOLDS_FROM_SEED2_SQL = u""" select
  79     {threshold1}, {threshold2}, {threshold3},
  80     {threshold4}, {threshold5}, {threshold6}, {threshold7}
  81     from {{table_name}} where {seed}={{seed2}}
  82     """.format(
  83         seed=COL_SEED2,
  84         threshold1=COL_THRESHOLD1,
  85         threshold2=COL_THRESHOLD2,
  86         threshold3=COL_THRESHOLD3,
  87         threshold4=COL_THRESHOLD4,
  88         threshold5=COL_THRESHOLD5,
  89         threshold6=COL_THRESHOLD6,
  90         threshold7=COL_THRESHOLD7)
  91
  92 class Seed2ThresholdTableGenerator(object):
  93     u""" Seed2と判定値の組み合わせテーブルの作成するクラス """
  94     def __init__(self):
  95         u""" コンストラクタ。"""
  96         pass
  97
  98     def insert_data(self, db_cursor, csv_reader, index):
  99         u""" csv_readerからデータを読み込み、 db_cursorへデータを挿入する。
 100         indexでどの判定値についてのものかを決定する """
 101         table_name = NAME.format(index=index)
 102         db_cursor.execute(CREATE_SQL.format(table_name=table_name))
 103
 104         csv_reader.next()   # skip header row
 105         for row in csv_reader:  #(seed2, threshold1, threshold2, ..., threshold7)
 106             vals = [int(r.strip()) for r in row]
 107             db_cursor.execute(INSERT_SQL.format(table_name=table_name), tuple(vals))
 108
 109 class Seed2ThresholdTableAccessor(object):
 110     u""" Seed2と判定値の組み合わせテーブルへのアクセス用クラス """
 111     def __init__(self, db_cursor):
 112         u""" db_cursor: cursor of sqlite3 database """
 113         self._cursor = db_cursor
 114
 115     def select_thresholds_from_seed2(self, seed2, index):
 116         u""" 指定されたindexの表からのseed2から判定値1-7を返す """
 117         table_name = NAME.format(index=index)
 118         sql = SELECT_THRESHOLDS_FROM_SEED2_SQL.format(
 119                 table_name=table_name, seed2=seed2)
 120         self._cursor.execute(sql)
 121         th1, th2, th3, th4, th5, th6, th7 = self._cursor.fetchone()
 122         return (th1, th2, th3, th4, th5, th6, th7)
 123
 124     def select_seed2s_from_all_threshold(self, threshold, index):
 125         u""" 指定された値以上の判定値を一つでも持つseed2を返す """
 126         table_name = NAME.format(index=index)
 127         sql = SELECT_SEEDS_FROM_THRESHOLD_SQL.format(
 128                 table_name=table_name, threshold=threshold)
 129         self._cursor.execute(sql)
 130         seeds = set([x[0] for x in self._cursor.fetchall()])
 131         return seeds
 132
 133     def select_seed2s_from_theshold_index(self, threshold, index, amulet_pos):
 134         u""" 指定された枠(amulet_pos)に、
 135         指定値以上の判定値を一つでも持つseed2を返す """
 136         table_name = NAME.format(index=index)
 137         sql = SELECT_SEEDS_FROM_THRESHOLD_SQL.format(
 138                 table_name=table_name, threshold=threshold,
 139                 threshold_col=COL_THRESHOLD_LIST[amulet_pos])
 140         self._cursor.execute(sql)
 141         seeds = set([x[0] for x in self._cursor.fetchall()])
 142         return seeds