def activate(cls, id, acti_code):
id = int(id)
cur = db.execute("select acti_code from User where id=%d" % (id))
row = cur.fetchone()
if row and acti_code == row[0]:
db.execute("update User set state=1 where id=%d" % (id))
db.commit()
return cls.get(id)
else:
return None
def changeStatus(uid, sid, status):
if status == 1:
try:
db.execute("UPDATE corpus SET status=2, update_time=? WHERE id=%d" % (sid), [datetime.now()])
db.execute(
"UPDATE assign SET status=1, update_time=? WHERE user_id=%d AND corpus_id=%d" % (uid, sid),
[datetime.now()],
)
db.commit()
return True
except:
return False
else:
pass
def saveUser(cls, form):
# move these judge to js code!
# error = ""
# if not form["username"]:
# error += "Username can't be empty!\n"
# elif not form["email"]:
# error += "Email can't be empty!\n"
# elif not form["password"]:
# error += "Password can't be empty!\n"
# elif form["password"] != form["confirm_password"]:
# error += "Please confirm password correctly!\n"
# else:
try:
acti_code = randbytes(20)
cur = db.execute(
"insert into User (first_name, last_name, email, password, university, department, position, acti_code)\
values (?, ?, ?, ?, ?, ?, ?, ?)",
[
form["first_name"],
form["last_name"],
form["email"],
form["password"],
form["university"],
form["department"],
form["position"],
acti_code,
],
)
db.commit()
return [cur.lastrowid, acti_code]
except Exception, e:
print e
return None
def getSentById(sid):
sid = int(sid)
cur = db.execute("SELECT content, update_time FROM corpus WHERE id=%d" % (sid))
res = cur.fetchone()
if res:
res = {"sid": sid, "content": res[0], "update_time": res[1]}
return res
def getItem(uid):
# db.row_factory = dict_factory
cur = db.execute("select corpus_id from assign where status=0 and user_id=%d" % (uid))
res = cur.fetchone()
if res:
sid = res[0]
else:
ok = False
sid = 0
while not ok:
try:
cur = db.execute("select id from corpus where status=0")
res = cur.fetchone()
if res:
sid = res[0]
db.execute(
"insert into assign (corpus_id, user_id, assign_type, status) values (?, ?, ?, ?)", [sid, uid, 0, 0]
)
db.commit()
ok = True
except:
continue
db.execute("UPDATE corpus SET status=1 WHERE id=%d" % (sid))
db.commit()
cur = db.execute("select id, content from corpus where id=%d" % (sid))
res = cur.fetchone()
return {"sid": res[0], "content": res[1]}
def getPasswordByName(cls, username):
if not username:
return None
cur = db.execute("select password from User where username = '******'" % (username))
passwd = cur.fetchone()
if passwd:
return passwd[0]
else:
return None
def updateUsername(self, username):
try:
cursor = db.execute("update User set username=%s where id=%d" % (username, self.id))
db.commit()
return True, u"设置成功"
except sqlite3.IntegrityError:
return False, u"被别人占用了:)"
return False, "fail"
def updateUser(cls, form, old_user):
error = ""
if not form["username"]:
error += "Username can't be empty!\n"
elif not form["email"]:
error += "Email can't be empty!\n"
elif not form["password"]:
error += "Password can't be empty!\n"
elif form["password"] != form["confirm_password"]:
error += "Please confirm password correctly!\n"
else:
db.execute(
"UPDATE User SET username='******', email='%s', password='******' WHERE username='******'"
% (form["username"], form["email"], form["password"], old_user)
)
db.commit()
return error
def tagOne(uid, sid):
uid = int(uid)
sid = int(sid)
try:
cur = db.execute("select corpus_id from assign where status=0 and user_id=%d" % (uid))
res = cur.fetchone()
if res:
sent_id = res[0]
db.execute("UPDATE corpus SET status=0, update_time=? WHERE id=%d" % (sent_id), [datetime.now()])
db.execute("UPDATE corpus SET status=1, update_time=? WHERE id=%d" % (sid), [datetime.now()])
db.execute("DELETE FROM assign WHERE user_id=%d AND status=0" % (uid))
db.execute("insert into Assign (corpus_id, user_id, assign_type, status) values (?, ?, ?, ?)", [sid, uid, 0, 0])
db.commit()
cur = db.execute("select id, content from corpus where id=%d" % (sid))
res = cur.fetchone()
return {"sid": res[0], "content": res[1]}
except:
return False
def get(cls, id):
uid = None
cursor = db.execute("select id, first_name, session_id, signup, email from User where id=%d" % (id))
row = cursor.fetchone()
if row:
u = cls(row[0])
u.username = row[1]
u.session_id = row[2]
u.create_time = row[3]
u.email = row[4]
return u
return None
uid = int(uid)
sid = int(sid)
try:
cur = db.execute("select corpus_id from assign where status=0 and user_id=%d" % (uid))
res = cur.fetchone()
if res:
sent_id = res[0]
db.execute("UPDATE corpus SET status=0, update_time=? WHERE id=%d" % (sent_id), [datetime.now()])
db.execute("UPDATE corpus SET status=1, update_time=? WHERE id=%d" % (sid), [datetime.now()])
db.execute("DELETE FROM assign WHERE user_id=%d AND status=0" % (uid))
db.execute("insert into Assign (corpus_id, user_id, assign_type, status) values (?, ?, ?, ?)", [sid, uid, 0, 0])
db.commit()
cur = db.execute("select id, content from corpus where id=%d" % (sid))
res = cur.fetchone()
return {"sid": res[0], "content": res[1]}
except:
return False
if __name__ == "__main__":
db = sqlite3.connect("LexicalTag.db")
# db.row_factory = dict_factory
sql = "insert into Assign (sid, uid) values (1, 1)"
try:
cur = db.execute(sql)
res = cur.fetchone()
print (res)
except:
pass
def getUserTags(uid):
uid = int(uid)
cur = db.execute("SELECT corpus_id FROM assign WHERE user_id=%d" % (uid))
res = [row[0] for row in cur.fetchall()]
return res
def updateSession(self, session_id):
cursor = db.execute("update User set session_id='%s' where id=%s" % (session_id, self.id))
db.commit()
def getCorpus():
cur = db.execute("SELECT id, content, status FROM corpus")
res = cur.fetchall()
return res
def getIds(cls, start=0, limit=20):
sql = "select id from User order by id asc limit %s, %s" % (start, limit)
cursor = db.execute(sql)
rows = cursor.fetchall()
return [x[0] for x in rows]
def getPasswordByEmail(cls, email):
if not email:
return None
cur = db.execute("select password from User where email = '%s'" % (email))
row = cur.fetchone()
return row and row[0]
def getUserByEmail(cls, email):
cursor = db.execute("select id from User where email='%s'" % (email))
row = cursor.fetchone()
return row and cls.get(row[0])