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