def index(): headers = ["***** 登录后才可查看更多哦 *****"] allFields = [] entries = [] tables = [] num = 0 isLogin = False isRoot = False curUser = session.get('user', None) if curUser: isLogin = True isRoot = True if curUser == "root" else False tables = session['tables'] with DbHandler(DB_NAME) as db: db.execute_sql(session['sql'] % tables[0]) entries = db.get_all_records() session['sql'] = DEFAULT_SQL # back to initial sql. num = len(entries) headers = entries[0].keys() if num else [] entries = (tuple(entry) for entry in entries) sql = DEFAULT_SQL + ' limit 1;' allFields = get_table_fields(sql % tables[0]) return render_template('records.html', headers=headers, allFields=allFields, isRoot=isRoot, tables=tables, entries=entries, num=num, isLogin=isLogin, user=curUser)
def addCol(): column = request.args.get('newCol') if column: #g_all_fields.append(column) add_col_sql = ADD_COLUMN_SQL % (session['tables'][0], column) with DbHandler(DB_NAME) as db: db.execute_sql(add_col_sql) return redirect(url_for('index'))
def dropTable(): tables = session.get('tables', []) with DbHandler(DB_NAME) as db: for k in request.args: db.execute_sql(DROP_TABLE_SQL % k) db.execute_sql(DELETE_TABLE_RECORD_SQL % k) tables.remove(k) session['tables'] = tables return redirect(url_for('index'))
def userConfig(): newPword = request.form["newPword"] if newPword: with DbHandler(DB_NAME) as db: curUser = session.get('user') update_sql = UPDATE_USER_PASSWORD_SQL % (newPword, curUser) db.execute_sql(update_sql) return redirect(url_for('logout')) return redirect(url_for('index'))
def dropRow(): condition = '' for k, v in request.args.items(): if v: condition += " %s='%s' and" % (k, v) delete_sql = DELETE_DATA_SQL % session['tables'][0] if condition: delete_sql += " where %s;" % condition[:-3] # strip 'and' with DbHandler(DB_NAME) as db: db.execute_sql(delete_sql) return redirect(url_for('index'))
def show_image(): field = list(request.args.values())[0] with DbHandler(DB_NAME) as db: df = pd.read_sql( "select {field} from {table}".format(field=field, table=session.get( 'tables', [])[0]), db.conn) counts = counts_max(df[field], 10) print_pie(counts, field) return redirect(url_for("images"))
def verify_user(uname, pword): with DbHandler(DB_NAME) as db: query_sql = SEARCH_USER_TABLE_SQL % (uname, pword) db.execute_sql(query_sql) tables = db.get_all_records() if tables and uname == 'root': db.execute_sql(SEARCH_ALL_TABLES_SQL) tables = db.get_all_records() tables.append(('users', )) # users table is only visible to root. if tables: tables = [tuple(t)[0] for t in tables] return list(set(tables))
def output(): tables = session.get('tables', []) coding = list(request.args.values())[0] print("this is output process", tables) print("this is choosing the encoding format:", coding) with DbHandler(DB_NAME) as db: pd_csv = pd.read_sql("select * from {table}".format(table=tables[0]), db.conn) pd_csv.to_csv("{}.csv".format(tables[0]), encoding=coding) filename = "{}.csv".format(tables[0]) if os.path.exists(filename): print("this is output**********") return send_from_directory(r'./', filename, as_attachment=True)
def addRow(): fields = [] values = [] for k, v in request.args.items(): if v: fields.append(k) values.append(v) if fields: insert_sql = INSERT_DATA_SQL.format( session['tables'][0], ','.join(fields)) % ("','".join(values)) with DbHandler(DB_NAME) as db: db.execute_sql(insert_sql) return redirect(url_for('index'))
def dropCol(): tables = session['tables'] curTable = session['tables'][0] all_fields = get_table_fields(session['sql'] % curTable) if request.args.keys(): for k in request.args: all_fields.remove(k) drop_col_sql = DELETE_COLUMN_SQL % (curTable, curTable, ','.join(all_fields)) with DbHandler(DB_NAME) as db: db.cursor.executescript( drop_col_sql) # execute multiple sqls at once. return redirect(url_for('index'))
def updateData(): update_part = '' condition = '' for k, v in request.args.items(): if v: if k.startswith('new'): update_part += "%s='%s'," % (k[3:], v) else: condition += " %s='%s' and" % (k[3:], v) if update_part: update_sql = UPDATE_DATA_SQL % (session['tables'][0], update_part[:-1] ) # strip ',' if condition: update_sql += ' where %s;' % condition[:-3] with DbHandler(DB_NAME) as db: db.execute_sql(update_sql) return redirect(url_for('index'))
def run(self): with self.threadingSum: logging.debug("%s start" % self.url) dbHandler = DbHandler() if not dbHandler.hasQuestion(self.url): # 插入新的问题 question = Question(self.url) title = question.get_title() detail = question.get_detail() answerNum = question.get_answer_num() followersNum = question.get_followers_num() tags = "" for tag in question.get_tags(): tags += tag + ";" tags = tags[0: len(tags) - 1] questionDict = {"url": self.url, "title": title, "detail": detail, "followers": followersNum, "answerNum": answerNum, "tags": tags} dbHandler.insertNewQuestion(questionDict) zh_qid = dbHandler.getQueIdByUrl(self.url) # 插入新的答案 for answer_link in question.get_all_answer_link(): answer = Answer(answer_link) author = answer.get_author() votes = answer.get_votes() answerDict = {"url": answer_link, "author": author, "zh_qid": zh_qid, "votes": votes} dbHandler.insertNewAnswer(answerDict) # 插入图片地址 zh_aid = dbHandler.getAnsIdByUrl(answer_link) for imgUrl in answer.get_all_pics(): dbHandler.insertNewImgUrl(zh_aid, imgUrl) contents = answer.get_answer_content() self.storeTheAnswer(zh_aid, contents) dbHandler.close() logging.debug("%s done" % self.url)
with self.threadingSum: logging.debug("%s start!" % self.url) pic_name = self.url.split("/")[-1] request = requests.get(self.url, stream=True, timeout=10) with open(os.getcwd() + "/tmp/html/images/" + pic_name, 'wb') as fd: for chunk in request.iter_content(): fd.write(chunk) logging.debug("%s done!" % self.url) if __name__ == '__main__': #设置线程数 threadingSum = threading.Semaphore(20) dbHandler = DbHandler() question = Question("http://www.zhihu.com/question/26702926") answer = Answer("http://www.zhihu.com/question/26702926/answer/33843851") img_urls = answer.get_all_pics() for url in img_urls: t = DownloadImg(threadingSum, url) t.start() for t in threading.enumerate(): if t is threading.currentThread(): continue t.join()
def __init__(self): self.dbHandler = DbHandler()
def get_table_fields(sql): with DbHandler(DB_NAME) as db: db.execute_sql(sql) entry = db.get_one_record() return (entry.keys() if entry else [])
def __init__(self, threadingSum, url): threading.Thread.__init__(self) self.dbHandler = DbHandler() self.url = url self.threadingSum = threadingSum
from dbHandler import DbHandler from model import * v = Vertex(12, 12, 12, 0) h = DbHandler('sqlite+pysqlite:///database.db') h.add(v) h.commit() print(h.vertices)