def create_dataset_from_sql(project_name, top_k=None):
dataset_dir = os.path.join(data_root, project_name)
# read project cfgs
sql = f"select `id`, `name`, `taskType`, `taskRules` from d_projects where `name`='{project_name}'"
res = db_session.execute(sql)
project = res.next() # get name
project = parse_projects(project)
# read data from mysql
sql = f"select d_hits.id as img_id, d_hits.data as path, d_hits_result.result as anns from d_hits, d_hits_result " \
"where d_hits.projectId='{}' and d_hits.id=d_hits_result.hitId and d_hits.status='done'".format(
project['id'])
res = db_session.execute(sql)
# create dataset from sql query results
dataset = create_dataset_from_sql_res(res, project['cats'])
# split and save dataset to json
filter_cats, filter_cats_num, train_num, val_num, test_num = split_and_save_coco_dataset(dataset, dataset_dir,
top_k)
# update project
project['cats_num'] = filter_cats_num
# project['cats'] = filted_cats # 保持创建项目时所有类
# project['classes'] = len(filted_cats)
project['train'] = train_num
project['valid'] = val_num
project['test'] = test_num
return project
def book(isbn):
user_id = session.get("user_id")
if user_id:
db_session()
book = db_session.execute("SELECT * FROM books WHERE isbn=:isbn;", {
"isbn": isbn
}).fetchone()
if book is None:
abort(404)
else:
if request.method == "POST":
db_session.execute(
"""
INSERT INTO reviews (message, rating, user_id, book_id)
VALUES (:message, :rating, :user_id, :book_id);
""", {
"message": request.form.get('message'),
"rating": request.form.get('rating'),
"user_id": user_id,
"book_id": book.id
})
db_session.commit()
return redirect(url_for("main.book", isbn=isbn))
else:
reviews = db_session.execute(
"""
SELECT user_id, message, rating, username FROM reviews
JOIN users ON users.id=reviews.user_id
WHERE book_id=:book_id;
""", {
"book_id": book.id
}).fetchall()
goodreads = {}
if current_app.config.get("GOODREADS_KEY"):
res = requests.get(
"https://www.goodreads.com/book/review_counts.json",
params={
"key": current_app.config.get("GOODREADS_KEY"),
"isbns": isbn
},
timeout=5)
if res.status_code != 200:
raise Exception(
"Request to goodreads was unsuccessful")
goodreads = (res.json())["books"][0]
return render_template("book.html",
book=book,
reviews=reviews,
user_id=int(user_id),
goodreads=goodreads)
else:
abort(403)
def book_api(isbn):
db_session()
book = db_session.execute(
"""
SELECT books.id, books.title, books.author, books.year,
COUNT(reviews.rating) AS total_voters,
AVG(reviews.rating) AS average_rating
FROM books
LEFT JOIN reviews ON books.id=reviews.book_id
WHERE books.isbn=:isbn
GROUP BY books.id
""", {
"isbn": isbn
}).fetchone()
if book is None:
return jsonify({"error": "Invalid book isbn"}), 422
else:
if book.average_rating is not None:
average_rating = round(float(book.average_rating), 2)
else:
average_rating = None
return jsonify({
"isbn": isbn,
"title": book.title,
"author": book.author,
"publication_date": book.year,
"total_voters": book.total_voters,
"average_rating": average_rating
})
def clear_auto_label(project_id):
# find auto-label rows
# sql = "select hitId from d_hits_result where projectId='{}' and notes='auto'".format(project_id)
# find sl/al lines
sql = "select id from d_hits where projectId='{}' and status in ('sl', 'al')".format(project_id)
hitIds = db_session.execute(sql)
for hid in hitIds:
hid = hid[0]
# update d_hits status
sql = "update d_hits set status='notDone' where id={}".format(hid)
db_session.execute(sql)
# clear d_hit_results
sql = "delete from d_hits_result where hitId={}".format(hid)
db_session.execute(sql)
db_session.commit()
def insert_sl_ann_to_db(hit_result_dict):
# 1.insert row to d_hits_result
# value 单行, values 多行，但是速度 values 更快
# id auto increment
sql = "insert into d_hits_result " \
"(`hitId`, `projectId`, `result`, `userId`, `timeTakenToLabelInSec`, `notes`, `created_timestamp`, `updated_timestamp`) " \
"values ({},'{}','{}','{}',{},'{}','{}','{}')".format(hit_result_dict['hitId'], # int
hit_result_dict['projectId'], # str
hit_result_dict['result'], # str
hit_result_dict['userId'], # str
hit_result_dict['timeTakenToLabelInSec'],
# int
hit_result_dict['notes'], # str
hit_result_dict['created_timestamp'], # str
hit_result_dict['updated_timestamp'])
db_session.execute(sql)
db_session.commit()
def query_results_by_status(project_name, status):
projectId = query_projectId_by_name(project_name)
sql = "select d_hits.id as hitId, d_hits.data as filepath, d_hits_result.result as result " \
"from d_hits, d_hits_result " \
"where d_hits_result.hitId=d_hits.id " \
"and d_hits.id in (select id from d_hits where projectId='{}' and status='{}')".format(projectId, status)
hit_results = db_session.execute(sql)
return hit_results
def query_projectId_by_name(project_name):
sql = "select id from d_projects where name='{}'".format(project_name)
res = db_session.execute(sql)
res = res.next() # 返回 <class 'sqlalchemy.engine.result.RowProxy'>
if len(res) > 0:
return res[0]
else:
return None
def query_d_hits_result_by_id(hit_id):
sql = "select result from d_hits_result where hitId={}".format(hit_id)
res = db_session.execute(sql)
res = res.next()
if len(res) > 0:
return res[0]
else:
return None
def query_all_datasets(orgID='2c9180836e6f27e2016e6f327ede0001'):
sql = f"select `name` from d_projects where orgId='{orgID}'" # Shuai datasets
project_names = db_session.execute(sql)
project_list = []
for idx, p_name in enumerate(project_names):
project = query_one_dataset(p_name[0])
project['idx'] = idx + 1 # add idx
project_list.append(project)
return project_list
def query_d_hits(project_name, status=None):
"""
:param project_name: voc, Retail Project Dataset
:param status: 'done', 'notDone'
:return:
"""
sql = "select * from d_hits " \
"where projectId in (select id from d_projects where name='{}')".format(project_name)
if status:
sql += "and status='{}'".format(status)
res = db_session.execute(sql)
# 总数
sql = sql.replace('*', 'count(*)')
total_num = db_session.execute(sql)
# 以循环的方式解析结果, yield, 一输出就没了，数据库用 generator 很合理
# for row in res:
# print(row)
# pprint(parse_row_to_dict(row, dict_template=hit_dict_tmp))
return res, total_num.next()[0]
def index():
user_id = session.get("user_id")
username = "******"
if user_id:
db_session()
user = db_session.execute("SELECT * FROM users WHERE id=:user_id", {
"user_id": session.get('user_id')
}).first()
username = user.username
return render_template("index.html", username=username)
def search():
key = request.args.get("key")
value = request.args.get("value")
results = []
db_session()
if key in {"isbn", "author", "title"}:
results = db_session.execute(
"SELECT * FROM books WHERE %s LIKE :value" % key, {
"value": "%" + value + "%"
}).fetchall()
return render_template("search.html",
key=key,
value=value,
results=results)
def register():
error = None
if request.method == 'POST':
if not request.form.get('username'):
error = "You must submit username"
elif not request.form.get('password'):
error = "You must submit password"
elif request.form.get('password') != request.form.get('password2'):
error = "Passwords don't match"
else:
db_session()
user = db_session.execute(
"SELECT * FROM users WHERE username=:username", {
"username": request.form.get('username')
}).first()
if user:
error = "This username already exists. Choose another one."
else:
password_hash = generate_password_hash(
request.form.get('password'))
db_session.execute(
"""
INSERT INTO users (username, password_hash)
VALUES (:username, :password_hash)""", {
"username": request.form.get('username'),
"password_hash": password_hash
})
db_session.commit()
user = db_session.execute(
"SELECT * FROM users WHERE username=:username", {
"username": request.form.get('username')
}).fetchone()
session["user_id"] = user.id
return redirect(url_for('main.index'))
return render_template('auth/register.html', error=error)
def login():
error = None
if request.method == 'POST':
if not request.form.get('username'):
error = "You must submit username"
elif not request.form.get('password'):
error = "You must submit password"
else:
db_session()
user = db_session.execute(
"SELECT * FROM users WHERE username=:username", {
"username": request.form.get('username')
}).fetchone()
if user and check_password_hash(user.password_hash,
request.form.get('password')):
session["user_id"] = user.id
return redirect(url_for('main.index'))
else:
error = "Login fail. Wrong username/password."
return render_template('auth/login.html', error=error)
def query_one_userId():
sql = "select id from d_users limit 0,1"
res = db_session.execute(sql)
return res.next()[0]
def update_status_in_db(hit_id, status):
sql = "update d_hits set status='{}' where id={}".format(status, hit_id)
db_session.execute(sql)
db_session.commit()
def getleaderboard():
u = db_session.execute(
"SELECT username,marks FROM user ORDER BY marks DESC")
return render_template("leaderboard.html", obj=u)
def query_d_hits_result(project_name):
sql = "select * from d_hits_result " \
"where projectId in (select id from d_projects where name='{}')".format(project_name)
res = db_session.execute(sql)
return res
