def inita():
#`date` timestamp DEFAULT CURRENT_TIMESTAMP(0),
# date TimeStamp DEFAULT (datetime('now','localtime'))
cur = get_db().cursor()
cur.execute("""CREATE TABLE `blog`
(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT,
content TEXT NOT NULL,
abstract TEXT NOT NULL,
date TimeStamp DEFAULT (datetime('now','localtime')),
tag TEXT,
file INT
)""")
cur.execute("""CREATE TABLE `tag`
(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
tag TEXT,
blog INT
)""")
cur.execute("""CREATE TABLE `comm`
(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
author TEXT,
content TEXT NOT NULL,
blog INT NOT NULL,
date TimeStamp DEFAULT (datetime('now','localtime')),
reply smallint
)""")
get_db().commit()
def alUpdate(self):
blogdb = get_db()
cur = blogdb.cursor()
if self.method == 'file':
cur.execute(
' SELECT id FROM blog WHERE file = ? ORDER BY id DESC LIMIT 8 OFFSET ?',
(
self.key,
self.page,
))
elif self.method == 'tag':
cur.execute('SELECT blog from tag where tag = ? LIMIT 8 OFFSET ?',
(
self.key,
self.page,
))
else:
cur.execute(
' SELECT id FROM blog ORDER BY id DESC LIMIT 8 OFFSET ?',
(self.page, ))
altemp = cur.fetchall()
altemp = list(map(lambda x: int(x[0]), altemp))
altemp.sort(reverse=True)
self.al = altemp
return self.al
def getPagn(self):
blogdb = get_db()
cur = blogdb.cursor()
if self.method == 'file':
cur.execute('SELECT count(*) FROM blog WHERE file = ?;',
(self.key, ))
elif self.method == 'tag':
cur.execute('SELECT count(*) FROM tag WHERE tag = ?;',
(self.key, ))
else:
cur.execute('SELECT count(*) FROM blog where file>0;')
pMax = cur.fetchall()
pMax = (int(pMax[0][0]) + 7) / 8
if self.page <= pMax and self.page > 0:
pagn = [[x, '', x] for x in range(1, pMax + 1)]
pagn[self.page - 1][1] = 'active'
if self.page == 1:
before = []
else:
before = [
['', 'prev', self.page - 1],
]
if self.page == pMax:
after = []
else:
after = [
['', 'next', self.page + 1],
]
self.pagn = before + pagn + after
else:
self.pagn = []
return self.pagn
def delIt(self):
blogdb = get_db()
cur = blogdb.cursor()
cur.execute('DELETE FROM blog WHERE id = ? ', (self.id, ))
cur.execute('DELETE FROM tag WHERE blog = ? ', (self.id, ))
cur.execute('DELETE FROM comm WHERE blog = ? ', (self.id, ))
blogdb.commit()
def getAl(self):
try:
blogdb = get_db()
cur = blogdb.cursor()
if self.section:
cur.execute(
'SELECT id,name,img,quote,content,section,date FROM posts WHERE owner = 0 and section = %s ORDER BY hot DESC LIMIT 10 OFFSET %s',
(
self.section,
self.offset,
))
else:
cur.execute(
'SELECT id,name,img,quote,content,section,date FROM posts WHERE owner = 0 ORDER BY hot DESC LIMIT 10 OFFSET %s',
(self.offset, ))
curPosts = cur.fetchall()
results = []
for curPost in curPosts:
temp = Post(curPost[0], curPost[1], curPost[2], curPost[3],
curPost[4], curPost[5], curPost[6])
temp.getRep()
results.append(temp)
self.results = results
if self.section:
cur.execute(
'SELECT count(*) FROM posts WHERE owner = 0 and section = %s;',
(self.section, ))
else:
cur.execute('SELECT count(*) FROM posts where owner = 0;')
pMax = cur.fetchall()
pMax = (int(pMax[0][0]) + 9) / 10
self.pagn = createPagn(self.page, pMax)
return True
except:
return False
def getIt(self):
blogdb = get_db()
cur = blogdb.cursor()
cur.execute(
' SELECT content, date, author, id, reply FROM comm WHERE blog = ? ORDER BY id DESC',
(self.id, ))
temp = cur.fetchall()
def preRep(c):
c = list(c) + ['']
if c[4]:
c[5] = '1'
else:
c[4] = c[3]
return c
temp = map(preRep, list(temp))
def coSort(x, y):
if x[4] < y[4]:
return 1
else:
return -1
temp.sort(coSort)
self.cl = temp
def update(self, title, tag, img, file, content):
abstract = abstr(content, img)
tags = (tag or '').replace('，', ',')
blogdb = get_db()
cur = blogdb.cursor()
if self.id:
cur.execute(
'UPDATE blog SET title = ? ,content = ?,abstract = ?,tag = ? ,file = ? WHERE ID = ?;',
(title, content, abstract, tags, file, self.id))
else:
cur.execute(
'insert into blog (title,tag,file,abstract,content) values (?, ?, ?, ?, ?)',
(title, tags, file, abstract, content))
cur.execute('select id from blog order by id desc limit 1')
blog = cur.fetchall()
self.id = blog[0][0]
blogdb.commit()
cur.execute('delete from tag where blog = ?', (self.id, ))
if tags:
tags = tags.split(',')
else:
tags = []
for tag in tags:
cur.execute('insert into tag (tag, blog) values (?, ?)',
(tag, self.id))
blogdb.commit()
def __init__(self):
blogdb = get_db()
cur = blogdb.cursor()
cur.execute('SELECT tag from tag order by id')
temp = cur.fetchall()
temp = list(set(temp))
tags = ','.join('%s' % id for id in temp)
self.tags = tags
def dele(self, tid):
blogdb = get_db()
cur = blogdb.cursor()
cur.execute('DELETE FROM `posts` where `id` = %s or `owner` = %s;', (
tid,
tid,
))
return 0
def getPg(self, pid):
blogdb = get_db()
cur = blogdb.cursor()
cur.execute('SELECT count(*) FROM posts where owner = %s and id < %s;',
(self.id, pid))
pg = cur.fetchall()
pg = (int(pg[0][0]) + 10) / 10
return pg
def insert(self, content, author, reply):
author = author or u'访客'
reply = reply or None
blogdb = get_db()
cur = blogdb.cursor()
cur.execute(
'INSERT into comm (content,author,blog,reply) values(?,?,?,?)',
(content, author, self.id, reply))
blogdb.commit()
def getNew(self):
blogdb = get_db()
cur = blogdb.cursor()
cur.execute(
' SELECT content, date, author, id, blog FROM comm ORDER BY id DESC LIMIT 8 '
)
temp = cur.fetchall()
self.cl = temp
return self.cl
def getEdit(self):
blogdb = get_db()
cur = blogdb.cursor()
cur.execute('SELECT title,content,tag from blog where id = ?',
(self.id, ))
content = cur.fetchall()[0]
self.title = content[0]
self.content = content[1]
self.tag = content[2]
def getNew(self):
blogdb = get_db()
cur = blogdb.cursor()
cur.execute(
'SELECT content,date,author,id,blog FROM comm order by id DESC LIMIT 12'
)
tmp = cur.fetchall()
self.cList = tmp
return self.cList
def __init__(self):
blogdb = get_db()
cur = blogdb.cursor()
cur.execute('SELECT id,content from info order by id')
temp = cur.fetchall()
self.title = temp[4][1]
self.subtitle = temp[3][1]
self.password = temp[2][1]
self.sidebar = temp[1][1]
self.tags = temp[0][1]
self.cate = dict(temp[6:])
def getRep(self):
blogdb = get_db()
cur = blogdb.cursor()
cur.execute(
'SELECT id,name,img,content,date FROM posts WHERE owner = %s ORDER BY id LIMIT 5',
(self.id, ))
self.reply = cur.fetchall()
cur.execute('SELECT count(*) FROM posts where owner = %s;',
(self.id, ))
pMax = cur.fetchall()
self.pagn = pMax[0][0]
return True
def getLen(self):
blogdb = get_db()
cur = blogdb.cursor()
if self.method == 'file':
cur.execute('SELECT count(*) from blog where file=?;',
(self.key, ))
elif self.method == 'tag':
cur.execute('SELECT count(*) FROM tag WHERE tag=?;', (self.key, ))
else:
cur.execute('SELECT count(*) FROM blog;')
rawlen = cur.fetchall()
rawlen = int(rawlen[0][0])
self.len = round((rawlen + 7) / 8) or 1
return self.len
def getArti(self):
blogdb = get_db()
cur = blogdb.cursor()
cur.execute(
'SELECT title, date, content, tag, abstract from blog where id = ?',
(self.id, ))
arti = cur.fetchall()[0]
self.title = arti[0]
self.date = arti[1]
self.content = arti[2]
self.tag = arti[3]
self.abstract = arti[4][:-17]
com = comment(self.id)
self.comList = com.commList()
def config(self, title='', subtitle='', sidebar='', tags=''):
blogdb = get_db()
cur = blogdb.cursor()
if title:
cur.execute('UPDATE info SET content = ? where id = -1', (title, ))
if subtitle:
cur.execute('UPDATE info SET content = ? where id = -2',
(subtitle, ))
if sidebar:
cur.execute('UPDATE info SET content = ? where id = -4',
(sidebar, ))
if tags:
cur.execute('UPDATE info SET content = ? where id = -5', (tags, ))
blogdb.commit()
def getIt(self):
blogdb = get_db()
cur = blogdb.cursor()
cur.execute(
'SELECT title, date, content, tag, abstract, file,img from blog where id = ?',
(self.id, ))
arti = cur.fetchall()[0]
self.title = arti[0]
self.date = arti[1]
if hasattr(self.date, 'strftime'):
self.date = self.date.strftime("%Y-%m-%d %H:%M:?")
self.content = arti[2]
self.tag = arti[3] or ''
self.abstract = arti[4]
self.file = arti[5]
self.img = arti[6] or ''
def newPost(self, author, img, quote, content, section, ip, owner):
blogdb = get_db()
cur = blogdb.cursor()
cur.execute(
'insert into posts (name,img,quote,content,section,ip,owner,hot) values (%s, %s, %s, %s, %s, %s, %s,CURRENT_TIMESTAMP)',
(author, (img or ""), (quote or ""), content, section, ip, owner))
if section:
cur.execute('select @@IDENTITY')
blog = cur.fetchall()[0][0]
blogdb.commit()
return blog
else:
cur.execute(
'update posts set hot = CURRENT_TIMESTAMP where id = %s',
(owner, ))
return owner
def commList(self):
try:
blogdb = get_db()
cur = blogdb.cursor()
#按照id降序排列DESC 升序排列ASC order by
cur.execute(
'SELECT content,date,author,id,reply from comm where blog=? order by id DESC',
(self.id, ))
tmp = cur.fetchall()
tmp = sorted(tmp, key=lambda x: (x[3] or x[4]))
Listlen = len(tmp)
for i in range(Listlen):
tmparr = list(tmp[i])
tmparr[4] = tmparr[4] or tmparr[3]
diff = tmparr[4] - tmparr[3]
diff = diff or ''
tmparr[4] = diff and u're'
if tmparr[4] == '':
tmparr[4] = tmparr[3]
tmp[i] = tuple(tmparr)
# Listlen = len(tmp)
# for i in range(Listlen):
# tmparr = list(tmp[i])+['']
# if tmparr[4]:
# tmparr[5]='1'
# else :
# tmparr[4]=tmparr[3]
# tmp[i] = tuple(tmparr)
# tmp = sorted(tmp,key=lambda x: x[4])
print(tmp)
self.cList = tmp
# def coVeri(x):
# x[4] = x[4] or x[3]
# diff = x[4]-x[3]
# diff = diff or ''
# x[4] = diff and u're'
# return x
# self.cList = map(coVeri,tmp)
###
except:
self.cList = []
finally:
#print(self.cList)
return self.cList
def setPwd(self, old, new):
import hashlib
m = hashlib.md5()
m.update(old)
m.update(m.hexdigest() + '1396')
if m.hexdigest() == self.password:
m = hashlib.md5()
m.update(new)
m.update(m.hexdigest() + '1396')
blogdb = get_db()
cur = blogdb.cursor()
cur.execute('UPDATE info SET content = ? where id = -3',
(m.hexdigest(), ))
blogdb.commit()
return 'Success'
else:
return "Couldn't match"
def setCate(self, oldId, newId, content):
blogdb = get_db()
cur = blogdb.cursor()
try:
if newId < 1:
cur.execute('delete from info where id = ?', (oldId, ))
cur.execute('UPDATE blog SET file=0 where file = ?', (oldId, ))
blogdb.commit()
if oldId == 0:
cur.execute('insert into info (id,content) values (?, ?)',
(newId, content))
else:
cur.execute('UPDATE info SET id=?,content=? where id = ?',
(newId, content, oldId))
cur.execute('UPDATE blog SET file=? where file = ?',
(newId, oldId))
blogdb.commit()
return 'Success'
except:
return 'Database Error'
def getExit(self):
# 必加 相当于和数据库连接
blogdb = get_db()
cur = blogdb.cursor()
#
cur.execute(
'SELECT id, title, abstract, tag, date, file FROM blog where id = ?',
(self.id, ))
exit = cur.fetchall()[0]
self.id = exit[0]
self.title = exit[1]
self.abstract = exit[2]
self.tag = exit[3]
date = exit[4]
if hasattr(date, 'strftime'):
self.date = date.strftime('%x')
else:
self.date = date[5:7] + '/' + date[8:10] + '/' + date[:4]
file = exit[5]
fileDict = {1: '闲聊', 3: '编程', 4: '杂谈', 5: '转载'}
self.file = fileDict[file]
return self
def getIt(self, pid=1):
blogdb = get_db()
cur = blogdb.cursor()
cur.execute(
'SELECT name,img,quote,content,section,date FROM posts WHERE id = %s',
(self.id, ))
arti = cur.fetchall()[0]
self.name = arti[0]
self.img = arti[1]
self.quote = arti[2]
self.content = arti[3]
self.section = arti[4]
self.date = arti[5]
cur.execute(
'SELECT id,name,img,content,date FROM posts WHERE owner = %s ORDER BY id LIMIT %s OFFSET %s',
(self.id, 10, (pid - 1) * 10))
self.reply = cur.fetchall()
cur.execute('SELECT count(*) FROM posts where owner = %s;',
(self.id, ))
pMax = cur.fetchall()
pMax = (int(pMax[0][0]) + 9) / 10
self.pagn = createPagn(pid, pMax)
return True
def getAl(self):
blogdb = get_db()
cur = blogdb.cursor()
if self.method == 'file':
cur.execute(
' SELECT id FROM blog WHERE file = ? ORDER BY id DESC LIMIT 8 OFFSET ?',
(
self.key,
self.offset,
))
elif self.method == 'tag':
cur.execute('select blog from tag where tag = ? LIMIT 8 OFFSET ?',
(
self.key,
self.offset,
))
else:
cur.execute(
' SELECT id FROM blog where file>0 ORDER BY id DESC LIMIT 8 OFFSET ?',
(self.offset, ))
al = cur.fetchall()
al = map(lambda x: int(x[0]), al)
al.sort(reverse=True)
self.al = al
def delArti(self):
blogdb = get_db()
cur = blogdb.cursor()
cur.execute('DELETE FROM blog where id = ?', (self.id, ))
cur.execute('DELETE from tag where id = ?', (self.id, ))
cur.execute("DELETE from comm where id = ?", (self.id, ))
def exper1():
cur = get_db().cursor()
cur.execute(
'''SELECT TAG, COUNT(*) FROM TAG GROUP BY TAG ORDER BY ID DESC;''')
print(cur.fetchall())
def delete(self):
blogdb = get_db()
cur = blogdb.cursor()
cur.execute('DELETE from comm where id=?', (self.id, ))
blogdb.commit()
