def login():
if "logged_in" in session: # 如果已经登录，则直接跳转到控制台
return redirect(url_for("dashboard"))
form = LoginForm(request.form) # 实例化表单类
if form.validate_on_submit(): # 如果提交表单，并字段验证通过
# 从表单中获取字段
username = request.form['username']
password_candidate = request.form['password']
sql = "SELECT * FROM users WHERE username = '******'" % (
username) # 根据用户名查找user表中记录
db = MysqlUtil() # 实例化数据库操作类
result = db.fetchone(sql) # 获取一条记录
password = result['password'] # 用户填写的密码
# 对比用户填写的密码和数据库中记录的密码是否一致
if sha256_crypt.verify(password_candidate,
password): # 调用verify方法验证，如果为真，验证通过
# 写入session
session['logged_in'] = True
session['username'] = username
flash('登陆成功！', 'success') # 闪存信息
return redirect(url_for('dashboard')) # 跳转到控制台
else: # 如果密码错误
flash('用户名和密码不匹配', 'danger') # 闪存信息
return render_template('login.html', form=form)
def login():
if 'login_in' in session:
return redirect(url_for('dashboard'))
if request.method == 'POST': #提过请求为post提交表单
username = request.form.get('username')
password_candidate = request.form.get('password')
sql = 'select * from users where username = "******"' % (username
) #查找用户名信息
db = MysqlUtil()
result = db.fetchone(sql)
if result:
password = result.get('password')
if sha256_crypt.verify(password_candidate, password):
# 写入session
session['logged_in'] = True
session['username'] = username
session['email'] = result.get('email') #暂时无用，后续涉及修改邮件使用
print(session)
flash('登陆成功！', 'success') #闪存信息
return redirect(url_for('article.dashboard')) #跳转到控制台
# return redirect('/dashboard') #跳转到控制台
else: # 密码错误
error = '用户名密码不匹配'
return render_template('login.html', error=error)
else:
error = '用户名不存在'
return render_template('login.html', error=error)
return render_template('login.html')
def delete_article(id):
db = MysqlUtil() # 实例化数据库操作类
sql = "DELETE FROM articles WHERE id = '%s' and author = '%s'" % (
id, session['username']) # 执行删除笔记的SQL语句
db.delete(sql) # 删除数据库
flash('删除成功', 'success') # 闪存信息
return redirect(url_for('dashboard')) # 跳转到控制台
def bus_start(table_name):
# 1. 查询oracle数据库数据
ou = OracleUtil('192.168.1.200', 1521, 'xe', 'CDCZ_NPC2020MD', 'Asdf123')
sql = "select distinct t.chr_name from %s t order by t.chr_name" % (
'ele_budget_subject')
rows = ou.select(sql)
chr_names = []
for row in rows:
if row is not None:
chr_name = str(row[0]).strip()
if chr_name not in chr_names:
chr_names.append(chr_name)
# 2. 查询mysql数据库数据
mu = MysqlUtil('192.168.1.200', 'bss_pro', 'root', 'Asdf@123')
sql = "select distinct t.subject from %s t where t.year=2020 order by t.subject" % (
table_name)
rows = mu.select(sql)
ret_names = []
for row in rows:
subject = str(row[0]).strip()
if (subject not in chr_names) and (subject not in ret_names):
ret_names.append(subject)
newfile = './ret_names_subject.xlsx'
wb = load_workbook(newfile)
ws = wb['Sheet1']
# 直接根据位置进行赋值
ws['A1'] = '名称'
i = 2
for item in ret_names:
ws.cell(row=i, column=1, value=str(item))
i = i + 1
wb.save(newfile)
def validate_username(self, field):
# 根据用户名查找user表中记录
sql = "SELECT * FROM users WHERE username='******'" % field.data
db = MysqlUtil() # 实例化数据库操作类
result = db.fetchone(sql) # 获取一条记录
if not result:
raise ValidationError("用户名不存在")
def update_data(table_name):
mu = MysqlUtil('192.168.1.200', 'bss_pro', 'root', 'Asdf@123')
name_map = read_excel()
for item in name_map:
sql = "update %s t set t.subject='%s' where t.year=2020 and t.subject='%s'" % (
table_name, item['tname'], item['sname'])
print(sql)
mu.update_sql(sql)
def articles():
db = MysqlUtil()
sql = 'select * from articles order by create_date desc limit 5' # 从article表中筛选5条数据，并根据日期降序排序
articles = db.fetchall(sql)
if articles:
return render_template('articles.html', articles=articles)
else:
msg = '暂无笔记'
return render_template('articles.html', msg=msg)
def dashboard():
db = MysqlUtil() # 实例化数据库操作类
sql = "SELECT * FROM articles WHERE author = '%s' ORDER BY create_date DESC" % (
session['username']) # 根据用户名查找用户笔记信息
result = db.fetchall(sql) # 查找所有笔记
if result: # 如果笔记存在，赋值给articles变量
return render_template('dashboard.html', articles=result)
else: # 如果笔记不存在，提示暂无笔记
msg = '暂无笔记信息'
return render_template('dashboard.html', msg=msg)
def index():
db = MysqlUtil() # 实例化数据库操作类
count = 3 # 每页显示数量
page = request.args.get('page') # 获取当前页码
if page is None: # 默认设置页码为1
page = 1
# 分页查询
# 从article表中筛选5条数据，并根据日期降序排序
sql = f'SELECT * FROM articles ORDER BY create_date DESC LIMIT {(int(page)-1)*count},{count}'
articles = db.fetchall(sql) # 获取多条记录
# 遍历文章数据
return render_template('home.html', articles=articles,
page=int(page)) # 渲染模板
def add_article():
form = ArticleForm(request.form) # 实例化wtforms的ArticleForm 获取请求值然后进行处理
if request.method == 'POST' and form.validate():
# 如果用户提交表单，并且通过验证
title = form.title.data
content = form.content.data
author = session.get('username')
create_date = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
db = MysqlUtil()
sql = 'insert into articles(title,content,author,create_date) values ("%s","%s","%s","%s")' % (title, content, author, create_date)
db.insert(sql)
flash('创建成功', 'success') #闪存信息 返回给前端的get_flashed_messages --> _messages.html
return redirect(url_for('article.dashboard')) # 添加成功 跳转回控制台展示
return render_template('add_article.html', form=form) #带着参数渲染模板
def dashboard():
print()
# print(session)
db = MysqlUtil()
# 根据用户名名查找用户笔记信息
sql = "SELECT * FROM articles WHERE author = '%s' ORDER BY create_date DESC limit 0,2" % (session['username'])
result = db.fetchall(sql) #查找所有笔记
# print('results',result)
if result: #如果有笔记，赋值给articles变量，并传回前端
return render_template('dashboard.html', articles=result, p_list = [x for x in range(1,3)])
else:
msg = '暂无笔记信息'
return render_template('dashboard.html', msg=msg)
def add_article():
form = ArticleForm(request.form) # 实例化ArticleForm表单类
if request.method == 'POST' and form.validate(): # 如果用户提交表单，并且表单验证通过
# 获取表单字段内容
title = form.title.data
content = form.content.data
author = session['username']
create_date = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
db = MysqlUtil() # 实例化数据库操作类
sql = "INSERT INTO articles(title,content,,author,create_date) VALUES ('%s','%s','%s','%s')" % (
title, content, author, create_date) # 插入数据的SQL语句
db.insert(sql)
flash('创建成功', 'success') # 闪存信息
return redirect(url_for('dashboard')) # 跳转到控制台
return render_template('add_article.html', form=form) # 渲染模板
def register():
re_form = RegisterForm(request.form)
if request.method == 'POST' and re_form.validate():
# 判断请求方式，和字段是否符合要求
db1 = MysqlUtil()
# 实例化数据库操作类，并将数据插入数据库
username = re_form.username.data
print(username)
print(type(username))
re_sql = 'select * from users where username = "******"' % (username)
result = db1.fetchone(re_sql) # 获取记录
print(result)
if not result:
# 判断用户名是否已经被注册
db = MysqlUtil()
email = re_form.email.data
password = sha256_crypt.encrypt(str(re_form.password.data))
# 对密码进行加密
sql = "INSERT INTO users(email,username,password) \
VALUES ('%s', '%s', '%s')" % (email, username, password
) # user表中插入记录
# 写sql语句
db.insert(sql)
flash('您已注册成功，请先登录', 'success') # 闪存信息
# return redirect(url_for('login')) #跳转到登陆页面
return redirect('/login')
else:
flash('用户名已经存在，请重新输入', 'false')
return render_template('register.html', form=re_form)
else:
return render_template('register.html', form=re_form) #模板渲染
def edit_article(id):
db = MysqlUtil() # 实例化数据库操作类
fetch_sql = "SELECT * FROM articles WHERE id = '%s' and author = '%s'" % (
id, session['username']) # 根据笔记ID查找笔记信息
article = db.fetchone(fetch_sql) # 查找一条记录
# 检测笔记不存在的情况
if not article:
flash('ID错误', 'danger') # 闪存信息
return redirect(url_for('dashboard'))
# 获取表单
form = ArticleForm(request.form)
if request.method == 'POST' and form.validate(): # 如果用户提交表单，并且表单验证通过
# 获取表单字段内容
title = request.form['title']
content = request.form['content']
update_sql = "UPDATE articles SET title='%s', content='%s' WHERE id='%s' and author='%s'" % (
title, content, id, session['username'])
db = MysqlUtil() # 实例化数据库操作类
db.update(update_sql) # 更新数据的SQL语句
flash('更改成功', 'success') # 闪存信息
return redirect(url_for('dashboard')) # 跳转到控制台
# 从数据库中获取表单字段的值
form.title.data = article['title']
form.content.date = article['content']
return render_template('edit_article.html', form=form) # 渲染模板
def page(page):
# 获取数据
db = MysqlUtil()
# 根据用户名名查找用户笔记信息
sql = "SELECT COUNT(*) FROM articles WHERE author = '%s'" % (session['username'])
result = db.fetchall(sql) # 查找所有笔记
page_num = 3 #分页栏显示数量
half_page_num = int(page_num/2)
eve_page_data = 2 #每页显示条数
start_data_num = (int(page) - 1) * page_num #开始显示的条数
db = MysqlUtil()
# 根据用户名名查找用户笔记信息
sql = "SELECT * FROM articles WHERE author = '%s' limit %i,%i" % (session['username'], (int(page)-1)*eve_page_data,eve_page_data) #从第几条开始，取几条
response_data = db.fetchall(sql) # 查找所有笔记
data_total_count = int(result[0]['COUNT(*)']) #笔记总条数
s,y = divmod(data_total_count,eve_page_data)
page_count = s if not y else s+1 #总页数
#这里待优化
if page_count <= page_num :
p_list = [x for x in range(1,page_count+1)]
elif (int(page) + half_page_num) > page_count :
p_list = [x for x in range(page_count-page_num,page_count+1)]
elif (int(page)- half_page_num) <= 0:
p_list = [x for x in range(1,page_num+1)]
else:
p_list = [x for x in range(int(page)-half_page_num, int(page)+half_page_num+1)]
return render_template('dashboard.html', articles=response_data, p_list=p_list)
def edit_article(id):
db = MysqlUtil()
fetch_sql = "SELECT * FROM articles WHERE id = '%s' and author = '%s'" % (id,session['username']) # 根据笔记id查找笔记信息
article = db.fetchone(fetch_sql) #查找一条记录
if not article:
flash('ID错误','danger') # 闪存信息
return redirect(url_for('article.dashboard'))
# 获取表单
form = ArticleForm(request.form)
if request.method == 'POST' and form.validate():
title = request.form.get('title')
content = request.form.get('content')
update_sql = 'update articles set title = "%s", content = "%s" where id = "%s" and author = "%s"' % (title,content,id,session.get('username'))
db = MysqlUtil()
db.update(update_sql) #更新到数据库
flash('更改成功', 'success')
return redirect(url_for('article.dashboard'))
form.title.data = article['title']
form.content.data = article['content']
return render_template('edit_article.html', form=form)
import time
from mysql_util import MysqlUtil
from ua_util import UAUtil
from ip_util import IPUtil
from my_spider_helper import MySpiderHelper
"""
(主程序)
"""
#获取运行时的时间
temp_time = time.time()
#获取UAUtil对象
ua_util = UAUtil()
#获取IPUtil对象
ip_util = IPUtil(ua_util)
#获取MysqlUtil对象
mysql_util = MysqlUtil()
#获取MySpiderHelper对象
my_spider_helper = MySpiderHelper(ua_util, ip_util, mysql_util)
#启动爬虫
my_spider_helper.start_spider()
#获取程序结束时的时间
current_time = time.time()
print("爬取完成, 共花费时间：" + str(current_time - temp_time))
def art_detail(id):
db = MysqlUtil()
sql = "select * from articles where id = '%s' " % (id)
article = db.fetchone(sql)
return render_template('article.html', article=article)
def delete_article(id):
db = MysqlUtil()
sql = "delete from articles where id = '%s' and author = '%s'" % (id, session.get('username'))
db.delete(sql)
flash('删除成功', 'seccess')
return redirect(url_for('article.dashboard'))
def article(id):
db = MysqlUtil() # 实例化数据库操作类
sql = "SELECT * FROM articles WHERE id = '%s'" % (id) # 根据id查找笔记
article = db.fetchone(sql) # 获取一条记录
return render_template('article.html', article=article) # 渲染模板