def fetchTodayWartPublishVideo():
db = dbHelper.database()
day_sql = "AND create_time >= date_format(NOW(),'%Y-%m-%d')"
sql = "SELECT * FROM videos WHERE qq !=0 AND publish_time is null %s" % (day_sql)
res = db.fetch(sql)
return res
def fetchNotPublishedAndQQ():
db = dbHelper.database()
sql = "SELECT * FROM videos WHERE qq!=0 AND publish_time is null"
res = db.fetch(sql)
if res:
return res
return []
def insertVideo(dic):
# TODO 登录验证
login = gfunc.getLoginNameForLocal()
if login[0] == False:
return False
vid = dic['vid']
db = dbHelper.database()
# 查询vid 是否存在 存在更新qq_create_time 不存在加入
isExist = db.fetch("select * from videos where vid = '%s'" % vid )
print('查询 数据库videos中 vid是否存在...')
if isExist:
# 更新
sql = "update videos set qq_create_time = '%s' where vid = '%s'" % (dic['qq_create_time'], vid)
db.update(sql)
print('videos table is exist update time')
else:
ls = list(dic)
sql = 'insert %s (' % 'videos' + ','.join(ls) + ') values (' +\
','.join(['%({})r'.format(field) for field in ls]) + ');'
sql = sql % dic
db.inset(sql)
print('inset video success')
# TODO
db.close()
def createTable():
db = dbHelper.database()
table = 'videos'
sql = """CREATE TABLE videos (
id int auto_increment primary key,
qq varchar(20),
title varchar(255),
url varchar(255),
alias varchar(255),
tags varchar(255),
first_class varchar(10),
second_class varchar(10),
qq_create_time varchar(50),
create_time DATETIME,
publish_time DATETIME,
aid varchar(100),
vid varchar(50),
pic varchar(255),
is_exist_local int(1),
local_path varchar(255),
fromId int(10),
ext varchar(255) )
"""
res = db.createTable(table, sql)
if res:
print('succ')
else:
print('fail')
def fetchTodayPublishedVideo(qq):
db = dbHelper.database()
day_sql = "AND publish_time >= date_format(NOW(),'%Y-%m-%d')"
sql = "SELECT * FROM videos WHERE qq = '%s' %s" % (qq, day_sql)
res = db.fetch(sql)
return res
def fetchVideo(qq, day=None, count=None):
db = dbHelper.database()
day_sql = ''
if day != None:
day_sql = "AND create_time >= date_format(NOW(),'%Y-%m-%d')"
sql = "SELECT * FROM videos WHERE qq = '%s' AND publish_time is null %s" % (qq, day_sql)
res = db.fetch(sql, limit=count)
return res
def createUser():
db = dbHelper.database()
table = 'user'
sql = """CREATE TABLE user(
userId int auto_increment primary key,
name varchar(100),
pwd varchar(100),
ext varchar(255)
)"""
db.createTable(table, sql)
def insertAnchor(name, uin, intr, vnum):
# TODO fromUserId
login = gfunc.getLoginNameForLocal()
if login[0] == False:
return False
db = dbHelper.database()
sql = "insert into anchor(name, uin, intr, vnum, fromUserId) values ('%s', '%s', '%s', '%d', '%s')" % (name, uin, intr, int(vnum), login[2])
flag = db.update(sql)
db.close()
return flag
def insetkdUser(qq, pwd, ext):
# TODO fromUserId
login = gfunc.getLoginNameForLocal()
if login[0] == False:
return False
db = dbHelper.database()
sql = "insert into kduser (qq, pwd, ext, fromUserId) values ('%s', '%s', '%s', '%s')" % (qq, pwd, ext, login[2])
flag = db.update(sql)
db.close()
return flag
def createTablekduser():
db = dbHelper.database()
table = 'kduser'
sql = """CREATE TABLE kduser(
id int auto_increment primary key,
qq varchar(50),
pwd varchar(50),
fromId int(10),
ext varchar(255)
)"""
res = db.createTable(table, sql)
def createTablekdvideo():
db = dbHelper.database()
table = 'kdvideo'
sql = """create table kdvideo (
id int auto_increment primary key,
url varchar(255),
title varchar(255),
vid varchar(50),
num int(30) default 0,
kid varchar(20),
fromUserId varchar(20)
)"""
db.createTable(table, sql)
def createBaseTable():
db = dbHelper.database()
table = 'anchor'
sql = """create table anchor (
aid int auto_increment primary key,
name varchar(100),
uin varchar(100),
intr varchar(255),
vnum int(10),
page int(10) default 1,
allpage int(10) default 1,
fromId int(10),
ext varchar(255) )"""
db.createTable(table, sql)
def updateVideoFromData(id, data, table=None):
db = dbHelper.database()
arrKey = data.keys()
valueStr = ''
for key in arrKey:
item = key + ' = ' + "'" +data[key] + "'"
if valueStr == '':
valueStr = item
else:
valueStr = valueStr + ', ' + item
if len(valueStr) > 0:
sql = "update %s set %s where id = '%d'" % (table, valueStr, int(id))
print(sql)
db.update(sql)
db.close()
def addKdvideo(url, title, vid, kid):
# login = gfunc.getLoginNameForLocal()
# if login[0] == False:
# return False
# uid = login[2]
flag = False
db = dbHelper.database()
isExist = db.fetch("select * from kdvideo where vid = '%s'" % vid )
if isExist:
pass
else:
uid = '1'
sql = "insert into kdvideo (url, title, vid, kid, fromUserId) values ('%s', '%s', '%s', '%s', '%s')" % (url, title, vid, kid, uid)
print(sql)
flag = db.update(sql)
db.close()
return flag
def getKdvideo(kid=None):
db = dbHelper.database()
sql = 'select * from kdvideo'
res = db.fetch(sql)
return res
def updateAllFromId():
db = dbHelper.database()
db.update("update kduser set fromUserId = '1'")
db.update("update videos set fromUserId = '1'")
db.update("update anchor set fromUserId = '1'")
def fetchTodayVideo():
db = dbHelper.database()
sql = "SELECT * FROM videos WHERE create_time >= date_format(NOW(),'%Y-%m-%d')"
res = db.fetch(sql)
return res
def fetchVideoFromAnchor(aid, cousr=0):
db = dbHelper.database()
sql = "select * from videos where aid = '%d' and publish_time is null " % int(aid)
res = db.fetch(sql)
return res
def fetchVideoFromAlias(qq, alias):
db = dbHelper.database()
sql = "SELECT * FROM videos WHERE qq = '%s' AND publish_time is null AND alias = '%s'" % (qq, alias)
res = db.fetch(sql)
return res
def fetchVideoFormId(ID):
db = dbHelper.database()
sql = "SELECT * FROM videos WHERE id='%s' " % ID
res = db.fetch(sql)
return res
def updateVideoQQ(id, qq):
db = dbHelper.database()
sql = "update videos set qq = '%s' where id = '%d'" % (qq, int(id))
print(sql)
db.update(sql)
db.close()
def insetUser(name, pwd):
db = dbHelper.database()
sql = "INSERT INTO user (name, pwd) values ('%s', '%s')" % (name, pwd)
flag = db.inset(sql)
db.close()
return flag
def fetchAllUser():
db = dbHelper.database()
sql = 'select * from kduser'
res = db.fetch(sql)
return res
def fetchAllAnchor():
db = dbHelper.database()
sql = 'select * from anchor'
res = db.fetch(sql)
return res
def delVideos():
db = dbHelper.database()
sql = "DELETE FROM videos where pic is null"
db.update(sql)
db.close()
def fetchAllVideo():
db = dbHelper.database()
sql = 'select * from videos'
res = db.fetch(sql)
return res
def fetchUserFromName(name):
db = dbHelper.database()
sql = "SELECT * FROM user WHERE name='%s'" % (name)
res = db.fetchUser(sql)
return res
