def insert_image(image, name, parent=None, mv=True):
"""Insert an image from form upload into the db and move into
position on the filesystem."""
try:
# Might be file form object
filename = image.filename
except:
# might just be string filename
filename = image
# use filename as name for image if not otherwise supplied
if not name:
name = filename
db = get_db()
db.execute("""insert into
instaclone_images(parent_id, image, name)
values(%s, %s, %s)""",
[parent, filename, name])
pk = db.lastrowid
# If handling a file upload will need to move from /tmp location
if mv:
directory = DIR + Image.image_location(pk, parent)
# make parent directory if necessary
if not os.path.isdir(directory):
os.mkdir(directory)
image.save('%s/%s' % (directory, image.filename))
return pk
def isnRepeat(name, dorm):
#检查重复报名
db.execute(
'SELECT * FROM registration_info WHERE `name`=%s AND `Dormitory`=%s',
(name, dorm))
result = db.fetchall()
return result
def findUser(username):
try:
db.execute('select * from users where `username`=%s', (username, ))
except Exception as e:
abort(408, message=str(e))
result = db.fetchall()
return result
def commentin(data):
username = data['username']
comment = data['comment']
time = data['time']
import ahocorasick
A = ahocorasick.Automaton()
file = open('C:/Users/Williams/screen/text.txt', 'r', encoding="utf-8")
a = file.read()
text = a.split()
#test = ['abcdefg', 'abcdef', 'abcde','abcd','abc','ab','a','abdcef','兼职']
for line in text:
bkw = str(line.strip())
A.add_word(bkw, (1, bkw))
A.make_automaton()
ecomment = comment
for k, (i, t) in A.iter(comment):
ecomment = comment.replace(t, "*" * len(t))
db.execute(
'insert into comments (`username`,`comment`,`time`)values(%s,%s,%s)',
(username, ecomment, time))
try:
conn.commit()
except Exception as e:
abort(408, message=str(e))
broadcast(comment)
return db.rowcount
async def i_am_admin(msg: Message):
"""Установка прав администратора пользователю."""
if filter_chat_is_private(msg):
admin_status = db.get_person(user_id=msg.chat.id)["is_admin"]
if msg.from_user.id in cfg.admin_list and admin_status is False:
db.execute(
"UPDATE Users SET is_admin=True where user_id=%s" % msg.from_user.id
)
await msg.answer(
text="Группа успешно изменена! Теперь у вас есть доступ к *Админ-Меню* в настройках."
)
await event_handler.new_admin(
username=msg.from_user.username, user_id=msg.chat.id
)
elif admin_status:
await msg.answer(text="Вы уже администратор!")
else:
await msg.answer("Вы не администратор!")
await event_handler.try_to_get_admin_mode(
username=msg.from_user.username, user_id=msg.chat.id
)
else:
await msg.answer(
text=f"[{msg.from_user.first_name}](tg://user?id={msg.from_user.id}),"
f" бот работает только в *личных сообщениях*.",
parse_mode="Markdown",
)
def search():
books = []
user = session.get("user_id")
if request.method == "GET":
try:
books = db.execute("SELECT * FROM books LIMIT 20")
except Exception as e:
flash("some thing wrong, Error:{}".format(e), category="error")
return render_template("index.html", books=books, user=user)
term = request.form.get("search_term", None)
if term == None or term == "":
flash("You must provide a term to search")
return render_template("index.html", books=books, user=user)
term = "%{}%".format(term)
books = db.execute(
"SELECT * FROM books WHERE isbn LIKE :term OR title LIKE :term OR author LIKE :term;",
{
"term": term
}).fetchall()
if not len(books):
flash("There is no books")
return render_template("index.html", books=books, user=user)
def userLogin(phone, password):
db.execute('select * from users where `phone`=%s', (phone,))
result = db.fetchone()
if result:
if checkPwd(password, result[3]):
return result
return None
def createUser(username, password):
#密码加密
password = encrypt(password)
db.execute('INSERT INTO users_info(`username`, `password`) VALUES(%s, %s)',
(username, password))
conn.commit()
return db.rowcount
def createUser(phone, password, nickname):
password = encrypt(password)
db.execute(
'insert into b_screen (`phone`, `password`,`nickname`) values (%s, %s, %s)',
(phone, password, nickname))
conn.commit()
return db.rowcount
def createApplicant(name, gender, grade, college, dorm, phone, firstWish,
secondWish, adjustment, faceTime):
db.execute(
'insert into spring (`name`, `gender`, `grade`, `college`, `dorm`, `phone`, `firstWish`, `secondWish` ,`adjustment` ,`faceTime`) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
(name, gender, grade, college, dorm, phone, firstWish, secondWish,
adjustment, faceTime))
conn.commit()
return db.rowcount
def getmessage():
db.execute('select * from message order by `id` desc ')
n1 = db.fetchall()
n2 = ['topic', 'comment', 'time']
data1 = {}
for row in n1:
data1[row[0]] = dict(zip(n2, row[1:]))
return data1
def userLogin(username, password):
db.execute('SELECT * FROM users_info WHERE `username`=%s ', (username, ))
result = db.fetchone()
#先根据用户名选取该行
if result:
if checkPwd(password, result[2]):
#检验密码
return result
return None
def userLogin(username, password):
db.execute('select * from user_name where `user_name`=%s', (username, ))
result = db.fetchone()
# result若存在, 在本例中是一个list, [1, "abc", ".....加密密码....."]
if result:
if checkPwd(password, result[2]):
return result
# 密码错误或用户名不存在
return None
def checkPhone(phone):
db.execute('select * from users where `phone`=%s',(phone,))
result = db.fetchone()
dictReturn=dict()
uniqueness=(result==None)
phoneLength=((len(phone)==11)and phone[0]=='1')
return {
'phoneLength':phoneLength,
'uniqueness':uniqueness}
def checkPhone(phone):
db.execute('select * from b_screen where `phone`=%s', (phone, ))
result = db.fetchone()
uniqueness = (result == None)
phoneLength = ((len(phone) == 11) and phone[0] == '1')
if (uniqueness and phoneLength):
return True
else:
return False
def createUser(username, password):
# 密码加密
password = encrypt(password)
db.execute(
'insert into user_name (`user_name`, `user_password`) values (%s, %s)',
(username, password))
# 对数据库进行修改(create, update, delete)的操作必须commit
conn.connection.commit()
return db.rowcount
def loginin(data):
username = data['username']
password = encrypt(data['password'])
db.execute('select * from users where `username`=%s', (username, ))
result = db.fetchone()
if (result) and checkPwd(password, result[2]):
session['user_id'] = result[0]
return {"errcode": 200, "errmsg": "登录成功"}
else:
return {"errcode": 400, "errmsg": "登录失败"}
def BulletProcess(phone, content):
db.execute('select * from b_screen where `phone`=%s', (phone, ))
result = db.fetchone()
userid = result[0]
time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
db.execute(
'insert into c_bullet (`time`, `content`,`userid`) values (%s, %s, %s)',
(time, content, userid))
ContentProcessed = automaton(content)
return ContentProcessed
def subtimein(data):
username = data['username']
password = encrypt(data['password'])
db.execute('insert into users (`username`,`password`)values(%s,%s)',
(username, password))
try:
conn.commit()
except Exception as e:
abort(408, message=str(e))
return db.rowcount
def create_book():
form = BookForm()
if request.method == 'POST' and form.validate_on_submit():
db.execute(
"INSERT INTO books (title, author) VALUES (:title, :author);", {
"title": form.title.data,
'author': form.author.data
})
db.commit()
# data = Book(form.title.data, form.author.data, form.isbn.data, form.year.data, )
return render_template('book.html', form=form)
def inputApplyInfo(data):
db.execute(' ALTER TABLE registration_info AUTO_INCREMENT = 1;')
db.execute(
'INSERT INTO registration_info(name, sex, Grade, College, Dormitory, Phone,\
First_choice, Secend_choice, Willing_adjust, Self_introduction) VALUES(%s, %s,\
%s,%s, %s, %s, %s, %s, %s, %s)',
(data['name'], data['sex'], data['Grade'], data['College'],
data['Dormitory'], data['Phone'], data['First_choice'],
data['Second_choice'], data['Willing_adjust'],
data['Self_introduction']))
conn.commit()
return db.rowcount
def checkPhone(phone):
db.execute('select * from spring where `phone`=%s', (phone, ))
result = db.fetchone()
dictReturn = dict()
uniqueness = (result == None)
phoneLength = ((len(phone) == 11) and phone[0] == '1')
if (phoneLength and uniqueness):
dictReturn = {'phoneLength': True, 'uniqueness': True}
elif ((phoneLength == True) and (uniqueness == False)):
dictReturn = {'phoneLength': True, 'uniqueness': False}
elif ((phoneLength == False) and (uniqueness == True)):
dictReturn = {'phoneLength': False, 'uniqueness': True}
else:
dictReturn = {'phoneLength': False, 'uniqueness': False}
return dictReturn
def top_images():
sql = """SELECT count(*) as cnt,
instaclone_images.id,
instaclone_images.parent_id,
instaclone_images.image,
instaclone_images.name
FROM instaclone_likes
INNER JOIN instaclone_images
ON instaclone_likes.image = instaclone_images.id
GROUP BY instaclone_images.id
ORDER BY cnt DESC limit 5;"""
db = get_db()
db.execute(sql)
rows = db.fetchall()
return rows
def index():
"主页"
dao=db.execute("select * from calendar where status = 0 order by level desc,endDate asc,id desc")
lists=dao.fetchall()
dao.close()
lists=map(handleListsCharset,lists)
return render_template("index.html",lists=lists)
async def update_schedule_files(group):
"""Обновление устаревших файлов расписания."""
async with aiohttp.ClientSession() as session:
async with session.get(url=main_link, ssl=False) as response:
assert response.status == 200
soup = Soup(await response.text(), "html.parser")
text = soup.find(
"a",
attrs={
"class": "element-title",
"data-bx-title": "%s.xlsx" % group
},
)
parsed_time = text["data-bx-datemodify"]
download_link = f"https://sibsutis.ru{text['data-bx-download']}"
result = db.execute(
query=f"SELECT update_time FROM fs WHERE file_name='{group}'"
)["update_time"]
if result != parsed_time:
db.update_time(file_name=group, update_time=parsed_time)
async with session.get(download_link,
allow_redirects=True,
ssl=False) as r:
open(file=f"./schedule_files/{group}.xlsx",
mode="wb").write(await r.content.read())
async def db_execute(msg):
"""Запрос к базе данных."""
try:
msg.reply(text=db.execute(msg.text))
except Exception as e:
msg.reply(text="Не удалось выполнить запрос.\n__%s__" % e)
error_handler.add_error_to_log(user=msg.from_user.username, error=e)
def login():
"""Log user in"""
session.clear()
if request.method == "POST":
username = request.form.get('username')
password = request.form.get('password')
print("==========>", username, password)
try:
rows = db.execute("SELECT * FROM users WHERE username = :username",
{
"username": username
}).fetchall()
if len(rows) > 0 and check_password_hash(rows[0]["password"],
password):
flash('Logged in successfully!', category='success')
session["user_id"] = rows[0]["id"]
session["username"] = rows[0]["username"]
return redirect("/")
else:
flash("invalid username or password", category='error')
return render_template("login.html")
except Exception as e:
flash("Error:{}".format(e), category='error')
return render_template("login.html")
else:
return render_template("login.html")
def api_book(isbn):
try:
curs = db.execute("SELECT * FROM books WHERE isbn=:isbn;", {
"isbn": isbn
}).fetchone()
# columns = [column[0] for column in books.discription]
except ():
return jsonify({"error": "some error"}), 404
if curs is None:
return jsonify({"error": "no book"}), 404
book = Book(
curs[0],
curs[1],
curs[2],
curs[3],
curs[4],
)
print("----------------", vars(book))
dictBook = vars(book)
return jsonify({
'id': book.id,
'author': book.author,
'title': book.title,
"isbn": book.isbn,
"releaseYear": book.year,
"rating": book.average_reviews,
"reviewCount": book.review_count
})
def callback(self, result):
g_lock.acquire()
logging.debug('whois results: ' + str(result))
sql = '''
UPDATE
`system_domain`
SET
`domain_whois` = %s
WHERE
`domain` = %s;
'''
try:
db.execute(sql, json.dumps(result, default=dumps_handler), self.target)
except Exception as e:
logging.error('saving whois data error.')
logging.error(traceback.format_exc())
logging.error(str(e))
g_lock.release()
def add():
"添加日程"
if request.method == "GET" :
return render_template("add.html")
else:
level=request.form.get("level",None)
content=request.form.get("content",None)
endDate=request.form.get("endDate",None)
dao=db.execute("insert into calendar(level,content,endDate,createTime) values('%s','%s','%s',now())"%(level,content,endDate))
dao.close()
return redirect("/")
def submitin(data):
username = data['name']
gender = data['sex']
grade = data['grade']
campus = data['campus']
academy = data['academy']
phoneNumber = data['phonenumber']
firstVolunteer = data['first_choice']
secondVolunteer = data['second_choice']
YorN = data['YorN']
time = data['time']
self_introduction = data['self_introduction']
db.execute(
'insert into users (`username`,`gender`,`grade`,`campus`,`academy`,`phoneNumber`,`firstVolunteer`,`secondVolunteer`,`YorN`,`time`,`self_introduction`)values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
(username, gender, grade, campus, academy, phoneNumber, firstVolunteer,
secondVolunteer, YorN, time, self_introduction))
try:
conn.commit()
except Exception as e:
abort(408, message=str(e))
return db.rowcount
def add_user(phone, email, nickname, avatar_url, password):
"""增加新用户"""
# 检查手机和Email是否存在
sql = "select id from user where phone = %s;"
if db.query(sql, phone):
return 0, "该手机号已经存在"
sql = "select id from user where email = %s;"
if db.query(sql, email):
return 0, "该Email已经被注册"
regist_time = datetime.now()
sql = "insert into user (phone, email, nickname, avatar_url, password, regist_time) values (%s, %s, %s, %s, %s, %s);"
return db.execute(sql, phone, email, nickname, avatar_url, password, regist_time)
def register():
"""Register user"""
# form = SignupForm()
if request.method == "POST":
username = request.form.get('username')
email = request.form.get('email')
password = request.form.get('password')
confirm = request.form.get('confirm')
bool, message = verifySignupFields(username, email, password, confirm)
if not bool:
flash(message, category='error')
return render_template('signup.html', error=message)
try:
db.execute(
"INSERT INTO users (username, email, password) VALUES (:username,:email, :password);",
{
"username": username,
"email": email,
"password": generate_password_hash(password, 'sha256')
})
db.commit()
except Exception as e:
return render_template('signup.html', error="Error:{}".format(e))
try:
user = db.execute(
"SELECT id FROM users WHERE username = :username;", {
"username": username
}).fetchone()
except Exception as e:
return render_template('signup.html', error="Error:{}".format(e))
session['user_id'] = user[0]["id"]
session['username'] = user[0]["username"]
flash("userCreated", category='success')
return redirect("/")
else:
return render_template("signup.html", )
def account():
"用户登录"
if request.method == "GET":
code=request.args.get("code","3")
return render_template("account.html",code=code)
else:
username=request.form.get("username",None)
password=request.form.get("password",None)
dao=db.execute("select password from account where username = '******' limit 1"%(username))
obj=dao.fetchone()
dao.close()
if obj == None:
url="/account?code=1"
elif obj.password == password:
session['uid']=time.time()
url="/"
else:
url="/account?code=2"
return redirect(url)
def add_comment(user_id, marker_id, content):
"""增加评论"""
create_time = datetime.now()
sql = "insert into comment (marker_id, user_id, content, create_time) values (%s, %s, %s, %s);"
return db.execute(sql, marker_id, user_id, content, create_time)
def num_images():
db = get_db(False)
db.execute('select count(*) from instaclone_images')
total_count = db.fetchone()[0]
return total_count
def add_marker(title, marker):
"""增加新的签到"""
create_time = datetime.now()
sql = "insert into marker (title, marker, create_time) values (%s, %s, %s);"
return db.execute(sql, title, marker, create_time)
def add_member(login_name, nick_name, password):
"""增加新的签到"""
create_time = datetime.now()
sql = "insert into member (login_name, nick_name, password, create_time) values (%s, %s, %s, %s);"
return db.execute(sql, login_name, nick_name, password, create_time)
def finish():
"完成日程"
id=request.args.get("id",None)
dao=db.execute("update calendar set status = 1 where id = %s"%(id))
dao.close()
return redirect("/")