def total_games_leader_board(won=True, points=None, global_stats=False): query = db.session.query( Player.id, Player.name, func.sum(func.coalesce(case([ (or_(Game.team1_player1_id == Player.id, Game.team1_player2_id == Player.id), case([((Game.team1_score >= 150) if won else (Game.team1_score < 150), 1)])), (or_(Game.team2_player1_id == Player.id, Game.team2_player2_id == Player.id), case([((Game.team2_score >= 150) if won else (Game.team2_score < 150), 1)])) ]), 0)).label('count'), ). \ select_from(Game). \ join(Player, or_(Game.team1_player1_id == Player.id, Game.team1_player2_id == Player.id, Game.team2_player1_id == Player.id, Game.team2_player2_id == Player.id), isouter=True) if global_stats: query = query.filter(Game.finished_at.isnot(None), (Game.points == points) if points else True) else: today = datetime.now() - timedelta(hours=8) beginning_of_year = datetime(today.year, 1, 1, 8, 0).astimezone(tz.gettz('UTC')) query = query.filter(Game.finished_at.isnot(None), Game.started_at.__gt__(beginning_of_year), (Game.points == points) if points else True) return query.group_by(Player.id). \ having(db.text('count > 0')). \ order_by(db.text('count DESC'))
class Test(db.Model): # define table name __tablename__ = 't_lessons' id = db.Column(db.Integer, primary_key=True) lesson_name = db.Column(db.String(32), unique=True, nullable=False) lesson_info = db.Column(db.String(32), nullable=False) status = db.Column(db.SmallInteger, nullable=False, default=1, doc="用户状态,0-禁用,1-启动") create_datetime = db.Column(db.DateTime, server_default=db.text("CURRENT_TIMESTAMP"), doc="创建时间") update_datetime = db.Column( db.DateTime, nullable=False, server_default=db.text( "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"), doc="更新时间") def __init__(self, **kwargs): super(Test, self).__init__(**kwargs) def __repr__(self): return "User<name:%r>" % self.lesson_name
class CraftEssence(db.Model): """概念礼装""" __tablename__ = 'craft_essences' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100), nullable=False) star = db.Column(db.SmallInteger, nullable=False) icon = db.Column(db.String(255), nullable=False) image = db.Column(db.String(255), nullable=False) atk = db.Column(db.Integer, nullable=False) max_atk = db.Column(db.Integer, nullable=False) hp = db.Column(db.Integer, nullable=False) max_hp = db.Column(db.Integer, nullable=False) effect = db.Column(db.Text, nullable=False) max_effect = db.Column(db.Text, nullable=False) effect_icon = db.Column(db.String(255), nullable=False) description = db.Column(db.Text, nullable=False) note = db.Column(db.Text, nullable=True, server_default=None) created = db.Column(db.TIMESTAMP, nullable=False, server_default=db.text('CURRENT_TIMESTAMP')) updated = db.Column(db.TIMESTAMP, nullable=False, server_default=db.text( 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) deleted = db.Column(db.TIMESTAMP, nullable=True, server_default=None) def __repr__(self): return '<%s%s %d: %s>' % (self.__class__.__name__, '(%s)' % self.__doc__ if self.__doc__ is not None else str(), self.id, self.name)
class Chat(db.Model): __tablename__ = 'chat' id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=True) name = db.Column(db.String(100), nullable=True) datetime = db.Column(db.DateTime, nullable=False) content = db.Column(db.Text, nullable=False) note = db.Column(db.Text, nullable=True, server_default=None) created = db.Column(db.TIMESTAMP, nullable=False, server_default=db.text('CURRENT_TIMESTAMP')) updated = db.Column(db.TIMESTAMP, nullable=False, server_default=db.text( 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) deleted = db.Column(db.TIMESTAMP, nullable=True, server_default=None) user = db.relationship('User', backref='chat') def __repr__(self): return '<%s%s %d: %s>' % (self.__class__.__name__, '(%s)' % self.__doc__ if self.__doc__ is not None else str(), self.id, self.content)
class SkillLv(db.Model): """技能升级材料""" __tablename__ = 'skill_lv' id = db.Column(db.Integer, primary_key=True) servant_id = db.Column(db.Integer, db.ForeignKey('servants.id'), nullable=False) level = db.Column(db.SmallInteger, nullable=False) item = db.Column(db.Integer, db.ForeignKey('items.id'), nullable=False) quantity = db.Column(db.Integer, nullable=False) note = db.Column(db.Text, nullable=True, server_default=None) created = db.Column(db.TIMESTAMP, nullable=False, server_default=db.text('CURRENT_TIMESTAMP')) updated = db.Column(db.TIMESTAMP, nullable=False, server_default=db.text( 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) deleted = db.Column(db.TIMESTAMP, nullable=True, server_default=None) servant = db.relationship('Servant', backref='skill_lv') def __repr__(self): return '<%s%s %d: %s>' % (self.__class__.__name__, '(%s)' % self.__doc__ if self.__doc__ is not None else str(), self.id, '%s Lv %d' % (self.servant.name, self.level))
class Alias(db.Model): """英灵别名""" __tablename__ = 'servant_alias' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(255), nullable=False, unique=True) servant_id = db.Column(db.Integer, db.ForeignKey('servants.id'), nullable=False) note = db.Column(db.Text, nullable=True, server_default=None) created = db.Column(db.TIMESTAMP, nullable=False, server_default=db.text('CURRENT_TIMESTAMP')) updated = db.Column(db.TIMESTAMP, nullable=False, server_default=db.text( 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) deleted = db.Column(db.TIMESTAMP, nullable=True, server_default=None) servant = db.relationship('Servant', backref='alias') def __repr__(self): return '<%s%s %d: %s>' % (self.__class__.__name__, '(%s)' % self.__doc__ if self.__doc__ is not None else str(), self.id, '%s -> %s' % (self.name, self.servant.name))
class NpEffect(db.Model): """宝具效果""" __tablename__ = 'np_effects' id = db.Column(db.Integer, primary_key=True) servant_id = db.Column(db.Integer, db.ForeignKey('servants.id'), nullable=False) effect = db.Column(db.Text, nullable=False) lv1 = db.Column(db.String(100), nullable=True) lv2 = db.Column(db.String(100), nullable=True) lv3 = db.Column(db.String(100), nullable=True) lv4 = db.Column(db.String(100), nullable=True) lv5 = db.Column(db.String(100), nullable=True) note = db.Column(db.Text, nullable=True, server_default=None) created = db.Column(db.TIMESTAMP, nullable=False, server_default=db.text('CURRENT_TIMESTAMP')) updated = db.Column(db.TIMESTAMP, nullable=False, server_default=db.text( 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) deleted = db.Column(db.TIMESTAMP, nullable=True, server_default=None) servant = db.relationship('Servant', backref='np_effects') def __repr__(self): return '<%s%s %d: %s>' % (self.__class__.__name__, '(%s)' % self.__doc__ if self.__doc__ is not None else str(), self.id, self.effect)
class Booking(db.Model): id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey('user.id')) book_id = db.Column(db.String(64), index=True) start_date = db.Column(db.Date) end_date = db.Column(db.Date) created_at = db.Column(db.TIMESTAMP, nullable=False, server_default=db.text("CURRENT_TIMESTAMP")) updated_at = db.Column( db.TIMESTAMP, nullable=False, server_default=db.text( "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")) def __repr__(self): return '<Booking {}>'.format(self.book_id) @property def serialize(self): return { 'user_id': self.user_id, 'book_id': self.book_id, 'start_date': dump_date(self.start_date), 'end_date': dump_date(self.end_date) }
class XSKClass(db.Model): # define table name __tablename__ = 'xsk_class' id = db.Column(db.Integer, primary_key=True) class_name = db.Column(db.String(32), unique=False, nullable=False, index=True) class_info = db.Column(db.String(100), nullable=True) class_period = db.Column(db.String(32), nullable=True) class_start = db.Column(db.String(32), nullable=True) status = db.Column(db.SmallInteger, nullable=False, default=1, doc="课程状态,0-禁用,1-启动") create_datetime = db.Column(db.DateTime, server_default=db.text("CURRENT_TIMESTAMP"), doc="创建时间") update_datetime = db.Column( db.DateTime, nullable=False, server_default=db.text( "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"), doc="更新时间") def __init__(self, **kwargs): super(XSKClass, self).__init__(**kwargs) def __repr__(self): return "Class<name:%r>" % self.class_name
def versus_leader_board(global_stats=False): players = aliased(Player) other_players = aliased(Player) query = db.session.query( players.id, players.name, other_players.id, other_players.name, func.sum(func.coalesce(case([ (and_(or_(Game.team1_player1_id == players.id, Game.team1_player2_id == players.id), not_(or_(Game.team1_player1_id == other_players.id, Game.team1_player2_id == other_players.id))), case([(Game.team1_score >= 150, Game.points)])), (and_(or_(Game.team2_player1_id == players.id, Game.team2_player2_id == players.id), not_(or_(Game.team2_player1_id == other_players.id, Game.team2_player2_id == other_players.id))), case([(Game.team2_score >= 150, Game.points)])) ]), 0)).label('wins_score'), func.sum(func.coalesce(case([ (and_(or_(Game.team1_player1_id == players.id, Game.team1_player2_id == players.id), not_(or_(Game.team1_player1_id == other_players.id, Game.team1_player2_id == other_players.id))), case([(Game.team1_score < 150, Game.points)])), (and_(or_(Game.team2_player1_id == players.id, Game.team2_player2_id == players.id), not_(or_(Game.team2_player1_id == other_players.id, Game.team2_player2_id == other_players.id))), case([(Game.team2_score < 150, Game.points)])) ]), 0)).label('loses_score'), func.sum(func.coalesce(case([ (and_(or_(Game.team1_player1_id == players.id, Game.team1_player2_id == players.id), not_(or_(Game.team1_player1_id == other_players.id, Game.team1_player2_id == other_players.id))), case([(Game.team1_score < 150, -1)], else_=1) * Game.points), (and_(or_(Game.team2_player1_id == players.id, Game.team2_player2_id == players.id), not_(or_(Game.team2_player1_id == other_players.id, Game.team2_player2_id == other_players.id))), case([(Game.team2_score < 150, -1)], else_=1) * Game.points) ]), 0)).label('balance'), ). \ select_from(Game). \ join(players, or_(Game.team1_player1_id == players.id, Game.team1_player2_id == players.id, Game.team2_player1_id == players.id, Game.team2_player2_id == players.id)). \ join(other_players, or_(Game.team1_player1_id == other_players.id, Game.team1_player2_id == other_players.id, Game.team2_player1_id == other_players.id, Game.team2_player2_id == other_players.id)) if global_stats: query = query.filter(Game.finished_at.isnot(None)) else: today = datetime.now() - timedelta(hours=8) beginning_of_year = datetime(today.year, 1, 1, 8, 0).astimezone(tz.gettz('UTC')) query = query.filter(Game.finished_at.isnot(None), Game.started_at.__gt__(beginning_of_year)) return query.group_by(players.id, other_players.id). \ having(or_(db.text('wins_score > 0'), db.text('loses_score > 0'))). \ order_by(db.text(request.args.get('versus_leader_board_order_by', 'balance DESC, wins_score DESC')))
def search_transcripts(search_term, current_page=1): """Searches comic transcripts and returns the dates for matching comics""" if app.config['SQLALCHEMY_DATABASE_URI'].startswith('sqlite'): results = SearchTranscripts.query.filter(db.text('transcript MATCH :search_term')).params( search_term=search_term).paginate(page=current_page, per_page=10, error_out=False) elif app.config['SQLALCHEMY_DATABASE_URI'].startswith('mysql'): results = Comic.query.filter(db.text('MATCH(transcript) AGAINST(:search_term)')).params( search_term=search_term).paginate(page=current_page, per_page=10, error_out=False) else: raise NotImplementedException return [datetime.utcfromtimestamp(result.date).date() for result in results.items]
class TaskJobModel(db.Model): __tablename__ = 'TaskJob' ID = db.Column(db.Text, primary_key=True) creation = db.Column(db.Text, nullable=False, default=datetime.datetime.utcnow) ended = db.Column(db.Text, server_default=db.text("''")) user = db.Column(db.Text, nullable=False, default="") ejecutable = db.Column(db.Text, nullable=False, default="") estado = db.Column(db.Integer, nullable=False, server_default=db.text("'En ejecución'")) output = db.Column(db.Text, server_default=db.text("'En ejecución'")) name = db.Column(db.Text, nullable=False) def __init__(self, name): self.ID = self.GetUniqueHash() self.name = name def __repr__(self): return self.ToJson() def GetUniqueHash(self): hash = hashlib.sha1() hash.update(str(time.time()).encode('utf-8')) return hash.hexdigest()[:10] def ToDict(self): #print("taskdict-> ", self.__dict__) return self.__dict__ def ToJson(self): result = OrderedDict() for key in self.__mapper__.c.keys(): result[key] = getattr(self, key) return json.dumps(result) @staticmethod def getByIDJson(id): task = TaskJobModel.query.get(id) return task.ToJson() @staticmethod def GetAllTasksJson(): tasks = TaskJobModel.query.all() data2 = [] for task in tasks: d = {} for column in task.__table__.columns: d[column.name] = str(getattr(task, column.name)) data2.append(d) return json.dumps(data2)
def leader_board(today=False, active=True, min_games_count=0, global_stats=False): query = db.session.query( Player.id, Player.name, (func.sum(func.coalesce(case([ (or_(Game.team1_player1_id == Player.id, Game.team1_player2_id == Player.id), case([(Game.team1_score >= 150, Game.points)])), (or_(Game.team2_player1_id == Player.id, Game.team2_player2_id == Player.id), case([(Game.team2_score >= 150, Game.points)])) ]), 0)) + (0 if today else Player.manual_wins)).label('wins_score'), (func.sum(func.coalesce(case([ (or_(Game.team1_player1_id == Player.id, Game.team1_player2_id == Player.id), case([(Game.team1_score < 150, Game.points)])), (or_(Game.team2_player1_id == Player.id, Game.team2_player2_id == Player.id), case([(Game.team2_score < 150, Game.points)])) ]), 0)) + (0 if today else Player.manual_loses)).label('loses_score'), (func.sum(func.coalesce(case([ (or_(Game.team1_player1_id == Player.id, Game.team1_player2_id == Player.id), case([(Game.team1_score < 150, -1)], else_=1) * Game.points), (or_(Game.team2_player1_id == Player.id, Game.team2_player2_id == Player.id), case([(Game.team2_score < 150, -1)], else_=1) * Game.points) ]), 0)) + (0 if today else (Player.manual_wins - Player.manual_loses))).label('balance'), func.sum(func.coalesce(case([ (or_(Game.team1_player1_id == Player.id, Game.team1_player2_id == Player.id, Game.team2_player1_id == Player.id, Game.team2_player2_id == Player.id), 1)], else_=0), 0)).label('games_count') ). \ select_from(Player). \ join(Game, or_(Game.team1_player1_id == Player.id, Game.team1_player2_id == Player.id, Game.team2_player1_id == Player.id, Game.team2_player2_id == Player.id), isouter=True) if today: today = datetime.now() - timedelta(hours=8) beginning_of_today = datetime(today.year, today.month, today.day, 8, 0).astimezone(tz.gettz('UTC')) query = query.filter(Game.finished_at.isnot(None), Game.started_at.__gt__(beginning_of_today)) else: if global_stats: query = query.filter(Game.finished_at.isnot(None)). \ having(db.text(f'games_count {">=" if active else "<"} {str(min_games_count)}')) else: today = datetime.now() - timedelta(hours=8) beginning_of_year = datetime(today.year, 1, 1, 8, 0).astimezone(tz.gettz('UTC')) query = query.filter(Game.finished_at.isnot(None), Game.started_at.__gt__(beginning_of_year)). \ having(db.text(f'games_count {">=" if active else "<"} {str(min_games_count)}')) return query. \ group_by(Player.id). \ order_by(db.text(request.args.get('leader_board_order_by', 'balance DESC, wins_score DESC')))
class Deliver(db.Model): __tablename__ = 'delivers' id = db.Column(db.Integer, primary_key=True) did = db.Column(db.String(20), nullable=False, unique=True) oid = db.Column(db.String(20), nullable=False) cid = db.Column(db.String(20), nullable=False) # 车id mid = db.Column(db.String(20), nullable=False) # 驾驶员id addr = db.Column(db.Text, nullable=False) # 目的地 status = db.Column(db.Text, nullable=False) # 目前位置 created_at = db.Column(db.TIMESTAMP, server_default=db.text('CURRENT_TIMESTAMP'), nullable=False) updated_at = db.Column(db.TIMESTAMP, server_default=db.text('CURRENT_TIMESTAMP'), server_onupdate=db.text('CURRENT_TIMESTAMP'), nullable=False)
class Car(db.Model): __tablename__ = 'cars' id = db.Column(db.Integer, primary_key=True) cid = db.Column(db.String(20), unique=True, nullable=False) mid = db.Column(db.String(20), nullable=False) status = db.Column(db.Text, nullable=False) # 目前位置 active = db.Column(db.Integer) # 是否活跃 cplace = db.Column(db.String(4), nullable=False) cnumber = db.Column(db.Integer, nullable=False) created_at = db.Column(db.TIMESTAMP, server_default=db.text('CURRENT_TIMESTAMP'), nullable=False) updated_at = db.Column(db.TIMESTAMP, server_default=db.text('CURRENT_TIMESTAMP'), server_onupdate=db.text('CURRENT_TIMESTAMP'), nullable=False)
class Message(db.Model): __tablename__ = 'message' __bind_key__ = 'stock' stock_name = db.Column(db.String(40), primary_key=True) stock_id = db.Column(db.CHAR(10)) stock_price = db.Column(db.DECIMAL(7, 2)) up_confine = db.Column(db.DECIMAL(4, 2), server_default=db.text("'0.10'")) down_confine = db.Column(db.DECIMAL(4, 2), server_default=db.text("'0.10'")) # continue_trans = db.Column ( db.TINYINT(1), server_default=db.text ( "'1'" ) ) def __init__(self, stock_name, stock_id=None, stock_price=1): self.stock_name = stock_name self.stock_price = stock_price
class Class(db.Model): """职介""" __tablename__ = 'class' Saber = 1 Lancer = 2 Archer = 3 Rider = 4 Caster = 5 Assassin = 6 Berserker = 7 Avenger = 8 Ruler = 9 Shielder = 10 Beast = 11 Alterego = 12 MoonCancer = 13 Foreigner = 14 id = db.Column(db.SmallInteger, primary_key=True) name = db.Column(db.String(255), nullable=False, unique=True) sort = db.Column(db.Integer, nullable=False, unique=True) created = db.Column(db.TIMESTAMP, nullable=False, server_default=db.text('CURRENT_TIMESTAMP')) updated = db.Column(db.TIMESTAMP, nullable=False, server_default=db.text( 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) deleted = db.Column(db.TIMESTAMP, nullable=True, server_default=None) def __init__(self, name, sort=None): self.name = name self.sort = self.last_sort + 1 def __repr__(self): return '<%s%s %d: %s>' % (self.__class__.__name__, '(%s)' % self.__doc__ if self.__doc__ is not None else str(), self.id, self.name) @property def last_sort(self): entity = self.__class__.query.order_by(desc('sort')).first() if entity: return entity.sort else: return 0
def index(): if request.method == "POST": searchQuery = request.form.get("searchQuery") print(searchQuery) # Avoid SQL Injection Using Bindings sql = "SELECT isbn, author, title \ FROM book \ WHERE isbn LIKE :x \ OR author LIKE :y \ OR title LIKE :z" # I spent an hour wondering why I couldnt put the bindings inside the wildcard string... # https://stackoverflow.com/questions/3105249/python-sqlite-parameter-substitution-with-wildcards-in-like matchString = "%{}%".format(searchQuery) stmt = db.text(sql).bindparams(x=matchString, y=matchString, z=matchString) results = db.session.execute(stmt).fetchall() print(results) session["books"] = [] for row in results: # A row is not JSON serializable so we pull out the pieces book = dict() book["isbn"] = row[0] book["author"] = row[1] book["title"] = row[2] session["books"].append(book) return render_template("index.html", searchedFor=searchQuery, books=session["books"]) return render_template("index.html")
def pg_trgm(): try: query = db.text("CREATE EXTENSION pg_trgm;") db.session.execute(query) db.session.commit() except Exception: pass
def get_posts_by_cat(cls, cat, limit=10, page=1, desc=True): category = cls.query.filter_by(slug=cat).one_or_none() if not category: return None, None posts = category.posts.order_by(db.text('id DESC')).limit( limit).offset((page - 1) * limit) return posts, category
class Request(Base): __tablename__ = 'requests' id = db.Column(db.Integer, primary_key=True) request_type = db.Column( db.String(64), index=True, nullable=False ) # change_organization_role | change_spec_definition | change_field_definition namespace = db.Column(db.String(64), index=True) # organizations.namespace resource = db.Column(db.String(64), index=True, nullable=False) # users/fields/specs/organizations resource_id = db.Column(db.Integer, index=True) content = db.Column(db.JSON) comments = db.Column(db.JSON) # comments state = db.Column(db.Enum(RequestState), nullable=False, default=RequestState.reviewing, index=True) created_by = db.Column(db.Integer, index=True, nullable=False) created_at = db.Column(db.TIMESTAMP, server_default=db.text('CURRENT_TIMESTAMP')) accepted_by = db.Column(db.Integer, index=True) accepted_at = db.Column(db.TIMESTAMP) def __repr__(self): return '<Request {}@{}>'.format(self.request_type, self.created_by) def delete(self): self.state = RequestState.deleted self.query.session.add(self) self.query.session.commit() return self
class Orders(db.Model): """ create a Orders table """ __tablename__ = 'orders' id = db.Column(db.Integer, primary_key=True) customer_id = db.Column(db.Integer, db.ForeignKey('customers.id')) employee_id = db.Column(db.Integer, db.ForeignKey('employees.id')) tablenumber = db.Column(db.Integer) notes = db.Column(db.String(255)) orderstatus = db.Column(db.Integer, db.ForeignKey('status.id')) email_sent = db.Column( db.String(10)) #sent an email when the flag is set to 'S' sms_sent = db.Column( db.String(10)) #sent an sms when the flag is set to 'S' processed = db.Column(db.String(10)) #flag 'N' or 'Y' datecreated = db.Column(db.TIMESTAMP) dateupdated = db.Column( db.TIMESTAMP, server_default=db.text( 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) orders_list = db.relationship('OrdersList', backref='order_items', lazy='dynamic') def __repr__(self): return '<Orders: {}'.format(self.tablenumber)
def display_deploy_info(): counts = db.session.query( db.func.count('*')).select_from(DeployInfo).scalar() print counts per_page = 10 search = False q = request.args.get('q') if q: search = True try: page = int(request.args.get('page', 1)) except ValueError: page = 1 offset = (page - 1) * per_page sql = 'select * from deploy_info order by deploy_info.deploy_date desc limit {},{}'.format( offset, per_page) deploy = db.session.query(DeployInfo).from_statement(db.text(sql)).all() pagination = Pagination(page=page, link_size='sm', total=counts, record_name='deploy', per_page=per_page, bs_version=3, format_total=True, format_number=True, show_single_page=False) return render_template('list-deploy-log.html', deploy=deploy, pagination=pagination, page=page, per_page=per_page)
class Run(db.Model): id = db.Column(db.Integer, primary_key = True, nullable=False) started_at = db.Column(db.TIMESTAMP, nullable=True) finished_at = db.Column(db.TIMESTAMP, nullable=True) count = db.Column(db.Integer, nullable=False, server_default=db.text("0")) tool_version = db.Column(db.Text, nullable=True) wdtk_version = db.Column(db.Text, nullable=True) dump_date = db.Column(db.Text, nullable=True) @property def estimated_factor(self): expected_count = Run.query.filter( Run.finished_at != None ).order_by(Run.finished_at.desc()).limit(1).one().count return self.count / expected_count @property def estimated_percentage(self): return int(self.estimated_factor * 100) @property def estimated_remaining(self): current_seconds = (datetime.utcnow() - self.started_at).total_seconds() if self.estimated_factor == 0: r = Run.query.filter(Run.finished_at != None).order_by(Run.finished_at.desc()).limit(1).one() return r.finished_at - r.started_at return timedelta(seconds=current_seconds / self.estimated_factor - current_seconds)
def display_repos_info(): counts = db.session.query( db.func.count('*')).select_from(RepoInfo).scalar() per_page = 5 search = False q = request.args.get('q') if q: search = True try: page = int(request.args.get('page', 1)) except ValueError: page = 1 offset = (page - 1) * per_page sql = 'select * from repos_info limit {},{}'.format(offset, per_page) repos = db.session.query(RepoInfo).from_statement(db.text(sql)).all() pagination = Pagination(page=page, link_size='sm', total=counts, record_name='repos', per_page=per_page, bs_version=3, format_total=True, format_number=True, show_single_page=False) return render_template('list-repo.html', repos=repos, pagination=pagination, page=page, per_page=per_page)
def search_deploy_info(): if request.method=='POST' and request.form.get('envtype',None) : args='%'+request.form.get('repo').encode('utf-8')+'%' sql='select * from deploy_info where repo_name like "%'+request.form.get('repo').encode('utf-8')+'%" and deploy_env="'+request.form.get('envtype')+'" order by deploy_info.deploy_date desc' deploy=db.session.query(DeployInfo).from_statement(db.text(sql)).all() return render_template('deploy-search.html',deploys=deploy) return redirect(url_for('display_deploy_info'))
class User(db.Model): id = db.Column(db.Integer, primary_key=True) first_name = db.Column(db.String(64), index=True) last_name = db.Column(db.String(64), index=True) created_at = db.Column(db.TIMESTAMP, nullable=False, server_default=db.text("CURRENT_TIMESTAMP")) updated_at = db.Column( db.TIMESTAMP, nullable=False, server_default=db.text( "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")) bookings = db.relationship('Booking', backref='booker', lazy='dynamic') def __repr__(self): return '<User {}>'.format(self.first_name)
class Order(db.Model): __tablename__ = 'orders' id = db.Column(db.Integer, primary_key=True) oid = db.Column(db.String(20), unique=True, nullable=False) # 订单id iid = db.Column(db.String(20), nullable=True) uid = db.Column(db.String(20), nullable=False) # 用户id aid = db.Column(db.String(20), nullable=False) # 商家id sum = db.Column(db.Integer, nullable=False) # 数量 pid = db.Column(db.String(20), nullable=False) # 商品id alevel = db.Column(db.Integer, nullable=False, default=0) did = db.Column(db.String(20), nullable=True) # 最新的运输id status = db.Column(db.Integer, default=0) # 0:未发货, 1:未收货, 2:已完成 addr = db.Column(db.Text, nullable=False) created_at = db.Column(db.TIMESTAMP, server_default=db.text('CURRENT_TIMESTAMP'), nullable=False) updated_at = db.Column(db.TIMESTAMP, server_default=db.text('CURRENT_TIMESTAMP'), server_onupdate=db.text('CURRENT_TIMESTAMP'), nullable=False)
def map(): latlong = Metar.query.from_statement( db.text(f"SELECT DISTINCT ON (station_id) station_id, * FROM metar;") ).all() MTN_OBSCN = Airsigmet.query.from_statement( db.text( "SELECT * FROM airsigmet WHERE valid_time_to >= NOW() AND hazard = 'MTN OBSCN';" )) IFR = Airsigmet.query.from_statement( db.text( "SELECT * FROM airsigmet WHERE valid_time_to >= NOW() AND hazard = 'IFR';" )) TURB = Airsigmet.query.from_statement( db.text( "SELECT * FROM airsigmet WHERE valid_time_to >= NOW() AND hazard = 'TURB';" )) ICE = Airsigmet.query.from_statement( db.text( "SELECT * FROM airsigmet WHERE valid_time_to >= NOW() AND hazard = 'ICE';" )) pireps = Pirep.query.from_statement( db.text( "SELECT * FROM pirep WHERE observation_time >= NOW() - INTERVAL '1 HOUR';" )).all() CONVECTIVE = Airsigmet.query.from_statement( db.text( "SELECT * FROM airsigmet WHERE valid_time_to >= NOW() AND hazard = 'CONVECTIVE';" )) ASH = Airsigmet.query.from_statement( db.text( "SELECT * FROM airsigmet WHERE valid_time_to >= NOW() AND hazard = 'ASH';" )) airplane = os.path.join(app.root_path, 'static', 'styles', 'airplane.png') print(airplane) try: url = f"http://api.ipstack.com/{request.headers['X-Real-IP']}?access_key={os.environ.get('IPSTACK')}" r = requests.get(url) j = json.loads(r.text) latitude = j['latitude'] longitude = j['longitude'] except: latitude = 44.967243 longitude = -103.771556 return render_template('map.html', title='Follow', latlong=latlong, airplane=airplane, MTN_OBSCN=MTN_OBSCN, pireps=pireps, IFR=IFR, TURB=TURB, ICE=ICE, CONVECTIVE=CONVECTIVE, ASH=ASH, latitude=latitude, longitude=longitude)
class User(db.Model): id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(64), index=True, unique=True) email = db.Column(db.String(120), index=True, unique=True) created_at = db.Column(db.DateTime, server_default=db.text('NOW()')) def __repr__(self): return '<User %r>' % (self.username)
class Criterion(db.Model): __tablename__ = 'criterions' id = db.Column( db.Integer, primary_key=True, server_default=db.text("nextval('criterias_id_seq'::regclass)")) name = db.Column(db.String)
class StageLv(db.Model): """灵基再临材料""" __tablename__ = 'stage_lv' id = db.Column(db.Integer, primary_key=True) servant_id = db.Column(db.Integer, db.ForeignKey('servants.id'), nullable=False) stage = db.Column(db.SmallInteger, nullable=False) item_id = db.Column(db.Integer, db.ForeignKey('items.id'), nullable=False) quantity = db.Column(db.Integer, nullable=False) note = db.Column(db.Text, nullable=True, server_default=None) created = db.Column(db.TIMESTAMP, nullable=False, server_default=db.text('CURRENT_TIMESTAMP')) updated = db.Column(db.TIMESTAMP, nullable=False, server_default=db.text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) deleted = db.Column(db.TIMESTAMP, nullable=True, server_default=None) servant = db.relationship('Servant', backref='stage_lv') item = db.relationship('Item', backref='stage_lv') def __repr__(self): return '<%s%s %d: %s>' % (self.__class__.__name__, '(%s)' % self.__doc__ if self.__doc__ is not None else str(), self.id, '%s 阶段 %d' % (self.servant.name, self.stage)) @staticmethod def validate(data): """ 输入数据验证 :param dict data: :return: bool, dict """ is_valid = True errors = {} return is_valid, errors @classmethod def edit(cls, data, servant_id): for stage_lv_data in data: stage_lv = \ cls.query.filter_by(servant_id=servant_id, stage=stage_lv_data['stage'], item_id=stage_lv_data['item_id']).first() \ or cls(servant_id=servant_id, stage=stage_lv_data['stage'], item_id=stage_lv_data['item_id'], quantity=stage_lv_data['quantity']) stage_lv.quantity = stage_lv_data['quantity'] db.session.add(stage_lv) db.session.commit()
def pagination(cls, limit=10, page=1, orderby='id', desc=True): query = cls.query count = query.count() records = [] if count: sort_by = '%s %s' % (orderby, 'DESC' if desc else 'ASC') records = query.order_by(db.text(sort_by)).limit( limit).offset((page - 1) * limit) return records, count
def posts_by_user(cls, user_id, limit=10, page=1, orderby='id', desc=True): query = cls.query.filter_by(user_id=user_id) count = query.count() records = [] if count: sort_by = '%s %s' % (orderby, 'DESC' if desc else 'ASC') records = query.order_by(db.text(sort_by)).limit( limit).offset((page - 1) * limit) return records, count
def display_repos_info(): counts=db.session.query(db.func.count('*')).select_from(RepoInfo).scalar() per_page=5 search=False q=request.args.get('q') if q: search=True try: page=int(request.args.get('page',1)) except ValueError: page=1 offset=(page-1)*per_page sql='select * from repos_info limit {},{}'.format(offset,per_page) repos=db.session.query(RepoInfo).from_statement(db.text(sql)).all() pagination = Pagination(page=page,link_size='sm',total=counts, record_name='repos',per_page=per_page,bs_version=3,format_total=True,format_number=True,show_single_page=False) return render_template('list-repo.html',repos=repos,pagination=pagination,page=page,per_page=per_page)
def display_deploy_info(): counts=db.session.query(db.func.count('*')).select_from(DeployInfo).scalar() print counts per_page=10 search=False q=request.args.get('q') if q: search=True try: page=int(request.args.get('page',1)) except ValueError: page=1 offset=(page-1)*per_page sql='select * from deploy_info order by deploy_info.deploy_date desc limit {},{}'.format(offset,per_page) deploy=db.session.query(DeployInfo).from_statement(db.text(sql)).all() pagination = Pagination(page=page,link_size='sm',total=counts, record_name='deploy',per_page=per_page,bs_version=3,format_total=True,format_number=True,show_single_page=False) return render_template('list-deploy-log.html',deploy=deploy,pagination=pagination,page=page,per_page=per_page)
def get_last_replies(thread): return db.session.query(Posts).filter_by(op_id = thread, deleted = 0).order_by(db.text('id desc')).limit(5)
def get_OPs_all(): return db.session.query(Posts).filter_by(op_id = '0' , deleted = 0).order_by(db.text('last_bump desc')).limit(10)
def get_user_posts(self, limit=10, page=1): total = self.posts.count() posts = self.posts.order_by(db.text("id DESC")).offset((page - 1) * limit).limit(limit) return posts, total
def get_OPs_catalog(board): return db.session.query(Posts).filter_by(op_id = '0', board = board, deleted = 0).order_by(db.text('last_bump desc')).limit(100)
def time_since_played(cls): return db.func.cast( db.func.extract(db.text('DAY'), db.func.now() - cls.played_at), db.Float)