pomodoro-python/database.py at master · tortuepin/pomodoro-python · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
import sqlite3
import datetime
import dbQuery
import os.path

class database:
    '''
    データベース関連
    '''

    def __init__(self):
        self.DBDirectory = "./dbs/"
        self.pomodoroDBName = "pomodoro.sqlite3" #ポモドーロした回数とか
        self.pomodoroTableName = "pomodoro"
        self.todoID = 0

        #もしディレクトリdbsがなかったら作る
        if not os.path.isdir(self.DBDirectory):
            os.mkdir(self.DBDirectory)


        #もしテーブルがなかったら作る
        if not self.isTable(self.pomodoroTableName):
            name = "create_" + self.pomodoroTableName
            q = dbQuery.dbQuery()
            query = q.getQuery(name)
            if query is not None:
                self.exeQuery(query)


    def exeQuery(self, query):
        '''
        指定されたqueryを実行する
        '''

        connector = sqlite3.connect(self.DBDirectory+self.pomodoroDBName)

        connector.execute(query)

        connector.commit()
        connector.close()


    def insertPomodoro(self):
        '''
        データベースに１ポモドーロ登録する
        '''
        connector = sqlite3.connect(self.DBDirectory+self.pomodoroDBName)

        query = "insert into " + str(self.pomodoroTableName) + " values" +\
                datetime.datetime.today().strftime("(%Y,%m,%d,%H,%M,") +\
                str(self.todoID) + ")"
        connector.execute(query)

        connector.commit()
        connector.close()

    def getTodaysPomodoro(self):
        '''
        今日何回ポモドーロしたか返す
        '''
        today = datetime.date.today()
        return self.getdaysPomodoro(today.year, today.month, today.day)

    def getdaysPomodoro(self, year, month, day):
        '''
        指定した日に何回ポモドーロしたか返す

        引数
        year
        month
        day
        '''
        connector = sqlite3.connect(self.DBDirectory+self.pomodoroDBName)

        query = "select count(*) from " + self.pomodoroTableName +\
                " where year = " + str(year) +\
                " and month = " + str(month) +\
                " and day = " + str(day)

        cursor = connector.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        connector.close()

        return result[0][0]

    def isTable(self, tablename):
        '''
        テーブルの存在確認
        '''
        connector = sqlite3.connect(self.DBDirectory+self.pomodoroDBName)

        query = ("select * from sqlite_master where type = 'table' and name = '%s'")
        cur = connector.execute(query % tablename)

        if cur.fetchone() == None:
            connector.close()
            return False

        connector.close()

        return True