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)
