コード例 #1
0
def table_city(state, city):
    """It includes top 10 data"""

    qry = db.session.query(
        basic.name,
        func.count(state_fips.description), 
        func.round(func.avg(metrics.tuition_in_state)),
        func.round(func.avg(metrics.tuition_out_of_state)),
        func.round(func.avg(metrics.instructional_expenditure_per_fte)),
        func.round(func.avg(metrics.faculty_salary)),
        func.round(func.avg(metrics.tuition_revenue_per_fte)),
        ).filter(basic.id==metrics.id).filter(state_fips.code==basic.state_fips)

    qry = qry.filter(state_fips.description == state)
    qry = qry.filter(basic.city == city)
    qry = qry.group_by(basic.name) 
    qry = qry.order_by(basic.name)

    table_data = []
    for name,count,tuition_IS,tuition_OS, expenditure, faculty_Sal, revenue in qry:      
        
        json = {}
        json["State"] = name
        json["No_Schools"] = count
        json["tuitionIn"] = tuition_IS
        json["tuitionOut"] = tuition_OS
        json["expenditure"] = expenditure
        json["facSalary"] = faculty_Sal
        json["tuiRevenue"] = revenue
        table_data.append(json)

    # Return a list of the column names (sample names)
    json_data = jsonify(table_data)
    return json_data
コード例 #2
0
def metric_country():
    """It includes Country Averages"""

    qry = db.session.query(
        func.count(basic.state), 
        func.round(func.avg(metrics.tuition_in_state)),
        func.round(func.avg(metrics.tuition_out_of_state)),
        func.round(func.avg(metrics.instructional_expenditure_per_fte)),
        func.round(func.avg(metrics.faculty_salary)),
        func.round(func.avg(metrics.tuition_revenue_per_fte)),
        ).filter(basic.id==metrics.id)

    country_data = []
    for count,tuition_IS,tuition_OS, expenditure, faculty_Sal, revenue in qry:      
        
        json = {}
        json["No_Schools"] = count
        json["tuitionIn"] = tuition_IS
        json["tuitionOut"] = tuition_OS
        json["expenditure"] = expenditure
        json["facSalary"] = faculty_Sal
        json["tuiRevenue"] = revenue
        country_data.append(json)

    # Return a list of the column names (sample names)
    return jsonify(country_data)
コード例 #3
0
def produktionsmittel():
    produktionsmittel_qry = db.session.query(Kaeufe.id, Angebote.name, Angebote.beschreibung,\
        func.concat(func.round(Angebote.preis, 2), " Std.").label("preis"),\
        func.concat(func.round(func.coalesce(func.sum(Produktionsmittel.prozent_gebraucht), 0), 2), " %").\
            label("prozent_gebraucht"))\
        .select_from(Kaeufe)\
        .filter(Kaeufe.betrieb==current_user.id).outerjoin(Produktionsmittel,\
        Kaeufe.id==Produktionsmittel.kauf).join(Angebote, Kaeufe.angebot==Angebote.id).\
        group_by(Kaeufe, Angebote, Produktionsmittel.kauf)

    produktionsmittel_aktiv = produktionsmittel_qry.having(func.coalesce(func.sum(Produktionsmittel.prozent_gebraucht).\
    label("prozent_gebraucht"), 0).label("prozent_gebraucht")<100).all()
    produktionsmittel_inaktiv = produktionsmittel_qry.having(func.coalesce(func.sum(Produktionsmittel.prozent_gebraucht).\
    label("prozent_gebraucht"), 0).label("prozent_gebraucht")== 100).all()

    table_aktiv = ProduktionsmittelTable(
        produktionsmittel_aktiv,
        no_items="(Keine Produktionsmittel vorhanden.)")
    table_inaktiv = ProduktionsmittelTable(
        produktionsmittel_inaktiv,
        no_items="(Noch keine Produktionsmittel verbraucht.)")

    return render_template('produktionsmittel.html',
                           table_aktiv=table_aktiv,
                           table_inaktiv=table_inaktiv)
