def CreatSongCommentTable(self, songid):
connectObj = db_util.ConnectToMysql()
connect = connectObj.getConnect()
cursor = connect.cursor()
sql_creatTable = """create table if not exists songid_%s
(
id bigint not null auto_increment,
content varchar(500),
username varchar(255),
userid varchar(255),
zan varchar(255),
onpage varchar(255),
time varchar(255),
primary key (id)
) DEFAULT CHARSET=utf8mb4 """ % songid
try:
cursor.execute(sql_creatTable)
connect.commit()
except Exception as e:
print("建表出错", '错误是:', e)
return 0
else:
return 1
finally:
cursor.close()
cursor.close()
def InsertComment(self, comment, songid):
connectObj = db_util.ConnectToMysql()
connect = connectObj.getConnect()
cursor = connect.cursor()
content = comment.content
username = comment.username
userid = comment.userid
zan = comment.zan
onpage = comment.onpage
time = comment.time
content = content.replace("'", "\\\'")
content = content.replace('"', '\\\"') #防止因为含有【"】【'】导致sql语句执行出错
username = username.replace("'", "\\\'")
username = username.replace('"', '\\\"') #防止因为含有【"】【'】导致sql语句执行出错
sql = "INSERT INTO songid_%s (content,username,userid,zan,onpage,time)VALUE ('%s','%s','%s', '%s','%s','%s')"
data = (songid, content, username, userid, zan, onpage, time)
try:
cursor.execute(sql % data)
connect.commit()
except Exception as e:
print("插入评论出错", '错误是:', e)
return 0
else:
return 1
finally:
cursor.close()
connect.close()
def GetlastPage(self, songid):
connectObj = db_util.ConnectToMysql()
connect = connectObj.getConnect()
cursor = connect.cursor()
sql = "select lastpage from songtotal WHERE songid = '%s'" % songid
try:
cursor.execute(sql)
connect.commit()
result = cursor.fetchone()[0]
return result
except Exception as e:
print("获取lastpage出错", "歌曲id", songid, '错误是:', e)
finally:
cursor.close()
connect.close()
def ShowAllSong(self, listenerid):
connectObj = db_util.ConnectToMysql()
connect = connectObj.getConnect()
cursor = connect.cursor()
sql = "select songid , songname from songtotal WHERE listenerid = '%s'" % listenerid
try:
cursor.execute(sql)
connect.commit()
except Exception as e:
print("展示某人所有歌曲", '错误是:', e)
return 0, 0
else:
return 1, cursor.fetchall()
finally:
cursor.close()
connect.close()
def GetNum(self, listenerid):
connectObj = db_util.ConnectToMysql()
connect = connectObj.getConnect()
cursor = connect.cursor()
sql = "select count(*) as value from songtotal WHERE listenerid = '%s'" % listenerid
try:
cursor.execute(sql)
connect.commit()
except Exception as e:
print("查询", listenerid, "已在库中歌曲总数出错", '错误是:', e)
else:
result = cursor.fetchone()[0]
return result
finally:
cursor.close()
connect.close()
def UpdateFinish(self, songid):
connectObj = db_util.ConnectToMysql()
connect = connectObj.getConnect()
cursor = connect.cursor()
sql = "UPDATE songtotal SET finish = 1 WHERE songid ='%s';" % songid
try:
cursor.execute(sql)
connect.commit()
except Exception as e:
print("更新finsih出错", '错误是:', e)
return 0
else:
return 1
finally:
cursor.close()
connect.close()
def addListener(self, newListener):
connectObj = db_util.ConnectToMysql()
connect = connectObj.getConnect()
cursor = connect.cursor()
sql = "insert into listener (listenerid)VALUE ('%s');"
data = (newListener.getid())
try:
cursor.execute(sql % data)
connect.commit()
except Exception as e:
print("插入newListener出错", '错误是:', e)
return 0
else:
return 1
finally:
cursor.close()
connect.close()
def SelectAllListenerId(self):
connectObj = db_util.ConnectToMysql()
connect = connectObj.getConnect()
cursor = connect.cursor()
sql = "select listenerid from listener"
try:
cursor.execute(sql)
connect.commit()
except Exception as e:
print("查看库中已有全部听众id出错", '错误是:', e)
return 0, 0
else:
return 1, cursor.fetchall()
finally:
cursor.close()
connect.close()
def verify(self, songid, listenerid):
connectObj = db_util.ConnectToMysql()
connect = connectObj.getConnect()
cursor = connect.cursor()
sql = "select count(*) from songtotal WHERE songid = '%s' AND listenerid = '%s'"
data = (songid, listenerid)
try:
cursor.execute(sql % data)
connect.commit()
except Exception as e:
print("查重出错, id: ", listenerid, "已在库中歌曲总数出错", '错误是:', e)
else:
result = cursor.fetchone()[0]
return result #返回这首歌在库中查到的次数
finally:
cursor.close()
connect.close()
def UpdateLastPage(self, songid, lastpage):
connectObj = db_util.ConnectToMysql()
connect = connectObj.getConnect()
cursor = connect.cursor()
sql = "UPDATE songtotal SET lastpage = %s WHERE songid ='%s';"
data = (lastpage, songid)
try:
cursor.execute(sql % data)
connect.commit()
except Exception as e:
print("更新lastpage出错", '错误是:', e)
return 0
else:
return 1
finally:
cursor.close()
connect.close()
def GetMaxID(self, songid):
connectObj = db_util.ConnectToMysql()
connect = connectObj.getConnect()
cursor = connect.cursor()
sql = "select max(id) from songid_%s"
data = (songid)
try:
cursor.execute(sql % data)
connect.commit()
except Exception as e:
print("获取max id出错", '错误是:', e)
return 0
else:
return cursor.fetchone()[0]
finally:
cursor.close()
connect.close()
def GetCommentID(self, songid, userid, content, time):
connectObj = db_util.ConnectToMysql()
connect = connectObj.getConnect()
cursor = connect.cursor()
#SELECT id FROM songid_28854182 WHERE username = '******' AND content = '唱不了啊[大哭]' AND time = '2018-07-29 18:53:46'
sql = "SELECT id from songid_%s WHERE userid = '%s' AND content = '%s' AND time = '%s'"
data = (songid, userid, content, time)
try:
cursor.execute(sql % data)
connect.commit()
except Exception as e:
print("获取最新评论主键id出错", '错误是:', e)
return 0
else:
result = cursor.fetchall()
length = len(result)
return result[length - 1][0]
finally:
cursor.close()
connect.close()
def addSong(self, song):
songid = song.getsongid()
listenerid = song.getlistenerid()
MyVerify = self.verify(songid, listenerid)
if MyVerify > 0:
return 1
elif MyVerify == 0:
connectObj = db_util.ConnectToMysql()
connect = connectObj.getConnect()
cursor = connect.cursor()
lastage = 1
finish = 0
ok = 0
songname = song.getsongname()
num = self.GetNum(song.listenerid)
songname = songname.replace("'", "\\\'")
songname = songname.replace('"', '\\\"') #防止因为含有【"】【'】导致sql语句执行出错
sql = "INSERT INTO songtotal (songid,lastpage,finish,ok,listenerid,num,songname)VALUES ('%s','%s','%s','%s','%s','%s','%s');"
data = (songid, lastage, finish, ok, listenerid, num, songname)
try:
cursor.execute(sql % data)
connect.commit()
songid = '%-20s' % songid
listenerid = '%-20s' % listenerid
print("成功插入歌曲， id ：", songid, " 听众id：", listenerid,
"歌名：", songname)
except Exception as e:
print("插入song出错", '错误是:', e)
return 0
else:
return 1
finally:
cursor.close()
connect.close()
def GetCommentbyID(self, songid, id):
connectObj = db_util.ConnectToMysql()
connect = connectObj.getConnect()
cursor = connect.cursor()
#SELECT * FROM `songid_28854182` WHERE id = 10058
sql = "SELECT * FROM songid_%s WHERE id = '%s'"
data = (songid, id)
try:
# print(sql % data)
cursor.execute(sql % data)
connect.commit()
except Exception as e:
print("根据id获取评论出错", '错误是:', e)
return 0
else:
return cursor.fetchone()
finally:
cursor.close()
connect.close()
# b = CommentDao()
# res = b.GetCommentbyID(28854182,10058)
# print(res)