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()