コード例 #4
0
ファイル: weather.py プロジェクト: gernot-h/weathermon
 def last_day(self):
     utc = timezone("UTC")
     eastern = timezone('US/Eastern')
     hours = 30
     now = datetime.utcnow()
     query = db.session.query(
             func.round(func.avg(Reading.temperature)),
             func.round(func.avg(Reading.humidity)),
             Reading.timestamp
         )\
         .filter_by(channel=self.channel)\
         .filter(Reading.timestamp > func.datetime('now', '-30 hours'))\
         .group_by(func.strftime('%d %H', Reading.timestamp))\
         .order_by(Reading.timestamp.desc())\
         .limit(hours)
     readings = query.all()
     times = ["" for i in range(hours)]
     est = utc.localize(datetime.utcnow()).astimezone(eastern)
     for i in range(0, hours):
         times[i] = "\"" + (
             est - timedelta(hours=i)).strftime("%I%p").lstrip('0') + "\""
     times.reverse()
     temps = [0 for i in range(hours)]
     hums = [0 for i in range(hours)]
     for r in readings:
         idx = hours - self._hours(now - r[2]) - 1
         temps[idx] = r[0] / 10
         hums[idx] = r[1]
     self.last_day_data = {
         "temperatures": temps,
         "humidities": hums,
         "timestamps": times
     }
     return self.last_day_data
コード例 #5
0
 def last_day(self):
     query = db.session.query(
             func.round(func.avg(Reading.temperature)),
             func.round(func.avg(Reading.humidity)),
             Reading.timestamp
         )\
         .filter_by(channel=self.channel)\
         .group_by(func.strftime('%H', Reading.timestamp))\
         .order_by(Reading.timestamp.desc())\
         .limit(30)
     return [{
         "temperature": r[0],
         "humidity": r[1],
         "timestamp": r[2]
     } for r in query.all()]
コード例 #6
0
async def get_sales_by_customer(from_date, to_date):
    """Return report about sales by customer in set period of time."""
    expression = order_details.c.unit_price * order_details.c.quantity * (1 - order_details.c.discount)
    query = select(
        [
            orders.c.customer_id,
            customers.c.company_name,
            func.round(func.sum(expression)).label('profit')
        ]
    ).select_from(
        join(join(orders, order_details, orders.c.order_id == order_details.c.order_id),
             customers, orders.c.customer_id == customers.c.customer_id)
    ).where(
        and_(
            orders.c.order_date >= from_date,
            orders.c.order_date <= to_date,
        )
    ).group_by(
        orders.c.customer_id,
        customers.c.company_name,
    ).order_by(
        desc('profit')
    )
    print(query)
    return await database.fetch_all(query=query)
コード例 #7
0
ファイル: weather.py プロジェクト: hackgrid/weathermon
 def last_day(self):
     query = db.session.query(
             func.round(func.avg(Reading.temperature)), 
             func.round(func.avg(Reading.humidity)),
             Reading.timestamp
         )\
         .filter_by(channel=self.channel)\
         .group_by(func.strftime('%H', Reading.timestamp))\
         .order_by(Reading.timestamp.desc())\
         .limit(30)
     return [
         {
             "temperature": r[0],
             "humidity": r[1],
             "timestamp": r[2]
         } for r in query.all()
     ]
コード例 #8
0
def tuition():
    """Return a dictionary of tuitions grouped by state."""

    qry = db.session.query(basic.state,func.round(func.avg(metrics.tuition_in_state)),func.round(func.avg(metrics.tuition_out_of_state))).filter(basic.id==metrics.id)
    qry = qry.group_by(basic.state) 

    tuition = []
    for state,tuition_IS,tuition_OS in qry:      
        
        json = {}
        json["State"] = state
        json["tuitionIn"] = tuition_IS
        json["tuitionOut"] = tuition_OS
        tuition.append(json)

    return jsonify(tuition)
コード例 #9
0
def get_total_chart(user_id):
    days_interval = datetime.today() - timedelta(days=365)
    total = db.session.query(
        Total.date, func.round(cast(func.sum(Total.value), NUMERIC),
                               2)).join(Total.portfolio).filter(
                                   Portfolio.user_id == user_id,
                                   Total.date >= days_interval).group_by(
                                       Total.date).order_by(Total.date).all()
    labels = []
    values = []

    for portfolio in total:
        date, value = portfolio
        labels.append(date)
        values.append(value)

    return labels, values
コード例 #10
0
def select_departments():
    """
    Select departments as a list of departments
    with such parameters as id, name, manager,
    phone, average salary, count of employees.
    :return: the list of departments
    """
    session = db.session
    departments = session.query(Department.id, Department.name,
                                Department.manager, Department.phone,
                                func.round(func.avg(Employee.salary), 2).label('average_salary'),
                                func.count(Employee.id).label('count_of_employees')) \
                         .select_from(Department) \
                         .join(Employee,isouter=True) \
                         .group_by(Department.id)
    session.close()
    return departments
