def dbDelete(db, table):
# DROP TABLE table_name
db = dbutil.dbUtils(dbname)
sql = "drop table %s" % (table)
if db.db_action(sql, 0) == True:
print("%s delete done." % (table))
db.close()
def checkUserList(table):
db = dbutil.dbUtils(dbname)
sql = "select * from " + table
userList = db.db_action(sql, 1)
for user in userList:
print(user)
db.close()
def dbInsertMovie(movieid, movie, tag, score, introduction):
db = dbutil.dbUtils(dbname)
movie = "(" + movieid + ",'" + movie + "','" + tag + "'," + score + ",'" + introduction + "')"
sql = 'insert into movie_info values ' + movie
if db.db_action(sql, 0) == True:
print("User insert done.")
db.close()
return "insert done."
def addDbColumn(table, column, columnAttri):
db = dbutil.dbUtils(dbname)
sql = "alter table %s add column %s %s" % (table, column, columnAttri)
try:
db.db_action(sql, 0)
print("Column insert done.")
except:
print("Exst column!")
db.close()
def dbTableDelete(table, condition=''):
db = dbutil.dbUtils('userdb.db')
sql = "delete from %s %s;" % (table, condition)
try:
db.db_action(sql, 0)
print("Delete done.")
except:
print("Failed")
db.close()
def dbInsertUser(userid, username, passwd):
db = dbutil.dbUtils(dbname)
if checkUserExist(username):
return "Exist user!!!"
user = "******" + userid + ",'" + username + "','" + passwd + "')"
sql = 'insert into user values ' + user
if db.db_action(sql, 0) == True:
print("User insert done.")
db.close()
return "insert done."
def likeDbProcess(filename, n):
db = dbutil.dbUtils('userdb.db')
sql = "select liken from img where imgname = '%s'" % (filename)
likeL = db.db_action(sql, 1)
like = likeL[0][0]
like += n
sql = "update img set liken = %d where imgname = '%s'" % (like, filename)
flag = db.db_action(sql, 0)
db.close()
return flag
def dbInsertReview(imgid, userid, username, content):
db = dbutil.dbUtils(dbname)
created_at = time.time()
sql = "insert into reviews values (null, '%s', '%s', '%s', '%s', '%f')" % (
imgid, userid, username, content, created_at)
try:
db.db_action(sql, 0)
print("Review insert done.")
except:
print("Failed")
db.close()
def dbInsertImg(imgname, uploader, categroy, author, intro, tag='null'):
db = dbutil.dbUtils(dbname)
# sql = "I love %s" % ('piano') | 'insert into img values '+ imgInfo
sql = "insert into img values (null, '%s', '%s', '%s', '%s', '%s', '%s', 0)" % (
imgname, uploader, categroy, author, intro, tag)
#if db.db_action(sql, 0) == True:
# print("User insert done.")
try:
db.db_action(sql, 0)
print("Img insert done.")
except:
print("Exst img name!")
db.close()
def dbTableDelete(table, condition):
db = dbutil.dbUtils(dbname)
sql = "delete from %s %s;" % (table, condition)
print(sql)
#sql = "PRAGMA table_info(img)"
db.db_action(sql, 0)
for i in l:
print(i)
"""try:
db.db_action(sql, 0)
print("Delete done.")
except:
print("Failed")"""
db.close()
def dbInsertImg(imgname, uploader, categroy='unknow', author='unknow', intro='', tag='', expectedprice='0'):
db = dbutil.dbUtils('userdb.db')
sql = 'insert into img values (null, "%s", "%s", "%s", "%s", "%s", "%s", 0, %s)' % (imgname, uploader, categroy, author, intro, tag, expectedprice)
#print("dbinsertImg: %s" % imgname)
#print(sql)
try:
db.db_action(sql, 0)
db.close()
return True
except:
falseinfo = "Exist image name!"
print(falseinfo)
db.close()
return falseinfo
def dbInsertUser(userid, username, passwd, isArtist):
db = dbutil.dbUtils('userdb.db')
userList = dbQuery("username", "user")
# check exist user
if checkUserExist(username):
return "Exist user!!!"
# insert
#user = "******" + userid + ",'" + username + "','" + passwd + "')"
user = "******" % (username, passwd, isArtist)
sql = 'insert into user values ' + user
if db.db_action(sql, 0) == True:
print("User insert done.")
db.close()
return True
else:
db.close()
return False
def dbUpdate(moviename, moviescore):
db = dbutil.dbUtils(dbname)
sql = "select score, numberOfScoring from movie_info where movie_name = '" + moviename + "'"
scoreL = db.db_action(sql, 1)
score = float(scoreL[0][0])
n = float(scoreL[0][1])
if score == -1 and n == 0:
score = moviescore
else:
score = (moviescore + (score * n)) / (n + 1)
# only keep one decimal point
score = int(score * 10) / 10
n += 1
#UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6
sql = "update movie_info set score = " + str(
score) + ", numberOfScoring = " + str(
n) + " where movie_name = '" + moviename + "'"
flag = db.db_action(sql, 0)
db.close()
def dbQuery(choice, table):
db = dbutil.dbUtils(dbname)
sql = "select " + choice + " from " + table
userList = db.db_action(sql, 1)
db.close()
return userList
def dbImgSelect(condition=''):
db = dbutil.dbUtils(dbname)
sql = "select imgname from img %s" % (condition)
imgList = db.db_action(sql, 1)
db.close()
return imgList
def dbQuery(choice, table):
db = dbutil.dbUtils('userdb.db')
sql = "select " + choice + " from " + table
movieList = db.db_action(sql, 1)
db.close()
return movieList