def get_maxtrix_from_db(): connection = get_connection(DB['host'], DB['user'], DB['password'], DB['db']) domain_id_no_out_domains_sql = "select domain_id, no_out_domains from domain" from_domain_to_domain_sql = "select * from domain_to_domain" row_indices = [] col_indices = [] values = [] with connection.cursor() as cursor: cursor.execute(domain_id_no_out_domains_sql) domain_id_no_out_domains = { obj['domain_id']: obj['no_out_domains'] for obj in cursor.fetchall() } cursor.execute(from_domain_to_domain_sql) from_domain_to_domain = cursor.fetchall() no_domains = len(domain_id_no_out_domains) for obj in from_domain_to_domain: row_indices.append(obj['to_domain_id']) col_indices.append(obj['from_domain_id']) values.append( 1 / domain_id_no_out_domains[obj['from_domain_id']] if domain_id_no_out_domains[obj['from_domain_id']] != 0 else 1 / no_domains) return coo_matrix((np.array(values), (np.array(row_indices) - 1, np.array(col_indices) - 1)), shape=(no_domains, no_domains), dtype=np.float32)
def login(): if request.method == 'GET': form = LoginForm() return render_template(template_name_or_list='account/login.html', **{'form': form}) # 获取用户名和密码 loginname = request.form.get('loginname') password = request.form.get('password') try: connection = get_connection() with connection.cursor() as cursor: sql = "select `id` from `account` where `username`=%s and `password`=%s" cursor.execute(sql, (loginname, encrypt_pwd(password=password))) result = cursor.fetchone() pass finally: connection.close() if not result: return '用户名或密码错误!' if 'username' in session: # 这里对已经登陆再进行登陆的都进行清空会话,即使同一个用户旧的也不保留。 session.clear() session['username'] = loginname session['user_id'] = result.get('id') # 头像url,写死一个 return redirect(location=url_for(endpoint='statistic.index'))
def users(): try: connection = get_connection() with connection.cursor() as cursor: sql = "select id, username from `account` order by id" cursor.execute(sql) result = cursor.fetchmany(size=50) finally: connection.close() return render_template('account/users.html', **{'users': result})
def code_record(uid): try: connection = get_connection() with connection.cursor() as cursor: sql = "select `id`, `codelines`, `date` from code_statistics where `user_id`=%s" cursor.execute(sql, (uid,)) result = cursor.fetchmany(size=50) print(result) with connection.cursor() as cursor: sql = "select * from account where id=%s" cursor.execute(sql, (uid,)) user = cursor.fetchone() finally: connection.close() return render_template(template_name_or_list='statistic/code_records.html', **{'records': result, 'user': user})
def calculate_pagerank(): M = get_maxtrix_from_db() v = page_rank(M, 0.001, 0.85).reshape((1, -1))[0] # with open('rank_result_from_db.txt', mode='w') as f: # for i, rank in enumerate(v): # f.write(f"{i}\t{rank}\n") # f.close() connection = get_connection(DB['host'], DB['user'], DB['password'], DB['db']) update_pagerank_sql = "update domain set pagerank = %s where domain_id = %s" with connection.cursor() as cursor: cursor.executemany(update_pagerank_sql, [(float(rank), i + 1) for i, rank in enumerate(v)]) connection.commit() connection.close()
#!/usr/bin/python # -*- coding: UTF-8 -*- import mysql_connection if __name__ == '__main__': db = mysql_connection.get_connection() cursor = db.cursor() sql = " SELECT * FROM account limit 2 " try: cursor.execute(sql) results = cursor.fetchall() for row in results: id = row[0] username = row[1] pwd = row[2] print ("id=%s,username=%s,pwd=%s" % \ (id, username, pwd)) except: print("Error: unable to fetch data") db.close()
def __init__(self): import mysql_connection as connection self.connection = connection.get_connection()