コード例 #11
0
def meine_kaeufe():
    try:
        user_type = session["user_type"]
    except:
        user_type = "nutzer"

    if user_type == "betrieb":
        return redirect(url_for('auth.zurueck'))
    else:
        session["user_type"] = "nutzer"

        kaufhistorie = db.session.query(Kaeufe.id, Angebote.name, Angebote.beschreibung,\
            func.concat(func.round(Angebote.preis, 2), " Std.").label("preis")
            ).\
            select_from(Kaeufe).\
            filter_by(nutzer=current_user.id).\
            join(Angebote, Kaeufe.angebot==Angebote.id).all()
        kaufh_table = KaeufeTable(kaufhistorie, no_items="(Noch keine Käufe.)")
        return render_template('meine_kaeufe.html', kaufh_table=kaufh_table)
コード例 #12
0
def index():
    top_graded = Movie.query.join(Review)\
        .with_entities(Movie.title, Movie.year, Movie.id, func.round(func.avg(Review.grade), 2)\
        .label('avg'))\
        .group_by(Movie.id).order_by(desc('avg')).limit(10).all()
    newbies = Movie.query.order_by(Movie.timestamp.desc()).limit(10).all()
    form = RequestForm()
    if form.validate_on_submit() and current_user.is_authenticated:
        movreq = MovieRequest(user_id=current_user.id,
                              name=form.name.data,
                              year=form.year.data,
                              other_info=form.other_info.data)
        db.session.add(movreq)
        db.session.commit()
        flash('Request sent!')
        return redirect(url_for('main.index'))
    return render_template('index.html',
                           title='home',
                           top_graded=top_graded,
                           newbies=newbies,
                           form=form)
コード例 #13
0
 def test_index(self):
     response = self.client.get('/')
     department = Department('IT', 'Ivanov I.I', '+38(093)1548-093')
     employee_1 = Employee('Anna', 'Olekseevna', 'Ivanova', 22,
                           '1998-01-01', '+38(099)1548123', 'trainee', 2, 1,
                           10000)
     employee_2 = Employee('Inna', 'Igorevna', 'Leonova', 22, '1998-01-01',
                           '+38(099)1548123', 'trainee', 2, 1, 11000)
     db.session.add(department)
     db.session.add(employee_1)
     db.session.add(employee_2)
     db.session.commit()
     departments = db.session.query(
         Department.id, Department.name, Department.manager, Department.phone,
         func.round(func.avg(Employee.salary), 2).label('average_salary'), \
         func.count(Employee.id).label('count_of_employees')).select_from(Department). \
         join(Employee, isouter=True).group_by(Department.id)
     self.assertEqual(departments[0].id, 1)
     self.assertEqual(departments[0].name, 'IT')
     self.assertEqual(departments[0].manager, 'Ivanov I.I')
     self.assertEqual(departments[0].phone, '+38(093)1548-093')
     self.assertEqual(departments[0].average_salary, 10500.00)
     self.assertEqual(departments[0].count_of_employees, 2)
