-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathdbopr.py
123 lines (114 loc) · 3.7 KB
/
dbopr.py
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
#coding = 'utf-8'
'''
模块功能：sqlite3数据库操作模块
作者：Li Yu
创建时间：2019/05/02
创建地点：武汉大学，湖北，武汉
作者邮箱：[email protected]
'''
from tkinter import *
from tkinter import messagebox,filedialog
import sqlite3,os
from datetime import *
# 创建sqlite数据库
def createDB(sp_ins,dbname,curdir):
if(dbname == ''):
print(dbname)
messagebox.showwarning('警告', '请输入数据库名称！')
else:
file = os.path.exists(curdir+'/db/'+dbname+'.db')
print(curdir+'/db/'+dbname+'.db')
print(file)
if file: # 数据库已存在
messagebox.showwarning('警告', '数据库已存在！')
return
createsql = '''
create table qqzoneinfo
(id integer primary key autoincrement,
comment text,
cmtnum int,
likenum int,
tid text,
createtime long)
'''
conn = sqlite3.connect(curdir+'/db/'+dbname+'.db')
c = conn.cursor()
c.execute(createsql)
conn.commit()
conn.close()
messagebox.showinfo('通知', '创建成功！')
a = list(sp_ins.choosedb_cb['values'])
a.append(dbname+'.db')
sp_ins.dbchoose_cb['values'] = tuple(a)
sp_ins.choosedb_cb['values'] = tuple(a)
sp_ins.dbc_cb['values'] = tuple(a)
# 选择数据库
def chooseDB(db_ins,curdir):
db_ins.filepath = filedialog.askopenfilename(initialdir= os.path.dirname(curdir+'/db/'),filetypes = (("DB files","*.db"),("all files","*.*")))
db_ins.dbchoose_entry.select_clear()
fp = StringVar()
fp.set(db_ins.filepath)
db_ins.dbchoose_entry.configure(textvariable=fp)
# 获取数据库数据用于展示
def getDBData(db_ins,dbname,tablename,curdir):
if(dbname == ''):
messagebox.showwarning('警告', '请选择数据库！')
return
conn = sqlite3.connect(curdir+'/db/'+dbname)
c = conn.cursor()
res = c.execute('select id,comment,cmtnum,likenum,tid,createtime from ' + tablename).fetchall()
items = db_ins.dbtable_tree.get_children()
[db_ins.dbtable_tree.delete(item) for item in items]
i = 1
print(len(res))
for row in res:
db_ins.dbtable_tree.insert("",END,text=str(i),values=(row[0],row[1],row[2],row[3],row[4],datetime.fromtimestamp(row[5])))
i = i + 1
conn.close()
# 获取数据库数据用于可视化
def getData2Draw(dbname,tablename):
data = []
if(dbname == ''):
messagebox.showwarning('警告', '请选择数据库！')
return
conn = sqlite3.connect(dbname)
c = conn.cursor()
res = c.execute('select * from ' + tablename).fetchall()
for row in res:
data.append([int(row[2]),int(row[3]),int(row[5]),row[1]]) # cmtnum,likenum,createtime,comment
conn.close()
return data
# 删除数据库所有数据
def deleteAll(db_ins,dbname,tablename):
if(dbname == ''):
return
res = messagebox.askyesno('通知','确认清空？')
print(res)
if(res == False):
return
conn = sqlite3.connect(dbname)
c = conn.cursor()
c.execute('delete from ' + tablename)
conn.commit()
conn.close()
messagebox.showinfo('通知', '已清空！')
# 选择phantom路径
def choosePhantom(self,conf):
conf.phfilepath = filedialog.askopenfilename(filetypes = (("phantomjs files","*.exe"),("all files","*.*")))
self.phantomjs_entry.select_clear()
fp = StringVar()
fp.set(conf.phfilepath)
self.phantomjs_entry.configure(textvariable=fp)
with open(conf.curdir+'/db/phpos.txt','w') as f:
f.write(conf.phfilepath)
# 保存账号信息
def saveAccount(conf):
with open(conf.curdir+'/db/account.txt','w') as f:
f.write(conf.account)
# 插入爬取信息
def sqlopr(conn,cur,item,tablename):
number = cur.execute("select * from "+tablename+" where tid = '"+item['tid']+"'").fetchall()
if len(number) > 0:
return
cur.execute("insert into "+tablename+" (comment,cmtnum,tid,createtime) values (?,?,?,?)",(item['content'],int(item['cmtnum']),item['tid'],item['created_time']))
conn.commit()