Spider4DJIDrone/sqliteWrapper.py at master · labrick/Spider4DJIDrone · 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
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
#!/usr/bin/python3
# coding=UTF-8

import sys
import time
import http.client as httplib
import sqlite3
from lxml import etree

# print(str(dbPointer))
# dbPointerr = common.getDBPointer()
# print(str(dbPointerr))

class SqliteWrapper:
    ''' docstring for SqliteWrapper '''
    def __init__(self, tableName):
        self.fundb = sqlite3.connect("./dji.db")
        self.fundb.text_factory = str
        self.dbPointer = self.fundb.cursor()
        self.tableName = tableName
        sql = "CREATE TABLE IF NOT EXISTS " + self.tableName + \
            " (postDate TEXT, postBy VARCHAR(20), device TEXT, postTitle TEXT, postLink TEXT UNIQUE, visitTimes INT, commentTimes INT, updateTime VARCHAR(20))"
        self.dbPointer.execute(sql)

    def fmtDate(self, dateStr):
        if(len(str(dateStr.split()[0]).split('-')) < 3):
            dateStr = str(time.localtime(time.time()).tm_year) + "-" + dateStr.split()[0]
        else:
            dateStr = dateStr.split()[0]
        t = time.strptime(dateStr, "%Y-%m-%d")
        return time.strftime("%Y-%m-%d", t)

    def fmtDateTime(self, dateStr):
        if(len(str(dateStr.split()[0]).split('-')) < 3):
            dateStr = str(time.localtime(time.time()).tm_year) + "-" + dateStr
        else:
            dateStr = dateStr
        t = time.strptime(dateStr, "%Y-%m-%d %H:%M")
        return time.strftime("%Y-%m-%d %H:%M", t)

    # save data to sqlite db
    def saveData(self, postDate, postBy, device, postTitle, postLink, visitTimes, commentTimes, updateTime):
        postDate = self.fmtDate(postDate)
        updateTime = self.fmtDateTime(updateTime)
        # sql = "CREATE TABLE IF NOT EXISTS " + self.tableName + \
        #     " (postDate TEXT, postBy VARCHAR(20), device TEXT, postTitle TEXT, postLink TEXT UNIQUE, visitTimes INT, commentTimes INT, updateTime VARCHAR(20))"
        # self.dbPointer.execute(sql)

        # INSERT data
        # print("deal with: %s, tableName: %s" %(date, tableName))
        try:
            self.dbPointer.execute("INSERT INTO " + self.tableName + \
                    # " VALUES (?, ?, ?, ?, ?)", (date(postDate), postBy, visitTimes, commentTimes, datetime(updateTime)))
                    " VALUES (?, ?, ?, ?, ?, ?, ?, ?)", (postDate, postBy, device, postTitle, postLink, visitTimes, commentTimes, updateTime))
        except:
            pass
            # 修改已经存在的值

        self.fundb.commit()

    def getLastestDate(self):
        self.dbPointer.execute("SELECT * FROM " + self.tableName + " ORDER BY DATE(postDate) DESC")
        return self.dbPointer.fetchone()

    def getRowNum(self):
        self.dbPointer.execute("SELECT COUNT(*) FROM " + self.tableName)
        return self.dbPointer.fetchone()[0]

    # dump data from db
    def dumpDb(self, tableName):
        pass
        self.dbPointer.execute("SELECT * FROM " + tableName)
        while(1):
            result = dbPointer.fetchone()
            if (result != None):
                print(result)
            else:
                break

    def onSale(self, startDate, endDate, onSaleTime):
        if (startDate < onSaleTime) and (onSaleTime < endDate):
            return True
        else:
            return False

    def getNoAirItem(self, itemName, startDate, endDate):
        # self.dbPointer.execute('SELECT * FROM ' + self.tableName + ' where postDate between "' \
        self.dbPointer.execute('SELECT ' + itemName + ' FROM ' + self.tableName + ' where postDate between "' \
                + startDate + '" and "' + endDate + '" and postDate != "' + endDate +  '" and device != "mavic air" ORDER BY postDate DESC')
        itemNames = list(self.dbPointer.fetchall())

        result = []
        for item in itemNames:
            result.append(item[0])
            # result.append(item)
        return result

    def getItem(self, itemName, startDate, endDate):
        # self.dbPointer.execute('SELECT * FROM ' + self.tableName + ' where postDate between "' \
        self.dbPointer.execute('SELECT ' + itemName + ' FROM ' + self.tableName + ' where postDate between "' \
                + startDate + '" and "' + endDate + '" and postDate != "' + endDate +  '" ORDER BY postDate DESC')
        itemNames = list(self.dbPointer.fetchall())

        result = []
        for item in itemNames:
            result.append(item[0])
            # result.append(item)
        return result


    def getDevice(self, startDate, endDate):
        startDate = self.fmtDate(startDate + "-01")
        endDate = self.fmtDate(endDate + "-01")
        # print(startDate)
        # print(endDate)
        airOnSaleTime = "2018-02-01"
        result = []
        if(self.onSale(startDate, endDate, airOnSaleTime)):
            result.extend(self.getNoAirItem("device", startDate, airOnSaleTime))
            result.extend(self.getItem("device", airOnSaleTime, endDate))
        elif (endDate < airOnSaleTime):
            result.extend(self.getNoAirItem("device", startDate, endDate))
        else:
            result.extend(self.getItem("device", startDate, endDate))

        return result

    def getPostLink(self, startDate, endDate):
        startDate = self.fmtDate(startDate + "-01")
        endDate = self.fmtDate(endDate + "-01")
        # print(startDate)
        # print(endDate)
        airOnSaleTime = "2018-02-01"
        result = []
        if(self.onSale(startDate, endDate, airOnSaleTime)):
            result.extend(self.getNoAirItem("postLink", startDate, airOnSaleTime))
            result.extend(self.getItem("postLink", airOnSaleTime, endDate))
        elif (endDate < airOnSaleTime):
            result.extend(self.getNoAirItem("postLink", startDate, airOnSaleTime))
        else:
            result.extend(self.getItem("postLink", startDate, endDate))

        return result