コード例 #14
0
ファイル: original.py プロジェクト: tutor-web/tutorweb.quizdb
    def updateAllocation(self, settings, question_cap=DEFAULT_QUESTION_CAP):
        # Get all existing allocations from the DB and their questions
        allocsByType = dict()
        hist_sel = float(settings.get('hist_sel', '0'))
        if hist_sel > 0.001:
            allocsByType['historical'] = []
            # Only get half the question cap if there's not much chance of the questions being used
            if hist_sel < 0.5 and 'question_cap_historical' not in settings:
                settings['question_cap_historical'] = int(settings.get('question_cap', DEFAULT_QUESTION_CAP)) / 2
        if hist_sel < 0.999:
            # NB: Need to add rows for each distinct question type, otherwise won't try and assign them
            allocsByType['regular'] = []
            allocsByType['template'] = []

        # Fetch all existing allocations, divide by allocType
        for (dbAlloc, dbQn) in (Session.query(db.Allocation, db.Question)
                .join(db.Question)
                .filter(db.Allocation.studentId == self.student.studentId)
                .filter(db.Allocation.active == True)
                .filter(db.Allocation.lectureId == self.dbLec.lectureId)):
            if not(dbQn.active) or (dbAlloc.allocationTime < dbQn.lastUpdate):
                # Question has been removed or is stale
                dbAlloc.active = False
            else:
                # Still around, so save it
                if (dbAlloc.allocType or dbQn.defAllocType) in allocsByType:
                    # NB: If hist_sel has changed, we might not want some types any more
                    allocsByType[dbAlloc.allocType or dbQn.defAllocType].append(dict(alloc=dbAlloc, question=dbQn))

        # Each question type should have at most question_cap questions
        for (allocType, allocs) in allocsByType.items():
            questionCap = int(settings.get('question_cap_' + allocType, settings.get('question_cap', DEFAULT_QUESTION_CAP)))

            # If there's too many allocs, throw some away
            for i in sorted(random.sample(xrange(len(allocs)), max(len(allocs) - questionCap, 0)), reverse=True):
                allocs[i]['alloc'].active = False
                del allocs[i]

            # If there's questions to spare, and requested to do so, reallocate questions
            if len(allocs) == questionCap and self.reAllocQuestions:
                if self.targetDifficulty is None:
                    raise ValueError("Must have a target difficulty to know what to remove")

                # Make ranking how likely questions are, based on targetDifficulty
                suitability = []
                for a in allocs:
                    if a['question'].timesAnswered == 0:
                        # New questions should be added regardless
                        suitability.append(1)
                    else:
                        suitability.append(1 - abs(self.targetDifficulty - float(a['question'].timesCorrect) / a['question'].timesAnswered))
                ranking = sorted(range(len(allocs)), key=lambda k: suitability[k])

                # Remove the least likely tenth
                for i in sorted(ranking[0:len(allocs) / 10 + 1], reverse=True):
                    allocs[i]['alloc'].active = False
                    del allocs[i]

            # Assign required questions randomly
            if len(allocs) < questionCap:
                query = Session.query(db.Question).filter_by(qnType='tw_questiontemplate' if allocType == 'template' else 'tw_latexquestion').filter_by(active=True)
                if allocType == 'historical':
                    # Get questions from lectures "before" the current one
                    targetQuestions = (Session.query(db.LectureQuestion.questionId)
                        .join(db.Lecture)
                        .filter(db.Lecture.plonePath.startswith(re.sub(r'/[^/]+/?$', '/', self.dbLec.plonePath)))
                        .filter(db.Lecture.plonePath < self.dbLec.plonePath)
                        .subquery())
                    query = query.filter(db.Question.questionId.in_(targetQuestions))
                else:
                    # Git questions from current lecture
                    query = query.filter(db.Question.lectures.contains(self.dbLec))

                # Filter out anything already allocated
                allocIds = [a['alloc'].questionId for a in allocs]
                if len(allocIds) > 0:
                    query = query.filter(~db.Question.questionId.in_(allocIds))

                # Give a target difficulty
                if self.targetDifficulty is not None:
                    query = query.order_by(func.abs(round(self.targetDifficulty * 50) - func.round((50.0 * db.Question.timesCorrect) / db.Question.timesAnswered)))

                for dbQn in query.order_by(func.random()).limit(max(questionCap - len(allocs), 0)):
                    dbAlloc = db.Allocation(
                        studentId=self.student.studentId,
                        questionId=dbQn.questionId,
                        lectureId=self.dbLec.lectureId,
                        allocationTime=datetime.datetime.utcnow(),
                        allocType='historical' if allocType == 'historical' else None,
                    )
                    Session.add(dbAlloc)
                    allocs.append(dict(alloc=dbAlloc, question=dbQn, new=True))

        Session.flush()
        for allocType, allocs in allocsByType.items():
            for a in allocs:
                yield (
                    self._questionUrl(a['alloc'].publicId),
                    allocType,
                    a['question'],
                )
