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()