def fecharaula(id_aula=None):
# if id_aula==None:
s = tables.Aula.__table__.update().where(tables.Aula.id==id_aula).values(ativa=0)
conn = engine.connect()
conn.execute(s)
return redirect(url_for('lista', id_aula=id_aula))
def consulta():
form = ConsultaAulas()
if request.method == "POST" and form.validate_on_submit():
if form.id_uri.data:
id_uri = form.id_uri.data
query = select([tables.Aula.dia, tables.Aula.nivel, tables.Professor.apelido]).where(
and_( tables.Aula.id == tables.Presenca.id_aula,
and_(tables.Presenca.id_aluno==tables.Aluno.id,
and_(tables.Aluno.ID_URI == id_uri,
and_(tables.Aula.id_prof == tables.Professor.id,
tables.Aula.ativa == 0
)
)
)
)
)
if form.email.data:
email = form.email.data
query = select([tables.Aula.dia, tables.Aula.nivel, tables.Professor.apelido]).where(
and_( tables.Aula.id == tables.Presenca.id_aula,
and_(tables.Presenca.id_aluno==tables.Aluno.id,
and_(tables.Aluno.email == email,
and_(tables.Aula.id_prof == tables.Professor.id,
tables.Aula.ativa == 0
)
)
)
)
)
conn = engine.connect()
result = conn.execute(query)
return render_template('home/mostraAulas.html', aulas=result)
else:
return render_template('home/consultaAulas.html', form=form)
def test_unregister():
req = request.get_json()
res = {}
if request.method == 'GET':
if 'user_id' in req:
sql1 = """
SELECT user.user_id AS id, user.username AS username, user.email AS email, user.password AS password, user.deleted AS deleted
FROM user
WHERE user.user_id = :param_1
"""
user_temp = engine.execute(text(sql1), {
'param_1': req['user_id']
}).fetchone()
if not user_temp == None:
if not user_temp.deleted:
sql2 = """
UPDATE user
SET deleted=:deleted
WHERE user.user_id = :user_id
"""
param = {'deleted': True, 'user_id': req['user_id']}
engine.execute(text(sql2), param)
try:
connection = engine.connect()
trans = connection.begin()
trans.commit()
except:
trans.rollback()
return "commit failed, failure in system!"
return redirect(url_for('show_all_user'))
else:
return "No user having this user_id"
else:
return "Bad request, request should be json object that include key of 'user_id'"
return "(test_unregister)No request received, request should be GET method"
def select(query, **kwargs):
connection = engine.connect()
try:
proxy = connection.execute(query, kwargs)
return proxy
finally:
connection_close(connection)
def before_request():
try:
g.conn = engine.connect()
except:
print("uh oh, problem connecting to database")
import traceback
traceback.print_exc()
g.conn = None
def get_tables(self, database):
#即便cus被关闭，他还是会记录上一次的选中的数据库
cus = engine.connect()
sql = 'use %s;' % database
cus.execute(sql)
cus.close()
sql = 'show tables;'
return self.execute_sql_query(sql)
def execute_sql_query(self, sql, unif=True):
cus = engine.connect()
res = cus.execute(sql).fetchall()
self
cus.close()
if unif:
return self._uniform_query_res(res)
else:
return res
def main():
con = engine.connect()
#result = con.execute("select * from students")
#for row in result:
# print("studentid:", row['studentid'])
con = MySQLdb.connect("localhost", "root", "96Ladybug", "PeerReview")
cursor = con.cursor()
cursor.execute("SET sql_notes = 0; ")
make_profs(con, cursor)
def row_count(query, **kwargs):
connection = engine.connect()
try:
proxy = connection.execute(query, kwargs)
rowcount = proxy.rowcount
proxy.close()
return rowcount
finally:
connection_close(connection)
def first(query, **kwargs):
connection = engine.connect()
try:
proxy = connection.execute(query, kwargs)
first_row = proxy.first()
proxy.close()
return first_row
finally:
connection_close(connection)
def save_edited_image():
# db.session.expire_all()
req = request.get_json()
if request.method == 'POST':
# if ("user_id" in req) and ("org_id" in req) and ("photo" in req):
if ("user_id" in req) and ("org_id" in req) and ("photo" in req) and (
"date_edited" in req):
# assume photo is in string base64 form. we need to change to longblob form
photo_decoded = b64decode(req['photo'])
edited = Edit(user_id=req['user_id'],
org_id=req['org_id'],
photo=photo_decoded,
date_edited=req['date_edited'])
db.session.add(edited)
db.session.commit()
db.session.refresh(edited)
# edited_id = edited.id
# edited.set
#edit_obj=db.session.query(Edit).get(edited.id)
sql1 = '''
UPDATE edit
SET mark_id = mark_id + 1
WHERE edit.edit_id = :edit_id
'''
param1 = {'edit_id': edited.id}
engine.execute(text(sql1), param1)
sql2 = '''
UPDATE original
SET mark_num = mark_num + 1
WHERE original.org_id = :org_id
'''
param2 = {'org_id': req['org_id']}
engine.execute(text(sql2), param2)
try:
connection = engine.connect()
trans = connection.begin()
trans.commit()
except:
trans.rollback()
return "commit failed, failure in system!"
# edit_obj.set()
#return f"'{edit_obj.mark_id}'"
return redirect(url_for('show_all_edit'))
else:
return "Bad request, request should be json object that inclue key of 'user_id', 'org_id', 'photo', 'date'!"
else:
return f"(save_edited_image)No request received, request should be POST method"
def verAulas(id_professor = None):
if current_user.is_admin:
query = select([tables.Professor.nome, tables.Professor.apelido, tables.Professor.id, tables.Professor.ID_URI])
conn = engine.connect()
result = conn.execute(query)
return render_template('home/verAulas.html', professores = result)
else:
flash("A área que você tentou acessar é restrita.")
return redirect(url_for("warning"))
def session():
Base.metadata.bind = engine
Base.metadata.create_all()
connection = engine.connect()
transaction = connection.begin()
session = Session(bind=connection)
yield session
session.close()
Base.metadata.drop_all(bind=engine)
connection.close()
def testapp():
_app = app
Base.metadata.create_all(bind=engine)
_app.connection = engine.connect()
yield app
Base.metadata.drop_all(bind=engine)
_app.connection.close()
def execute(query, **kwargs):
connection = engine.connect()
transaction = connection.begin()
try:
proxy = connection.execute(query, kwargs)
transaction.commit()
return proxy
except:
transaction.rollback()
raise
finally:
connection_close(connection)
def test_register():
req = request.get_json()
res = {}
if request.method == 'POST':
if ("username" in req) and ("email" in req) and ("password" in req):
sql1 = """
SELECT user.user_id AS id, user.username AS username, user.email AS email, user.password AS password, user.deleted AS deleted
FROM user
WHERE user.username = :username_1
"""
user_temp = engine.execute(text(sql1), {
'username_1': req["username"]
}).fetchall()
if not user_temp == []:
for user in user_temp:
if not user.deleted:
return f"'{user_temp}'existing username!"
sql2 = """
SELECT user.user_id AS id, user.username AS username, user.email AS email, user.password AS password, user.deleted AS deleted
FROM user
WHERE user.email = :email_1
"""
user_temp = engine.execute(text(sql2), {
'email_1': req["email"]
}).fetchall()
if not user_temp == []:
for user in user_temp:
if not user.deleted:
return f"'{user_temp}'existing email!"
sql3 = """
INSERT INTO user (username, email, password, deleted) VALUES (:username, :email, :password, :deleted)
"""
param = {
'username': req['username'],
'email': req['email'],
'password': req['password'],
'deleted': False
}
engine.execute(text(sql3), param)
try:
connection = engine.connect()
trans = connection.begin()
trans.commit()
except:
trans.rollback()
return "commit failed, failure in system!"
return redirect(url_for('show_all_user'))
else:
return "Bad request, request should be json object that include key of 'username','email','password'"
return "(test_register)No request received, request should be POST method"
def get_sql_tree(self):
cus = engine.connect()
sql_tree = {}
databases = self.get_databases()
for i in databases:
branch = {}
tables = self.get_tables(i)
for j in tables:
cols_info = self.get_table_column_info(j)
leaf = self.get_table_column(cols_info)
branch[j] = leaf
sql_tree[i] = branch
return sql_tree
def index():
form = PreForm()
form.prof.choices = [(p.nome, p.nome) for p in tables.Professor.query.all()]
if request.method == "POST" and form.validate_on_submit():
if form.id_uri.data:
id_uri = form.id_uri.data
query = select([tables.Aluno.id]).where(tables.Aluno.ID_URI == id_uri)
elif form.email.data:
email = form.email.data
query = select([tables.Aluno.id]).where(tables.Aluno.email == email)
else:
flash('formulario vazio')
return redirect(url_for('index'))
conn = engine.connect()
res = conn.execute(query).fetchone()
if res:
id_aluno = res['id']
query = select([tables.Aula.id]).where(
and_( tables.Aula.id_prof == tables.Professor.id,
and_( tables.Aula.ativa == 1,
and_( tables.Professor.apelido == form.prof.data,
tables.Aula.dia == date.today() ))))
result = conn.execute(query).fetchone()
if result:
id_aula = result['id']
new_presenca = tables.Presenca(id_aula, id_aluno)
db.session.add(new_presenca)
flash("Sua presença foi marcada com sucesso!")
try:
db.session.commit()
except exc.IntegrityError:
db.session.rollback()
flash("Voce ja tem presenca nessa aula")
else:
flash("O professor nao tem nenhuma aula aberta pro dia de hoje")
else:
flash("Dado nao cadastrado")
else:
if form.errors:
flash(form.errors)
return render_template('home/index.html',
form=form)
def make_prediction(self,
img_cv2,
camera_id,
threshold=0.5,
rect_th=1,
text_size=3,
text_th=3):
boxes, pred_cls = self.model_output(img_cv2,
threshold) # получаем предсказания
img_cv2 = cv2.cvtColor(img_cv2,
cv2.COLOR_BGR2RGB) # конвертируем в RGB
car_boxes = self.get_car_boxes(boxes, pred_cls)
# Получаем из бд парковки
statement = text(
"""SELECT * FROM parking_boxes where camera_id={}""".format(
camera_id))
res = engine.connect().execute(statement)
parking_boxes_str = list(res)[0][1]
# [[], []]
parking_boxes = ast.literal_eval(
parking_boxes_str) # конвертируем строку в лист
overlaps = compute_overlaps(
np.array(parking_boxes),
np.array(car_boxes)) # получаем пересечения
free_space = False
for parking_area, overlap_areas in zip(np.array(parking_boxes),
overlaps):
# Ищем максимальное значение пересечения с любой обнаруженной
# на кадре машиной (неважно, какой).
max_IoU_overlap = np.max(overlap_areas)
# Получаем верхнюю левую и нижнюю правую координаты парковочного места.
x1, y1, x2, y2 = parking_area
# Проверяем, свободно ли место, проверив значение IoU.
if max_IoU_overlap < 0.15:
# Место свободно! Рисуем зелёную рамку вокруг него.
cv2.rectangle(img_cv2, tuple([int(x1), int(y1)]),
tuple([int(x2), int(y2)]), (0, 255, 0), 3) # 3
# Отмечаем, что мы нашли как минимум оно свободное место.
free_space = True
else:
# Место всё ещё занято — рисуем красную рамку.
cv2.rectangle(img_cv2, tuple([int(x1), int(y1)]),
tuple([int(x2), int(y2)]), (255, 0, 0), 1) # 1
# Записываем значение IoU внутри рамки.
# font = cv2.FONT_HERSHEY_DUPLEX
# cv2.putText(img_cv2, f"{max_IoU_overlap:0.2}", (x1 + 6, y2 - 6), font, 0.3, (255, 255, 255))
return img_cv2
def get_one_table(self, db, table):
sql_1 = 'use %s;' % db
# sql_2 = 'select * from %s where id >0;' % table
sql_2 = 'select * from %s;' % table
# [(1, 'xiaoao', '"{\\"a\\": 1, \\"b\\": 2}"', None),]
cus = engine.connect()
cus.execute(sql_1)
content = cus.execute(sql_2).fetchall()
cus.close()
cols_info = self.get_table_column_info(table)
table_cols = self.get_table_column(cols_info)
# ('id', 'name', 'content', 'setting')
res = [dict(x) for x in content]
self._load_json(res)
return {'main': res, 'columns': table_cols}
def get_closest_cities(lat: float,
lng: float,
limit: int = 1,
offset: int = 0) -> list[dict]:
"""Get closest cities by coordinates."""
result = []
conn = engine.connect()
s = text("SELECT *, "
"("
"3959 * acos( cos( radians(:latitude) ) * "
"cos( radians( latitude ) ) * cos( radians( longitude ) - "
"radians(:longitude) ) + sin( radians(:latitude) ) * "
"sin( radians( latitude ) ) )"
") AS distance "
"FROM city "
"INNER JOIN cityname ON cityname.city_id = city.id "
"ORDER BY distance "
"LIMIT :limit OFFSET :offset")
raw_data = conn.execute(s,
latitude=lat,
longitude=lng,
limit=limit,
offset=offset).fetchall()
for raw_item in raw_data:
item = {
"id": raw_item["id"],
"country_code": raw_item["country_code"],
"data": {
"lat": raw_item["latitude"],
"lng": raw_item["longitude"]
},
"population": raw_item["population"],
"value": raw_item["name"],
"distance": raw_item["distance"],
}
result.append(item)
conn.close()
return result
def get_closest_airports(lat: float,
lng: float,
limit: int = 1,
offset: int = 0) -> list[dict]:
conn = engine.connect()
s = text("SELECT *, "
"("
"3959 * acos( cos( radians(:latitude) ) * "
"cos( radians( latitude ) ) * cos( radians( longitude ) - "
"radians(:longitude) ) + sin( radians(:latitude) ) * "
"sin( radians( latitude ) ) )"
") AS distance "
"FROM airport "
"ORDER BY distance "
"LIMIT :limit OFFSET :offset")
raw_data = conn.execute(s,
latitude=lat,
longitude=lng,
limit=limit,
offset=offset).fetchall()
return [dict(row) for row in raw_data]
def get_table_column_info(self, table_name):
sql = 'desc %s;' % table_name
cus = engine.connect()
res = cus.execute(sql).fetchall()
cus.close()
return res
def get_path(source: int, destination: int) -> dict[int, list]:
result = defaultdict(list)
s = text("""
WITH RECURSIVE search_graph(
source, -- point 1
destination, -- point 2
distance, -- edge property
depth, -- depth, starting from 1
path -- path, stored using an array
) AS (
SELECT -- ROOT node query
g.source, -- point 1
g.destination, -- point 2
g.distance AS distance, -- edge property
1 AS depth, -- initial depth =1
ARRAY[g.source] AS path -- initial path
FROM route AS g
WHERE SOURCE = :source -- ROOT node =?
UNION ALL SELECT -- recursive clause
g.source, -- point 1
g.destination, -- point 2
g.distance + sg.distance AS distance, -- edge property
sg.depth + 1 AS depth, -- depth + 1
PATH || g.source AS PATH -- add a new point to the path
FROM route AS g, search_graph AS sg -- circular INNER JOIN
WHERE g.source = sg.destination -- recursive JOIN condition
AND (g.source <> ALL(sg.path))-- prevent from cycling
AND sg.depth <= 2 -- search depth =?
)
SELECT DISTINCT PATH || destination AS PATH,
depth,
distance
FROM search_graph -- query a recursive table. You can add LIMIT output or use a cursor
WHERE destination = :destination
ORDER BY distance
LIMIT 10
""")
conn = engine.connect()
raw_data = conn.execute(s, source=source,
destination=destination).fetchall()
conn.close()
needed_cities = list(
reduce(lambda a, b: a | set(b["path"]), raw_data, set()))
airports = (Airport.query.with_entities(
Airport.id,
Airport.airport_name,
Airport.latitude,
Airport.longitude,
).filter(Airport.id.in_(needed_cities)).all())
airports = {
airport.id: {
"airport_name": airport.airport_name,
"latitude": airport.latitude,
"longitude": airport.longitude,
}
for airport in airports
}
for row in raw_data:
result[row["depth"]].append({
"nodes": [airports[airport_id] for airport_id in row["path"]],
"total_distance":
row["distance"],
})
return result
import re, requests, time
from app import app
from app import engine
from app import db_session
from flask import url_for, render_template, flash, g, session, \
redirect
from flask import request
from .forms import xForm
from .forms import signForm
from mandril import drill
import stripe
from stripe import Customer, Charge
con = engine.connect()
app.secret_key = 'super secret key'
STEAM_API_KEY = "1A15D2C82402F944CF5625FC011EF14C"
open_id = OpenID(app)
_steam_id_re = re.compile('steamcommunity.com/openid/id/(.*?)$')
@app.route('/')
@app.route('/index')
def index():
form = xForm()
g.user = None
count = 0
for users in User.query.filter_by(ptype=2):
if users.paid == 1:
count += 20 * 0.3
return update_wrapper(wrapped_function, f)
return decorator
def tnow():
tlist = []
today = datetime.date.today()
tlist.append(today)
def cmd(command):
return Popen(command, shell=True, stdout=PIPE)
con = engine.connect()
app.secret_key = 'super secret key'
STEAM_API_KEY = "1A15D2C82402F944CF5625FC011EF14C"
open_id = OpenID(app)
_steam_id_re = re.compile('steamcommunity.com/openid/id/(.*?)$')
@app.route('/servers', methods=['GET'])
def servers():
sstats = json.loads(request.args.get('json'))
output = render_template('servers.html', server=sstats)
return output
@app.route('/graphs', methods=['GET'])
#-*- coding:utf-8 -*-
# 部署时需要运行
from app import db, engine
if __name__ == '__main__':
# 创建数据库
cur = engine.connect()
cur.execute('create database rest;')
db.create_all()
def clear_database():
with contextlib.closing(engine.connect()) as con:
trans = con.begin()
for table in reversed(Base.metadata.sorted_tables):
con.execute(table.delete())
trans.commit()