コード例 #15
0
def get_ridership_w_sqlalchemy(summary_date):
    """"""

    # !!!As of now this function is incomplete, primarily due to the
    # fact that the passenger_census table doesn't appear to be a
    # part of the 'trans' sqlalchemy model at this time

    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker
    from sqlalchemy.sql import func

    from trimet.model.oracle.trans \
        import Location as loc, Passenger_Census as pc

    oracle_url = 'oracle://{user}:{pw}@{db}'
    engine = create_engine(oracle_url)
    Session = sessionmaker(bind=engine)
    session = Session()

    if not summary_date:
        summary_date = session.query(func.max(pc.summary_begin_date)).scalar()

    day_dict = dict()
    for service_day in ('W', 'S', 'U'):
        day_query = (
            session.query(
                pc.location_id,
                func.sum(pc.ons).label('ons'),
                func.sum(pc.offs).label('offs'),
                pc.summary_begin_date)
            .filter(pc.service_key == service_day)
            .group_by(pc.location_id, pc.summary_begin_date))

        day_dict[service_day] = day_query

    w = day_dict['W']
    s = day_dict['S']
    u = day_dict['U']

    rows = (
        session.query(
            loc.public_location_description.label('stop_desc'),
            w.ons.label('wkdy_ons'), w.offs.label('wkdy_offs'),
            s.ons.label('sat_ons'), s.offs.label('sat_offs'),
            u.ons.label('sun_ons'), u.offs.label('sun_offs'),
            func.round(loc.x_coordinate, 2).label('x_coord'),
            func.round(loc.y_coordinate, 2).label('y_coord'),
            w.summary_begin_date.label('begin_date'))
        .join(loc)
        .outerjoin(s)
        .outerjoin(u)
        .filter(
            loc.location_id == w.location.id,
            w.summary_begin_date == summary_date,
            w.location_id == s.location_id,
            w.summary_begin_date == s.summary_begin_date,
            s.location_id == u.location_id,
            s.summary_begin_date == u.summary_begin_date)
        .order_by(loc.location_id)
        .all()
    )

    # http://stackoverflow.com/questions/2258072
    row_types = [col.type for col in rows.columns]

    return rows, row_types
コード例 #16
0
ファイル: sqlalchemy_resource.py プロジェクト: gmat/lims
    def build_filter( 
        field_name, data_type, filter_type, inverted, value ):

        if DEBUG_FILTERS:
            logger.info('build filter: %r, %r, %r, %r, %r', 
                field_name, data_type, filter_type, inverted, value)
        
        expression = None
        col = column(field_name)
        if data_type in ['integer', 'float', 'decimal']:
            col = cast(col, sqlalchemy.sql.sqltypes.Numeric)
        elif data_type == 'boolean':
            col = cast(col, sqlalchemy.sql.sqltypes.Boolean)
        if data_type == 'string':
            col = cast(col, sqlalchemy.sql.sqltypes.Text)
        if filter_type in ['exact', 'eq']:
            if data_type == 'string':
                value = str(value)
            expression = col == value
            if data_type == 'list':
                expression = text(
                    "'%s'=any(string_to_array(%s,'%s'))" % (value, field_name, LIST_DELIMITER_SQL_ARRAY))
        elif filter_type == 'about':
            decimals = 0
            if '.' in value:
                decimals = len(value.split('.')[1])
            expression = func.round(col, decimals) == value
            if DEBUG_FILTERS:
                logger.info(
                    'create "about" expression for: %r, %r, decimals %r', 
                    field_name, value, decimals)
        elif filter_type == 'contains':
            if data_type == 'string':
                value = str(value)
            expression = col.contains(value)
        elif filter_type == 'icontains':
            if data_type == 'string':
                value = str(value)
            expression = col.ilike('%{value}%'.format(value=value))
        elif filter_type == 'lt':
            expression = col < value
        elif filter_type == 'lte':
            expression = col <= value
        elif filter_type == 'gt':
            expression = col > value
        elif filter_type == 'gte':
            expression = col >= value
        elif filter_type == 'is_blank':
            if data_type == 'string':
                col = func.trim(col)
            if value and str(value).lower() == 'true':
                expression = col == None
                if data_type == 'string':
                    expression = col == ''
            else:
                expression = col != None
                if data_type == 'string':
                    col = func.trim(col)
                if (data_type == 'string' or 
                    data_type == 'list'):
                    expression = col != ''
        elif filter_type == 'is_null':
            if value and str(value).lower() == 'true':
                expression = col == None
            else:
                expression = col != None
            # TODO: test that col <> '' expression is created
        elif filter_type == 'in':
            if data_type == 'list': # NOTE: for the list type, interpret "in" as any of the
                # given values are in the field
                temp_expressions = []
                for _val in value:
                    temp_expressions.append(col.ilike('%{value}%'.format(value=_val)))
                
                expression = or_(*temp_expressions)
            else:
                expression = col.in_(value)
        elif filter_type == 'ne':
            if data_type == 'string':
                value = str(value)
            expression = col != value
        elif filter_type == 'range':
            if len(value) != 2:
                logger.error('field: %r, val: %r, '
                    'range expression must be list of length 2', 
                    field_name, value)
            else:
                expression = col.between(value[0], value[1], symmetric=True)
        else:
            logger.error(
                'field: %r, unknown filter type: %r for value: %r', 
                field_name, filter_type, value)
        if inverted:
            expression = not_(expression)
        return expression
