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