def addUserCategory(self, user_id, category_name, isDelete):
#USERID INT PRIMARY KEY,USERNAME VARCHAR(20),PASSWORD VARCHAR(32),REGTIME DATETIME,DELFLAG INT
db = DBUtil()
sql = 'insert into `USER_CATEGORY` VALUE(id,%d,"%s",%d)' % (
user_id, category_name, isDelete)
id = db.execute_insert(sql)
return id
def addUserDetail(self, user_login_id, gender, img_path, birthday,
province, city, marriage):
#
db = DBUtil()
db.execute_insert(
'insert into `USER_DETAILS` VALUE(id,%s,%s,%s,%s,%s,%s,%s)',
(user_login_id, gender, img_path, birthday, province, city,
marriage))
def getUserImgById(self, user_login_id):
db = DBUtil()
data = db.execute("select * from `user_img` where user_login_id=%s" %
(user_login_id))
if data:
return True
else:
return False
def getUserCategoryByName(self, user_login_id, categoryName):
db = DBUtil()
sql = "SELECT ID,CATEGORY_NAME FROM `USER_CATEGORY` WHERE USER_LOGIN_ID =%s and CATEGORY_NAME='%s' AND IS_DELETE=0" % (
user_login_id, categoryName)
data = db.execute(sql)
if data:
return True
else:
return False
def getUserDailyDetailById(self,category_id,daily_id):
db = DBUtil()
sql = 'SELECT * FROM `USER_DAILY_DETAILS` WHERE USER_CATEGORY_ID=%d AND DAILY_ID=%d'%(category_id,daily_id)
data = db.execute(sql)
if data:
# 存在数据
return True
else:
# 不存在数据
return False
def getCategoryList(self):
# SELECT * FROM `CATEGORY` c LEFT JOIN `daily` d ON c.`ID`=d.`category_id`
db = DBUtil()
results = db.execute('SELECT id,name FROM `CATEGORY`')
dataList = []
for row in results:
dict = {}
dict['id'] = row[0]
dict['name'] = row[1]
dataList.append(dict)
return dataList
def getArchivesDate(self):
db = DBUtil()
results = db.execute('SELECT COUNT(*) AS COUNT, DATE_FORMAT( create_time, \'%Y-%m\') AS create_time FROM `DAILY` GROUP BY DATE_FORMAT( create_time, \'%Y-%m\') ORDER BY create_time DESC')
dataList = []
for row in results:
dict = {}
dict['count'] = row[0]
dict['date'] = row[1]
dict['year'] =row[1].split('-')[0]
dict['month'] =row[1].split('-')[1]
dataList.append(dict)
return dataList
def searchDailyByName(self,username):
db = DBUtil()
data = db.execute_select("SELECT * FROM `USER_LOGIN` WHERE USERNAME=%s",username)
#得到元组，转成字典
dict = {}
if data:
data = data[0]
dict['userid'] = data[0]
dict['username'] = data[1]
dict['password'] = data[2]
dict['regtime'] = data[3]
dict['delflag'] = data[4]
return dict
def getUserImg(self, user_login_id):
db = DBUtil()
data = db.execute(
"select img_path,user_login_id from `user_img` where user_login_id=%s"
% (user_login_id))
img_data = {}
if data:
row = data[0]
img_data['img_path'] = '/' + row[0]
img_data['user_login_id'] = row[1]
else:
return False
return img_data
def getUserCategory(self, user_id):
db = DBUtil()
sql = "SELECT ID,CATEGORY_NAME FROM `USER_CATEGORY` WHERE USER_LOGIN_ID =%s AND IS_DELETE=0" % user_id
data = db.execute(sql)
user_category_list = []
if data:
for row in data:
dict = {}
dict['id'] = row[0]
dict['name'] = row[1]
user_category_list.append(dict)
print(user_category_list)
return user_category_list
def getRecentDaily(self):
db = DBUtil()
results = db.execute('SELECT * FROM `DAILY` ORDER BY create_time DESC LIMIT 5')
dataList = []
for row in results:
dict = {}
dict['id'] = row[0]
dict['title'] = row[1]
dict['body'] = row[2]
dict['create_time'] = row[3]
dict['user_id'] = row[4]
dict['user_name'] = row[5]
dataList.append(dict)
return dataList
def getAllDaily(self):
db = DBUtil()
results = db.execute('SELECT d.`id`,d.`title`,d.`body`,d.`create_time`,d.`user_id`,u.`username` FROM `DAILY` d INNER JOIN `USER_LOGIN` u ON d.`user_id`=u.`id`')
dataList = []
for row in results:
dict = {}
dict['id'] = row[0]
dict['title'] = row[1]
dict['body'] = row[2]
dict['create_time'] = row[3]
dict['user_id'] = row[4]
dict['user_name'] = row[5]
dataList.append(dict)
return dataList
def getArchivesDaily(self,year,month):
db = DBUtil()
search_sql = "SELECT * FROM `DAILY` WHERE MONTH(create_time)='%s' AND YEAR(create_time)='%s'"%(month,year)
print(search_sql)
results = db.execute('SELECT d.`id`,d.`title`,d.`body`,d.`create_time`,d.`user_id`,u.`username` FROM ('+search_sql+') as d INNER JOIN `USER_LOGIN` u ON d.`user_id`=u.`id`')
dataList = []
for row in results:
dict = {}
dict['id'] = row[0]
dict['title'] = row[1]
dict['body'] = row[2]
dict['create_time'] = row[3]
dict['user_id'] = row[4]
dict['user_name'] = row[5]
dataList.append(dict)
return dataList
def getCategoryDailyList(self,id):
db = DBUtil()
search_sql = "SELECT * FROM `DAILY` WHERE category_id='%s' " % id
print(search_sql)
results = db.execute(
'SELECT d.`id`,d.`title`,d.`body`,d.`create_time`,d.`user_id`,u.`username` FROM (' + search_sql + ') as d INNER JOIN `user_login` u ON d.`user_id`=u.`id`')
dataList = []
for row in results:
dict = {}
dict['id'] = row[0]
dict['title'] = row[1]
dict['body'] = row[2]
dict['create_time'] = row[3]
dict['user_id'] = row[4]
dict['user_name'] = row[5]
dataList.append(dict)
return dataList
def updateUserDetail(self, user_login_id, gender, img_path, birthday,
province, city, marriage, name):
db = DBUtil()
db.execute(
"UPDATE `user_details` SET gender=%s,img_path='%s',birthday='%s',province=%s,city=%s,marriage=%s WHERE user_login_id = %s"
% (gender, img_path, birthday, province, city, marriage,
user_login_id))
db = DBUtil()
db.execute("UPDATE `user_login` SET USERNAME ='******' WHERE id=%s" %
(name, user_login_id))
class CommentDao:
def __init__(self):
self.db = DBUtil()
def addComment(self, content, create_time, daily_id, user_id):
db = DBUtil()
print(content, create_time, daily_id, user_id)
db.execute("INSERT INTO `COMMENT` VALUE(ID,'%s','%s',%d,%d)"%(content, create_time, daily_id, user_id))
def getAllComments(self):
db = DBUtil()
pass
def getAllCommentByDailyId(self, id):
db = DBUtil()
data = self.db.execute_select(
"SELECT c.`ID`,c.`CONTENT`,c.`create_time`,c.`daily_id`,c.`user_id`,u.`USERNAME` FROM `comment` AS c LEFT JOIN `user_login` AS u ON c.`USER_ID`=u.`ID` WHERE c.`daily_id` = %s",
id)
commentList = []
if data:
for row in data:
dict = {}
dict['id'] = row[0]
dict['content'] = row[1]
dict['create_time'] = row[2]
dict['daily_id'] = row[3]
dict['user_id'] = row[4]
dict['user_name'] = row[5]
commentList.append(dict)
return commentList
def getLastComment(self, daily_id,user_id):
db = DBUtil()
row = self.db.execute_select("SELECT c.`ID`,c.`CONTENT`,c.`create_time`,c.`daily_id`,c.`user_id`,u.`USERNAME` FROM `comment` AS c LEFT JOIN `user_login` AS u ON c.`USER_ID`=u.`ID` WHERE c.`daily_id` = %s AND c.`USER_ID`=%s ORDER BY CREATE_TIME DESC LIMIT 1",
(daily_id,user_id))[0]
print(row)
dict = {}
dict['id'] = row[0]
dict['content'] = row[1]
dict['create_time'] = json.dumps(row[2], cls=CJsonEncoder)
dict['daily_id'] = row[3]
dict['user_id'] = row[4]
dict['user_name'] = row[5]
print(dict)
return dict
def search_daily(self,str):
db = DBUtil()
# str = like '%str%'
sql = "SELECT d.`id`,d.`title`,d.`body`,d.`create_time`,d.`user_id`,u.`username` FROM (SELECT * FROM daily WHERE title LIKE \'%"+str+"%\' or body LIKE \'%"+str+"%\' ) AS d INNER JOIN `user_login` u ON d.`user_id`=u.`id`"
results = db.execute(sql)
dataList = []
for row in results:
dict = {}
dict['id'] = row[0]
dict['title'] = row[1]
dict['body'] = row[2]
# print(json.dumps(row[3], cls=CJsonEncoder))
# dict['create_time'] =json.dumps(row[3], cls=CJsonEncoder).replace("\"","")
dict['create_time'] =row[3]
dict['user_id'] = row[4]
dict['user_name'] = row[5]
dataList.append(dict)
return dataList
def getUserDetail(self, user_login_id):
print(user_login_id)
db = DBUtil()
data = db.execute_select(
'SELECT USER_LOGIN_ID,gender,IMG_PATH,BIRTHDAY,PROVINCE,CITY,MARRIAGE,l.`USERNAME` FROM `USER_DETAILS` d LEFT JOIN `USER_LOGIN` l ON d.`USER_LOGIN_ID`=l.`ID` WHERE d.`USER_LOGIN_ID`=%s',
user_login_id)
if data:
row = data[0]
user_details = {}
user_details['user_login_id'] = row[0]
user_details['gender'] = row[1]
user_details['img_path'] = row[2]
user_details['birthday'] = row[3]
user_details['province'] = row[4]
user_details['city'] = row[5]
user_details['marriage'] = row[6]
user_details['username'] = row[7]
db2 = DBUtil()
region = db2.execute_select(
"SELECT p.`code` p_code,c.`code` c_code,p.`name` p_name,c.`name` c_name FROM `province` p INNER JOIN `city` c ON c.`province_code` = p.`code` WHERE c.`code` = %s",
user_details['city'])
distict = {}
if region:
temp = region[0]
distict['p_code'] = temp[0]
distict['c_code'] = temp[1]
distict['p_name'] = temp[2]
distict['c_name'] = temp[3]
print('************')
print(user_details)
print('************')
return (user_details, distict)
else:
return False
def getDailyById(self,id):
db = DBUtil()
data = self.db.execute_select("SELECT d.`id`,d.`title`,d.`body`,d.`create_time`,d.`user_id`,u.`username` FROM (SELECT * FROM `DAILY` WHERE id=%s) AS d INNER JOIN `user_login` u ON d.`user_id`=u.`id`",id)
dict = {}
if data:
row = data[0]
dict['id'] = row[0]
dict['title'] = row[1]
dict['body'] = row[2]
dict['create_time'] = row[3]
dict['user_id'] = row[4]
dict['user_name'] = row[5]
return dict
def getLastComment(self, daily_id,user_id):
db = DBUtil()
row = self.db.execute_select("SELECT c.`ID`,c.`CONTENT`,c.`create_time`,c.`daily_id`,c.`user_id`,u.`USERNAME` FROM `comment` AS c LEFT JOIN `user_login` AS u ON c.`USER_ID`=u.`ID` WHERE c.`daily_id` = %s AND c.`USER_ID`=%s ORDER BY CREATE_TIME DESC LIMIT 1",
(daily_id,user_id))[0]
print(row)
dict = {}
dict['id'] = row[0]
dict['content'] = row[1]
dict['create_time'] = json.dumps(row[2], cls=CJsonEncoder)
dict['daily_id'] = row[3]
dict['user_id'] = row[4]
dict['user_name'] = row[5]
print(dict)
return dict
def getAllCommentByDailyId(self, id):
db = DBUtil()
data = self.db.execute_select(
"SELECT c.`ID`,c.`CONTENT`,c.`create_time`,c.`daily_id`,c.`user_id`,u.`USERNAME` FROM `comment` AS c LEFT JOIN `user_login` AS u ON c.`USER_ID`=u.`ID` WHERE c.`daily_id` = %s",
id)
commentList = []
if data:
for row in data:
dict = {}
dict['id'] = row[0]
dict['content'] = row[1]
dict['create_time'] = row[2]
dict['daily_id'] = row[3]
dict['user_id'] = row[4]
dict['user_name'] = row[5]
commentList.append(dict)
return commentList
def addUserImg(self, user_login_id, img_path):
db = DBUtil()
db.execute_insert('insert into user_img VALUE(id,%s,%s)',
(img_path, user_login_id))
def addDaily(self,title,body,create_time,category,user_id,modified_time,click):
#USERID INT PRIMARY KEY,USERNAME VARCHAR(20),PASSWORD VARCHAR(32),REGTIME DATETIME,DELFLAG INT
db = DBUtil()
print(type(category))
id = db.execute_insert('insert into `DAILY` VALUE(id,"%s","%s","%s","%s",%d,%d,%d)'%(title,body,create_time,modified_time,category,user_id,click))
return id
def updateUserImg(self, user_login_id, img_path):
db = DBUtil()
db.execute(
"update `user_img` set img_path='%s' where user_login_id=%s" %
(img_path, user_login_id))
def addUserTag(self,userid,daily_id,tag):
#USERID INT PRIMARY KEY,USERNAME VARCHAR(20),PASSWORD VARCHAR(32),REGTIME DATETIME,DELFLAG INT
db = DBUtil()
sql = 'insert into `USER_DAILY_TAGS` VALUE(id,%d,%d,"%s")'%(userid,daily_id,tag)
id = db.execute_insert(sql)
return id
def __init__(self):
self.db = DBUtil()
class DailyDao:
def __init__(self):
self.db = DBUtil()
def addDaily(self,title,body,create_time,category,user_id,modified_time,click):
#USERID INT PRIMARY KEY,USERNAME VARCHAR(20),PASSWORD VARCHAR(32),REGTIME DATETIME,DELFLAG INT
db = DBUtil()
print(type(category))
id = db.execute_insert('insert into `DAILY` VALUE(id,"%s","%s","%s","%s",%d,%d,%d)'%(title,body,create_time,modified_time,category,user_id,click))
return id
def getAllDaily(self):
db = DBUtil()
results = db.execute('SELECT d.`id`,d.`title`,d.`body`,d.`create_time`,d.`user_id`,u.`username` FROM `DAILY` d INNER JOIN `USER_LOGIN` u ON d.`user_id`=u.`id`')
dataList = []
for row in results:
dict = {}
dict['id'] = row[0]
dict['title'] = row[1]
dict['body'] = row[2]
dict['create_time'] = row[3]
dict['user_id'] = row[4]
dict['user_name'] = row[5]
dataList.append(dict)
return dataList
def getRecentDaily(self):
db = DBUtil()
results = db.execute('SELECT * FROM `DAILY` ORDER BY create_time DESC LIMIT 5')
dataList = []
for row in results:
dict = {}
dict['id'] = row[0]
dict['title'] = row[1]
dict['body'] = row[2]
dict['create_time'] = row[3]
dict['user_id'] = row[4]
dict['user_name'] = row[5]
dataList.append(dict)
return dataList
def getArchivesDate(self):
db = DBUtil()
results = db.execute('SELECT COUNT(*) AS COUNT, DATE_FORMAT( create_time, \'%Y-%m\') AS create_time FROM `DAILY` GROUP BY DATE_FORMAT( create_time, \'%Y-%m\') ORDER BY create_time DESC')
dataList = []
for row in results:
dict = {}
dict['count'] = row[0]
dict['date'] = row[1]
dict['year'] =row[1].split('-')[0]
dict['month'] =row[1].split('-')[1]
dataList.append(dict)
return dataList
#获取指定月份下的所有文章
def getArchivesDaily(self,year,month):
db = DBUtil()
search_sql = "SELECT * FROM `DAILY` WHERE MONTH(create_time)='%s' AND YEAR(create_time)='%s'"%(month,year)
print(search_sql)
results = db.execute('SELECT d.`id`,d.`title`,d.`body`,d.`create_time`,d.`user_id`,u.`username` FROM ('+search_sql+') as d INNER JOIN `USER_LOGIN` u ON d.`user_id`=u.`id`')
dataList = []
for row in results:
dict = {}
dict['id'] = row[0]
dict['title'] = row[1]
dict['body'] = row[2]
dict['create_time'] = row[3]
dict['user_id'] = row[4]
dict['user_name'] = row[5]
dataList.append(dict)
return dataList
#获取指定分类下的所有文章
def getCategoryDailyList(self,id):
db = DBUtil()
search_sql = "SELECT * FROM `DAILY` WHERE category_id='%s' " % id
print(search_sql)
results = db.execute(
'SELECT d.`id`,d.`title`,d.`body`,d.`create_time`,d.`user_id`,u.`username` FROM (' + search_sql + ') as d INNER JOIN `user_login` u ON d.`user_id`=u.`id`')
dataList = []
for row in results:
dict = {}
dict['id'] = row[0]
dict['title'] = row[1]
dict['body'] = row[2]
dict['create_time'] = row[3]
dict['user_id'] = row[4]
dict['user_name'] = row[5]
dataList.append(dict)
return dataList
#获取分类列表
def getCategoryList(self):
#SELECT * FROM `category` c LEFT JOIN `DAILY` d ON c.`ID`=d.`category_id`
db = DBUtil()
results = db.execute('SELECT * FROM `CATEGORY`')
dataList = []
for row in results:
dict = {}
dict['id'] = row[0]
dict['name'] = row[1]
dataList.append(dict)
return dataList
def getDailyById(self,id):
db = DBUtil()
data = self.db.execute_select("SELECT d.`id`,d.`title`,d.`body`,d.`create_time`,d.`user_id`,u.`username` FROM (SELECT * FROM `DAILY` WHERE id=%s) AS d INNER JOIN `user_login` u ON d.`user_id`=u.`id`",id)
dict = {}
if data:
row = data[0]
dict['id'] = row[0]
dict['title'] = row[1]
dict['body'] = row[2]
dict['create_time'] = row[3]
dict['user_id'] = row[4]
dict['user_name'] = row[5]
return dict
def searchDailyByName(self,username):
db = DBUtil()
data = db.execute_select("SELECT * FROM `USER_LOGIN` WHERE USERNAME=%s",username)
#得到元组，转成字典
dict = {}
if data:
data = data[0]
dict['userid'] = data[0]
dict['username'] = data[1]
dict['password'] = data[2]
dict['regtime'] = data[3]
dict['delflag'] = data[4]
return dict
def search_daily(self,str):
db = DBUtil()
# str = like '%str%'
sql = "SELECT d.`id`,d.`title`,d.`body`,d.`create_time`,d.`user_id`,u.`username` FROM (SELECT * FROM daily WHERE title LIKE \'%"+str+"%\' or body LIKE \'%"+str+"%\' ) AS d INNER JOIN `user_login` u ON d.`user_id`=u.`id`"
results = db.execute(sql)
dataList = []
for row in results:
dict = {}
dict['id'] = row[0]
dict['title'] = row[1]
dict['body'] = row[2]
# print(json.dumps(row[3], cls=CJsonEncoder))
# dict['create_time'] =json.dumps(row[3], cls=CJsonEncoder).replace("\"","")
dict['create_time'] =row[3]
dict['user_id'] = row[4]
dict['user_name'] = row[5]
dataList.append(dict)
return dataList
def getDailyIdByUser(self,user_login_id):
db = DBUtil()
sql = "select id from `DAILY` where user_id=%d"%user_login_id
print(sql)
results = db.execute(sql)
def getAllComments(self):
db = DBUtil()
pass
def addCategory(self, name):
# USERID INT PRIMARY KEY,USERNAME VARCHAR(20),PASSWORD VARCHAR(32),REGTIME DATETIME,DELFLAG INT
db = DBUtil()
db.execute_insert('insert into Category VALUE(id,%s)', (name))
def addComment(self, content, create_time, daily_id, user_id):
db = DBUtil()
print(content, create_time, daily_id, user_id)
db.execute("INSERT INTO `COMMENT` VALUE(ID,'%s','%s',%d,%d)"%(content, create_time, daily_id, user_id))