コード例 #17
0
ファイル: original.py プロジェクト: tutor-web/tutorweb.quizdb
    def updateAllocation(self, settings, question_cap=DEFAULT_QUESTION_CAP):
        # Get all existing allocations from the DB and their questions
        allocsByType = dict()
        hist_sel = float(settings.get('hist_sel', '0'))
        if hist_sel > 0.001:
            allocsByType['historical'] = []
            # Only get half the question cap if there's not much chance of the questions being used
            if hist_sel < 0.5 and 'question_cap_historical' not in settings:
                settings['question_cap_historical'] = int(
                    settings.get('question_cap', DEFAULT_QUESTION_CAP)) / 2
        if hist_sel < 0.999:
            # NB: Need to add rows for each distinct question type, otherwise won't try and assign them
            allocsByType['regular'] = []
            allocsByType['template'] = []

        # Fetch all existing allocations, divide by allocType
        for (dbAlloc, dbQn) in (Session.query(db.Allocation, db.Question).join(
                db.Question
        ).filter(db.Allocation.studentId == self.student.studentId).filter(
                db.Allocation.active == True).filter(
                    db.Allocation.lectureId == self.dbLec.lectureId).order_by(
                        db.Allocation.allocationId)):
            if not (dbQn.active) or (dbAlloc.allocationTime < dbQn.lastUpdate):
                # Question has been removed or is stale
                dbAlloc.active = False
            else:
                # Still around, so save it
                if (dbAlloc.allocType or dbQn.defAllocType) in allocsByType:
                    # NB: If hist_sel has changed, we might not want some types any more
                    allocsByType[dbAlloc.allocType
                                 or dbQn.defAllocType].append(
                                     dict(alloc=dbAlloc, question=dbQn))

        # Each question type should have at most question_cap questions
        for (allocType, allocs) in allocsByType.items():
            questionCap = int(
                settings.get(
                    'question_cap_' + allocType,
                    settings.get('question_cap', DEFAULT_QUESTION_CAP)))

            # If there's too many allocs, throw some away
            for i in sorted(random.sample(xrange(len(allocs)),
                                          max(len(allocs) - questionCap, 0)),
                            reverse=True):
                allocs[i]['alloc'].active = False
                del allocs[i]

            # If there's questions to spare, and requested to do so, throw away oldest questions
            if len(allocs) == questionCap and self.reAllocQuestions:
                for i in reversed(
                        xrange(
                            int(questionCap * float(
                                settings.get('allocation_realloc_perc', 20)) /
                                100))):
                    allocs[i]['alloc'].active = False
                    del allocs[i]

            # Assign required questions randomly
            if len(allocs) < questionCap:
                query = Session.query(db.Question).filter_by(
                    qnType='tw_questiontemplate' if allocType ==
                    'template' else 'tw_latexquestion').filter_by(active=True)
                if allocType == 'historical':
                    # Get questions from lectures "before" the current one
                    targetQuestions = (Session.query(
                        db.LectureQuestion.questionId).join(db.Lecture).filter(
                            db.Lecture.plonePath.startswith(
                                re.sub(r'/[^/]+/?$', '/',
                                       self.dbLec.plonePath))).filter(
                                           db.Lecture.plonePath <
                                           self.dbLec.plonePath).subquery())
                    query = query.filter(
                        db.Question.questionId.in_(targetQuestions))
                else:
                    # Git questions from current lecture
                    query = query.filter(
                        db.Question.lectures.contains(self.dbLec))

                # Filter out anything already allocated
                allocIds = [a['alloc'].questionId for a in allocs]
                if len(allocIds) > 0:
                    query = query.filter(~db.Question.questionId.in_(allocIds))

                # Give a target difficulty
                if self.targetDifficulty is not None:
                    query = query.order_by(
                        func.abs(
                            round(self.targetDifficulty * 50) -
                            func.round((50.0 * db.Question.timesCorrect) /
                                       db.Question.timesAnswered)))

                for dbQn in query.order_by(func.random()).limit(
                        max(questionCap - len(allocs), 0)):
                    dbAlloc = db.Allocation(
                        studentId=self.student.studentId,
                        questionId=dbQn.questionId,
                        lectureId=self.dbLec.lectureId,
                        allocationTime=datetime.datetime.utcnow(),
                        allocType='historical'
                        if allocType == 'historical' else None,
                    )
                    Session.add(dbAlloc)
                    allocs.append(dict(alloc=dbAlloc, question=dbQn, new=True))

        Session.flush()
        for allocType, allocs in allocsByType.items():
            for a in allocs:
                yield (
                    self._questionUrl(a['alloc'].publicId),
                    allocType,
                    a['question'],
                )
