def bbs_sum_reply(self):
sql = ' select count(1) from bbs_bbs where parentid in ( \
select bbsid from bbs_bbs where parentid in ( \
select bbsid from bbs_bbs where bbsid = ' + str(self.__bbsid) + ') \
) \
UNION ALL \
select count(1) from bbs_bbs where parentid in ( \
select bbsid from bbs_bbs where bbsid = ' + str(self.__bbsid) + ')'
result = connDB.query(sql)
return result
def register(username, userpass):
'''
flag=1 可以注册
flag=0 不可以注册
'''
# * 判断数据库中是否有相同的username
query_sql = 'select username from bbs_user where username= \'' + username + '\''
sql_return = connDB.query(query_sql)
# 如果数据库中有要注册的username，返回错误信息
if sql_return:
return 0
# 插入数据
insert_sql = 'insert into bbs_user (username, userpass) values (\'' + username + '\', \'' + userpass + '\')'
connDB.insert(insert_sql)
return 1
def login(username, userpass):
'''
flag=0 not find user
flag=1 userpass error
flag=2 valid success
'''
sql = 'select username, userpass from bbs_user where username= \'' + username + '\''
sql_return = connDB.query(sql)
# 如果存在用户
if len(sql_return) > 0:
if username == sql_return[0][0] and userpass == sql_return[0][1]:
# 如果用户名、用户密码正确
return 2
# 如果用户名or密码不正确
return 1
# 如果不存在用户
return 0
def query_board(self):
query_board_sql = 'select * from board;'
result = connDB.query(query_board_sql)
return result
def query_all_user(self):
sql = 'select * from bbs_user'
result = connDB.query(sql)
return result
def query_all_by_username(self):
sql = 'select * from bbs_user where username = \'' + self.__username + '\';'
result = connDB.query(sql)
return result
def query_all_by_id(self):
sql = 'select * from bbs_user where id = ' + str(self.__id) + ';'
result = connDB.query(sql)
return result
def order_by_dateandtime(self):
sql = 'select * from bbs_bbs \
where bbstitle is not null \
ORDER BY dateandtime DESC, bbsid DESC'
result = connDB.query(sql)
return result
def clicking_ranking(self):
sql = 'select * from bbs_bbs \
where bbstitle is not null \
ORDER BY bbsclick desc'
result = connDB.query(sql)
return result
def query_by_bbsid(self):
sql = 'select * from bbs_bbs where bbsid = ' + str(self.__bbsid) + ';'
result = connDB.query(sql)
return result
def query_all_bbs(self):
sql = 'select * from bbs_bbs where bbstitle is not null; '
result = connDB.query(sql)
return result
def get_board_id_and_name(self):
sql = 'select boardid, boardname from board'
result = connDB.query(sql)
print(result)
return result
def get_boardid_dict(self):
sql = 'select a.boardid, a.boardname, b.topics_name \
from (select boardid, boardname from board) as a INNER JOIN (select boardid, topics_name from topics) as b ON a.boardid = b.boardid'
result = connDB.query(sql)
print(result)
return result