+++ /dev/null
-# -*- coding: utf-8 -*-
-
-# databaseの生成用スクリプト
-
-import sqlite3
-import csv
-import os.path
-import os, sys
-
-DataDirecroty = "data"
-OutputDBFileName = "OmamoriMH4.sqlite3"
-MinMaxFileName = "minmax.csv"
-SecondSkillFileName = "2ndskill.csv"
-SecondSlotFileName = "slot_2ndskill.csv"
-
-class DataBaseGenerator(object):
- u""" this is access class to database """
-
- # for minmax
- MinMaxMasterTableName = u"skill_minmax_master"
- MinMaxTableName = u"skill_minmax_{id}"
- MinMaxMasterCreateSql = u"create table if not exists skill_minmax_master(id integer primary key, omamori_name varchar, skill_table_name varchar);"
- MinMaxCreateSql = u"create table if not exists {table_name}(id integer primary key, skill_name varchar, min1 integer, max1 integer, min2 integer, max2 integer);"
-
- # for Second skill
- SecondTableName = u"skill_second_{id}"
- SecondSlotTableName = u"skill_second_slot"
- SecondMasterTableName = u"skill_second_master"
- SecondMasterCreateSql = u"create table if not exists skill_Second_master(id integer primary key, omamori_name varchar, Second_table_name varchar);"
- SecondCreateSql = u"create table if not exists {table_name}(id integer primary key, random_seed integer unique, skill_name1 varchar, skill_name2 varchar, skill_name3 varchar, skill_name4 varchar, skill_name5 varchar, skill_name6 varchar, skill_name7 varchar);"
- SecondSlotCreateSql = u"create table if not exists {table}(id integer primary key, random_seed integer unique, slot1 integer, slot2 integer, slot3 integer, slot4 integer, slot5 integer, slot6 integer, slot7 integer);".format(table=SecondSlotTableName)
-
- def __init__(self, dbName=None):
- if dbName is None:
- self._dbName = ":memory:"
- else :
- self._dbName = dbName
-
- def Open(self):
- self._connect = sqlite3.connect(self._dbName)
- self._connect.text_factory = str # for usage of utf-8
- self._cursor = self._connect.cursor()
-
- def CreateSecondSkillTable(self):
- u"""
- csvファイルから第2スキルの表を生成する
- """
- print "load Second skill"
- self._cursor.execute(self.SecondMasterCreateSql) # create master table of Second skill
- reader = csv.reader(file(os.path.join(DataDirecroty, SecondSkillFileName), "r")) # (omamori_name, filename of Second_skill)
-
- reader.next() # skip header row
- cnt = 0
- for row in reader:
- tableName = self.SecondTableName.format(id=cnt)
- createSql = self.SecondCreateSql.format(table_name=tableName)
- insertSql = u"insert into {table}(omamori_name, Second_table_name) values(?, ?);".format(table=self.SecondMasterTableName)
- oma_name = row[0].strip()
- self._cursor.execute(insertSql, (oma_name, tableName))
- self._cursor.execute(createSql) # create skill table for each omamori
-
- reader1 = None
- fname1 = os.path.join(DataDirecroty, row[1].strip())
- if os.path.exists(fname1) and os.path.isfile(fname1):
- reader1 = csv.reader(file(fname1, "r")) # (random seed, skill1, skil2, ..., skill7)
- else:
- print "file1 ", fname1, " doesn't exist!"
-
- insertSql = u"insert into {table}(random_seed, skill_name1, skill_name2, skill_name3, skill_name4, skill_name5, skill_name6, skill_name7) values(?,?,?,?,?,?,?,?);".format(table=tableName)
-
- if reader1 is not None:
- reader1.next() # skip header
- for row1 in reader1:
- val_tup = tuple([x.strip() if i != 0 else int(x.strip()) for i, x in enumerate(row1)])
- self._cursor.execute(insertSql, val_tup)
-
- cnt += 1
-
- # for Slot table
- self._cursor.execute(self.SecondSlotCreateSql) # create slot table of Second skill
- reader = csv.reader(file(os.path.join(DataDirecroty, SecondSlotFileName), "r")) # (random_seed, slot1, slot2, ..., slot7)
-
- reader.next() # skip header row
- insertSql = u"insert into {table}(random_seed, slot1, slot2, slot3, slot4, slot5, slot6, slot7) values(?,?,?,?,?,?,?,?);".format(table=self.SecondSlotTableName)
- for row in reader:
- val_tup = tuple([int(x.strip()) for x in row])
- self._cursor.execute(insertSql, val_tup)
-
- self._connect.commit()
-
- def CreateMinMaxTable(self):
- u"""
- お守り名と対応するスキルの最大最小値の記載されたcsvファイルから
- お守りごとのスキルの最大最小値を記載したテーブルを作成する
- """
- print "load min & max of skill"
-
- self._cursor.execute(self.MinMaxMasterCreateSql) # create master table of skill min max
- reader = csv.reader(file(os.path.join(DataDirecroty, MinMaxFileName), "r")) # (name, filename of minmax1, filename of minmax2)
-
- reader.next() # skip header row
- cnt = 0
- for row in reader:
- tableName = self.MinMaxTableName.format(id=cnt)
- createSql = self.MinMaxCreateSql.format(table_name=tableName)
- insertSql = u"insert into {table}(omamori_name, skill_table_name) values(?, ?);".format(table=self.MinMaxMasterTableName)
- oma_name = row[0].strip()
- self._cursor.execute(insertSql, (oma_name, tableName))
- self._cursor.execute(createSql) # create minmax table for each omamori
-
- reader1, reader2 = None, None
- fname1, fname2 = os.path.join(DataDirecroty, row[1].strip()), os.path.join(DataDirecroty, row[2].strip())
- if os.path.exists(fname1) and os.path.isfile(fname1):
- reader1 = csv.reader(file(fname1, "r")) # (name of skill1, min1, max1)
- else:
- print "file1 ", fname1, " doesn't exist!"
- if os.path.exists(fname2) and os.path.isfile(fname2):
- reader2 = csv.reader(file(fname2, "r")) # (name of skill1, min1, max1)
- else:
- print "file2 ", fname2, " doesn't exist!"
-
- insertSql = u"insert into {table}(skill_name, min1, max1, min2, max2) values(?,?,?,?,?);".format(table=tableName)
-
- skill_dict = {} # dict for skill1, skill2
-
- if reader1 is not None:
- reader1.next() # skip header
- for row1 in reader1:
- minVal, maxVal = int(row1[1]), int(row1[2])
- skill_name = row1[0].strip()
- skill_dict[skill_name] = (minVal, maxVal, 0, 0)
-
- if reader2 is not None:
- reader2.next() # skip header
- for row2 in reader2:
- minVal, maxVal = int(row2[1]), int(row2[2])
- skill_name = row2[0].strip()
- if skill_name in skill_dict:
- val = skill_dict[row2[0]]
- skill_dict[skill_name] = (val[0], val[1], minVal, maxVal)
- else:
- skill_dict[skill_name] = (0, 0, minVal, maxVal)
-
- # insert to minmax table
- for k, v in skill_dict.items():
- self._cursor.execute(insertSql, (k,) + v)
-
- cnt += 1
-
- self._connect.commit()
-
- def Close(self):
- self._connect.commit()
- self._cursor.close()
- self._connect.close()
-
- def DropTables(self):
- u""" 全てのテーブルの削除 """
- self._cursor.execute("select name from sqlite_master where type='table';")
- tableNames = []
- for catalog in cur.fetchall():
- tableNames.append(catalog[0])
- for table in tableNames:
- self._cursor.execute("drop table {name};".format(name=table))
- self._connect.commit()
-
-
-if __name__ == "__main__":
- db = DataBaseGenerator(OutputDBFileName)
- #db = DataBaseGenerator() # for memory
- db.Open()
- db.CreateMinMaxTable()
- db.CreateSecondSkillTable()
- db.Close()
-
OSDN Git Service