コード例 #18
0
s = select(c)
r = conn.execute(s)
results = r.fetchall()


for i in r.keys(): print(i)
print('\n')
for i in results[0]: print(i)

pl(29) # ______________________ 

#  Aggregate functions 

c = [
    func.sum(items.c.quantity), 
    func.round(func.avg(items.c.quantity), 2),
    func.max(items.c.quantity),
    func.min(items.c.quantity), 
    func.count(customers.c.id),
]

s = select(c)

r = conn.execute(s)

results = r.fetchall()

print(s, '\n')
for i in r.keys(): print(i)
print('\n')
for i in results[0]: print(i)
コード例 #19
0
ファイル: dialect.py プロジェクト: Kozea/Dyko
 def func_round(self, property, tree):
     return sqlfunctions.round(self.get_selectable(property.property, tree))
コード例 #20
0
ファイル: routes.py プロジェクト: grovesr/my-things-server
def getLevel1NodesWithInfo(exactFilterBy={}, 
                           likeFilterBy={}, 
                           orderField=None, 
                           orderDir=None, 
                           excludeRoot=False, 
                           infoDepth=None,
                           page=None,
                           perPage=None):
    if infoDepth != 3:
        raise BadRequest('Requesting level 1 nodes with information to a depth other than 3 not supported yet')
    if exactFilterBy.get('ownerId', None):
        ownerQuery = User.query.filter_by(id= exactFilterBy['ownerId'])
        if ownerQuery.count() == 0:
            raise NotFound('Invalid ownername specified in main/nodes/info/depth/3 request, so no nodes could be found')
        owner = ownerQuery.first()
    rootNode = Node.query.filter_by(parent=None).first()
    
    # get all nodes with the given filter
    all1 = db.session.query(Node)\
                  .filter_by(**exactFilterBy)
    for likeKey, likeValue in likeFilterBy.items():
        if likeKey == 'name':
            all1 = all1.filter(Node.name.ilike('%' + likeValue + '%'))
        if likeKey == 'description':
            all1 = all1.filter(Node.description.ilike('%' + likeValue + '%'))
        if likeKey == 'review':
            all1 = all1.filter(Node.review.ilike('%' + likeValue + '%'))
    all1 = all1.subquery()
    exactFilterBy['parentId'] = rootNode.id
    # get all sub nodes of main nodes under root node               
    sub1 = db.session.query(Node)\
                  .filter(Node.parentId.in_(db.session.query(Node.id)\
                  .filter_by(**exactFilterBy)))
    for likeKey, likeValue in likeFilterBy.items():
        if likeKey == 'name':
            sub1 = all1.filter(Node.name.ilike('%' + likeValue + '%'))
        if likeKey == 'description':
            sub1 = all1.filter(Node.description.ilike('%' + likeValue + '%'))
        if likeKey == 'review':
            sub1 = all1.filter(Node.review.ilike('%' + likeValue + '%'))
    sub1 = sub1.subquery()
    # accumulate all leaf item info and add to each sub item
    sub2 = db.session.query(sub1)\
                  .join(all1, sub1.c.id==all1.c.parentId)\
                  .distinct()\
                  .add_columns(select([func.avg(Node.rating)]).where(Node.parentId==sub1.c.id).label('averageRating'),
                               select([func.count()]).where(and_(Node.parentId==sub1.c.id, Node.need==True)).label('needChildren'),
                               select([func.count()]).where(and_(Node.parentId==sub1.c.id, Node.haveTried==True)).label('haveTriedChildren'),
                               select([func.count()]).where(Node.parentId==sub1.c.id).label('numberChildren'))\
                   .subquery()
    # get all main nodes
    main1 = db.session.query(Node)\
                  .filter_by(**exactFilterBy)
    for likeKey, likeValue in likeFilterBy.items():
        if likeKey == 'name':
            main1 = main1.filter(Node.name.ilike('%' + likeValue + '%'))
        if likeKey == 'description':
            main1 = main1.filter(Node.description.ilike('%' + likeValue + '%'))
        if likeKey == 'review':
            main1 = main1.filter(Node.review.ilike('%' + likeValue + '%'))
    if orderField != 'averageLeafRating':
        if orderDir == 'asc':
            main1 = main1.order_by(asc(orderField))
        if orderDir == 'desc':
            main1 = main1.order_by(desc(orderField))
    main1 = main1.subquery()
    asub2  = aliased(sub2)
    asub1=aliased(sub1)
    main1WithNumSubs = db.session.query(main1)\
                  .outerjoin(sub1, main1.c.id==sub1.c.parentId)\
                  .add_columns(select([func.count()]).where(asub1.c.parentId==main1.c.id).label('numberSubs'))\
                  .distinct()\
                  .subquery()
    # outer join sub nodes to main nodes on sub.parentId=main.id
    rowssq = db.session.query(main1WithNumSubs)\
                  .outerjoin(sub2, main1WithNumSubs.c.id==sub2.c.parentId)\
                  .add_columns(select([func.round(func.avg(asub2.c.averageRating))]).where(asub2.c.parentId==main1WithNumSubs.c.id).label('averageLeafRating'),
                               select([func.sum(asub2.c.needChildren)]).where(asub2.c.parentId==main1WithNumSubs.c.id).label('needLeaves'),
                               select([func.sum(asub2.c.haveTriedChildren)]).where(asub2.c.parentId==main1WithNumSubs.c.id).label('haveTriedLeaves'),
                               select([func.sum(asub2.c.numberChildren)]).where(asub2.c.parentId==main1WithNumSubs.c.id).label('numberLeaves'))\
                   .distinct()\
                   .subquery()
    if orderField == 'averageLeafRating':
        if orderDir == 'asc':
            rows = db.session.query(rowssq).filter(getattr(rowssq.c, orderField)!=None).order_by(asc(orderField))
        if orderDir == 'desc':
            rows = db.session.query(rowssq).filter(getattr(rowssq.c, orderField)!=None).order_by(desc(orderField))
    else:
        rows = db.session.query(rowssq)
    if perPage is None:
        #paginate
        perPage = rows.count()
        rowPages = rows.paginate(page=None, per_page=perPage)
    else:
        rowPages = rows.paginate(page=page, per_page=perPage)
    rows = rowPages.items
    nodes = []
    for row in rows:
        rowDict = row._asdict()
        node = Node.query.filter(Node.id==rowDict['id']).first()
        if node.nodeInfo is None:
            node.nodeInfo = {}
        nodeInfo = json.loads(node.nodeInfo)
        try:
            nodeInfo['averageLeafRating'] = int(rowDict.pop('averageLeafRating'))
        except TypeError:
            nodeInfo['averageLeafRating'] = None
        try:
            nodeInfo['needLeaves'] = int(rowDict.pop('needLeaves'))
        except TypeError:
            nodeInfo['needLeaves'] = 0
        try:
            nodeInfo['haveTriedLeaves'] = int(rowDict.pop('haveTriedLeaves'))
        except TypeError:
            nodeInfo['haveTriedLeaves'] = 0
        try:
            nodeInfo['numberLeaves'] = int(rowDict.pop('numberLeaves'))
        except TypeError:
            nodeInfo['numberLeaves'] = 0
        try:
            nodeInfo['numberSubs'] = int(rowDict.pop('numberSubs'))
        except TypeError:
            nodeInfo['numberSubs'] = 0
        node.nodeInfo = nodeInfo
        nodes.append(node)
    if(not excludeRoot):
        nodes.append(rootNode)
    db.session.commit()
    nodesJson = {'nodes':[]}
    nodesJson['nodeCount'] = len(nodes)
    nodesJson['totalNodes'] = rowPages.total
    nodesJson['page'] = rowPages.page
    nodesJson['pages'] = rowPages.pages
    nodesJson['perPage'] = rowPages.per_page
    nodesJson['nextPage'] = rowPages.next_num
    nodesJson['prevPage'] = rowPages.prev_num
    for node in nodes:
        node.childCount = len(node.children);
        nodesJson['nodes'].append(node.buildPublicJson())
    return jsonify(nodesJson)