예제 #1
0
 def commit_entries(self, end_batch_id):
     session = Session()
     current_staged_entry = session.query(StagedEntry).order_by(StagedEntry.entry_id)\
         .filter(StagedEntry.entry_id <= end_batch_id)
     while current_staged_entry is not None:
         self.commit_entry(current_staged_entry)
     session.close()
예제 #2
0
def food_edit_food_tags_form(request):
    session = Session()
    food = session.query(Food).filter_by(id=request.matchdict['id']).first()
    food_tags = session.query(FoodTag).order_by(FoodTag.name).all()
    session.close()
    # TODO: find a way to auto-include request in every one of these...?
    return {'request': request, 'title': 'Edit Tags for %s' % food.name, 'food': food, 'tags': food_tags}
예제 #3
0
def calorie_graph_data(request):
    session = Session()
    food_entries = session.query(FoodEntry).order_by(FoodEntry.date).all()
    food_days = FoodDay.group_days(food_entries)
    tags = session.query(FoodTag).order_by(FoodTag.id)
    session.close()
    return {'food_days': map(lambda x: x.to_dict(), food_days), 'tags': map(lambda x: x.to_dict(), tags)}
예제 #4
0
def food_tag_add(request):
    name = request.params['name']
    
    session = Session()
    new_tag = FoodTag(name)
    session.add(new_tag)
    session.commit()
    
    return HTTPFound('/food_tag/list')
예제 #5
0
def get_lat_and_long(postcode):
    session = Session()
    postcode_record = session.query(Locations).filter(
        Locations.pcds == postcode).first()
    session.close()
    if postcode_record is not None:
        return postcode_record.lat, postcode_record.long
    else:
        return None, None
예제 #6
0
def food_edit_food_tags(request):
    food_id = request.matchdict['id']
    tag_ids = map(int, request.params.getall('tag_id'))
    session = Session()
    food = session.query(Food).filter_by(id=food_id).first()
    already_tagged = []
    to_remove = []
    for food_tag in food.food_tags:
        if not food_tag.id in tag_ids:
            to_remove.append(food_tag)
        else:
            already_tagged.append(food_tag.id)
    for tag in to_remove:
        food.food_tags.remove(tag)
    to_add = session.query(FoodTag).filter(FoodTag.id.in_(set(tag_ids)-set(already_tagged))).all()
    food.food_tags.extend(to_add)
    session.commit()
    session = Session()
    food = session.query(Food).filter_by(id=food_id).first()
    session.close()
    
    if request.is_xhr:
        return {'request': request, 'food': food}
    else:
        return HTTPFound('/food/list')
예제 #7
0
def lose_it_upload(request):
    session = Session()
    
    # TODO: duplicate detection
    input_file = request.POST['file'].file
    reader = LoseItDataReader(input_file)
    existing_for_date = {}
    ignored = []
    for entry in reader:
        existing_food = session.query(Food).filter_by(name=entry.name).all()
        food = None
        if len(existing_food) == 0:
            food = Food(entry.name)
            session.add(food)
        else:
            food = existing_food[0]
        if not entry.date in existing_for_date:
            existing_entries = session.query(FoodEntry).filter_by(date=entry.date).all()
            existing_for_date[entry.date] = existing_entries
        if existing_for_date[entry.date]:
            ignored.append(entry)   
        else:
            new_entry = FoodEntry(food, entry.calories, entry.date)
            session.add(new_entry)
    
    session.commit()
    if ignored:
        return {'title': 'Existing Data Not Imported', 'ignored': ignored}
    else:
        return HTTPFound('/food_entry/lose_it_upload_form')
예제 #8
0
 def commit_entry(self, staged_entry):
     session = Session()
     indicator_values = {}
     for indicator in self.indicators_metadata:
         value_location = self.location_engine.convert_postcode(
             staged_entry.postcode,
             self.indicators_metadata[indicator]['resolution'])
         previous_value_entry = session.query(IndicatorValue)\
             .filter(IndicatorValue.indicator == indicator,
                     IndicatorValue.location == value_location,
                     IndicatorValue.date < staged_entry.date)\
             .order_by(IndicatorValue.date.desc())\
             .first()
         following_value_entry = session.query(IndicatorValue)\
             .filter(IndicatorValue.indicator == indicator,
                     IndicatorValue.location == value_location,
                     IndicatorValue.date > staged_entry.date)\
             .order_by(IndicatorValue.date)\
             .first()
         if previous_value_entry is None and following_value_entry is None:
             value = None
             indicator_date = None
         elif following_value_entry is None \
                 or abs((previous_value_entry.date - staged_entry.date).days) \
                 < abs((following_value_entry.date - staged_entry.date).days):
             value = previous_value_entry.value
             indicator_date = previous_value_entry.date
         else:
             value = following_value_entry.value
             indicator_date = following_value_entry.date
         # Check if value falls within frequency range
         if indicator_date is not None and (abs(staged_entry.date - indicator_date)).days\
                 > (FREQUENCY_DAY_COUNTS[self.indicators_metadata[indicator]['frequency']] / 2):
             value = None
         indicator_values[indicator] = value
     new_entry = TargetEntry(
         entry_id=staged_entry.entry_id,
         sale_id=staged_entry.sale_id,
         date=staged_entry.date,
         value=staged_entry.value,
         PDD_type=staged_entry.PDD_type,
         postcode=staged_entry.postcode,
         town_or_city=staged_entry.town_or_city,
         district=staged_entry.district,
         county=staged_entry.county,
         new_property_flag=staged_entry.new_property_flag,
         property_type=staged_entry.property_type,
         tenure_type=staged_entry.tenure_type)
     for indicator in indicator_values:
         setattr(new_entry, indicator, indicator_values[indicator])
     session.add(new_entry)
     session.commit()
     session.close()
예제 #9
0
 def run(self):
     running = True
     while running:
         session = Session()
         current_job = session.query(Job).order_by(
             Job.timestamp_when_valid).first()
         if current_job is None or current_job.timestamp_when_valid > datetime.datetime.now(
         ):
             sleep(JOB_MANAGER_POLL_DELAY)
         else:
             self.complete_job(current_job)
             current_job.delete()
         session.close()
예제 #10
0
def init_result():
    session = Session()
    entry = Session(Result).filter(Result.id == 1).one_or_none()
    # 如数据不存在,就新增一条为1的空数据
    if not entry:
        session.add(Result(modules="", total_time="", data="", dev=""))
        session.commit()
    else:
        pass
    session.close()
예제 #11
0
 def add_job(self, valid_datetime, job_type, details):
     session = Session()
     new_job = Job(timestamp_when_valid=valid_datetime,
                   job_type=job_type,
                   details=details)
     session.add(new_job)
     session.commit()
     session.close()
예제 #12
0
 def __init__(self, database_engine):
     self.database_engine = database_engine
     session = Session()
     for model_name in self.get_model_names():
         self.load_model(model_name)
     # Find largest model input count after all models loaded
     for model_name in self.get_trained_model_names():
         model_input_count = len(self.get_model_inputs(model_name))
         if model_input_count > self.max_inputs:
             self.max_inputs = model_input_count
     # Check for models that were in training when last shut down
     for model_record in session.query(ModelEntry).filter(
             ModelEntry.state == 'training'):
         self.train_model(model_record.name)
     session.close()
예제 #13
0
 def get_model_table(self):
     session = Session()
     models_entries = session.query(ModelEntry)
     table = []
     for model_entry in models_entries:
         dependencies = ", ".join(
             self.get_model_dependencies(model_entry.name))
         table.append({
             'Name': model_entry.name,
             'Type': model_entry.type,
             'Dataset': model_entry.dataset,
             'State': model_entry.state,
             'Dependencies': dependencies
         })
     session.close()
     return table
예제 #14
0
def main():
    engine = create_engine('postgresql://[email protected]/my_metrics')
    Base.metadata.create_all(engine)
    Session.configure(bind=engine)
    
    session = Session()
    food_entries = session.query(FoodEntry).order_by(FoodEntry.date)
    for entry in food_entries:
        food_name = entry.name
        existing_food = session.query(Food).filter_by(name=food_name).all()
        if len(existing_food) == 0:
            new_food = Food(food_name)
            session.add(new_food)
    session.commit()
예제 #15
0
def validate_students():
    with Session(engine) as session:
        students = session.execute(
            select(Student).\
            order_by(Student.last)
        ).scalars().all()

        q = list(filter(lambda x : not x.today().status, students))
    search(q)
예제 #16
0
def validate_leaders():
    with Session(engine) as session:
        leaders = session.execute(
            select(Student).\
            where(Student.leader_id==None).\
            order_by(Student.last)
        ).scalars().all()
        
        q = list(filter(lambda x : not x.today().status, leaders))
    search(q)
예제 #17
0
def setup_df(input_file):
    df = pd.read_csv(input_file, header=None)
    f_row = df.iloc[0].str.match(r"([A-Z][-a-zA-Z]*'?[-a-zA-Z]+), ([A-Z][a-z]*'?[-a-zA-Z]+)( [A-Z]'?[-a-zA-Z]*)? (\((\d{6})?\))?")
    
    if not f_row.any(axis=None):
        df = df.iloc[1:]

    f_col = df.iloc[:,0].str.match(r"([A-Z][-a-zA-Z]*'?[-a-zA-Z]+), ([A-Z][a-z]*'?[-a-zA-Z]+)( [A-Z]'?[-a-zA-Z]*)? (\((\d{6})?\))?")
    if not f_col.any(axis=None):
        df = df.iloc[:,1:]

    dfs = []
    for index, series in df.iterrows():
        c = series.str.extract(r"([A-Z][-a-zA-Z]*'?[-a-zA-Z]+), ([A-Z][a-z]*'?[-a-zA-Z]+)( [A-Z]'?[-a-zA-Z]*)? (\((\d{6})?\))?")
        c.drop(columns=3, inplace=True)
        c.dropna(how='all', inplace=True)
        c.rename(columns={0:'Last', 1:'First', 2:'Middle', 4:'ID'}, inplace=True)
        c.loc[c['ID'] != c.iloc[0, 3], 'Leader_ID'] = c.iloc[0, 3]
        c['ID'] = c['ID'].astype(int)
        c['Leader_ID'] = c['Leader_ID'].astype(float)
        dfs.append(c)

    with engine.begin() as conn:
        Base.metadata.create_all(conn)

        for r in dfs:
            r.to_sql("student", con=conn, if_exists='append', index=False)

            with Session(engine) as session, session.begin():
                for student_id in r.iloc[:, 3]:
                    attendance = Attendance(student_id=student_id)
                    session.add(attendance)

    with Session(engine) as session:
        people = session.execute(
            select(Student).\
            order_by(Student.last)
        ).scalars().all()

        data = [user.serialize for user in people]
        # print(data)
        # print("2:", session.execute(select(Attendance)).scalars().all())
        eel.create_table(data)
예제 #18
0
 def update_commit_schedule(self, first_pull_date, frequency):
     session = Session()
     # Calculate when the next pull is that cannot take place immediately
     next_scheduled_pull = first_pull_date
     while next_scheduled_pull < datetime.datetime.now():
         if frequency == 'yearly':
             next_scheduled_pull += relativedelta(years=1)
         elif frequency == 'monthly':
             next_scheduled_pull += relativedelta(months=1)
         else:
             next_scheduled_pull += relativedelta(
                 days=FREQUENCY_DAY_COUNTS[frequency])
     threshold_date = next_scheduled_pull - relativedelta(
         days=FREQUENCY_DAY_COUNTS[frequency] / 2)
     commits_before_threshold = session.query(Job).filter(
         Job.job_type == COMMIT_JOB,
         Job.timestamp_when_valid < threshold_date)
     for commit in commits_before_threshold:
         commit.timestamp_when_valid = next_scheduled_pull
     session.close()
예제 #19
0
 def add_new_model(self, settings):
     new_model_name = settings['name']
     new_model = Model(settings)
     self.models[new_model_name] = new_model
     self.save_model(new_model_name)
     new_model_record = ModelEntry(name=settings['name'],
                                   type=settings['type'],
                                   dataset=settings['dataset'],
                                   state='untrained')
     session = Session()
     session.add(new_model_record)
     session.commit()
     session.close()
예제 #20
0
def search(absent_list):
    global search_bar, width

    for index, user in enumerate(absent_list):
        name = f'{user.first} {user.last}'
        
        print(f"{len(absent_list) - index} remaining...")
        print(f"[?] Searching  : {name}")
        pug.click(search_bar)
        pug.typewrite(name)

        wait_label = capture.find_img_coordinates("waiting_room_label.png", "meeting")
        in_meeting_label = capture.find_img_coordinates("in_the_meeting_label.png", "meeting")
        
        if wait_label and in_meeting_label:
            x, y = wait_label[0] - 20, wait_label[1] + 10
            height = in_meeting_label[1] - (wait_label[1] + 25)

            wait_list = capture.get_text_coordinates(x, y, width, height)
            
            # MOVE TO NEXT STUDENT IF MORE THAN ONE NAME IS DETECTED
            if len(wait_list) > 1:
                return f"IMPOSTER DETECTED:<br/>{name}"
            
            print("WAIT LIST:", wait_list)
            for person in wait_list:
                cv_name = person['Text']
                if (cv_name != name) and len(cv_name) == len(name):
                    if spell_check({name : cv_name}) <= 2:
                        record(user)
                        admit(name, wait_list)
                elif cv_name == name:
                    print("NO SPELL:", name)
                    record(user)
                    admit(name, wait_list) 
            close_search()
        else:
            close_search()
            print("Could not locate labels.")
            return f'Could not<br/>locate labels.'

    with Session(engine) as session:
        students = session.execute(
            select(Student).\
            order_by(Student.last)
        ).scalars().all()
        
        data = [usr.serialize for usr in students]
        eel.create_table(data)
예제 #21
0
def food_entry_add(request):
    name = request.params['name']
    calories = int(request.params['calories'])
    day = date.today() - timedelta(int(request.params['days_ago']))
    
    session = Session()
    existing_food = session.query(Food).filter_by(name=food_name).all()
    food = None
    if len(existing_food) == 0:
        food = Food(food_name)
        session.add(food)
    else:
        food = existing_food[0]
    new_entry = FoodEntry(food, calories, day)
    session.add(new_entry)
    session.commit()
    
    return HTTPFound('/food_entry/add_form')
예제 #22
0
 def delete_model(self, model_name):
     session = Session()
     model_record = session.query(ModelEntry).filter(
         ModelEntry.name == model_name)
     model_record.delete()
     session.commit()
     del self.models[model_name]
     os.remove(model_name)
     # Update max inputs value
     self.max_inputs = 0
     for model_name in self.get_trained_model_names():
         model_input_count = len(self.get_model_inputs(model_name))
         if model_input_count > self.max_inputs:
             self.max_inputs = model_input_count
     session.close()
def main():
    engine = create_engine('postgresql://[email protected]/my_metrics')
    Base.metadata.create_all(engine)
    Session.configure(bind=engine)
    
    session = Session()
    foods = session.query(Food)
    foods_by_name = {}
    for food in foods:
        foods_by_name[food.name] = food
    
    food_entries = session.query(FoodEntry).order_by(FoodEntry.date)
    for entry in food_entries:
        food = foods_by_name[entry.name]
        entry.food = food
    session.commit()
예제 #24
0
 def get_model_names(self, dataset_name=None):
     session = Session()
     model_names = []
     if dataset_name is None:
         model_records = session.query(ModelEntry).all()
     else:
         model_records = session.query(ModelEntry).filter(
             ModelEntry.dataset == dataset_name)
     if model_records is not None:
         for model in model_records:
             model_names.append(model.name)
     session.close()
     return model_names
예제 #25
0
 def get_trained_model_names(self, dataset_name=None):
     model_names = []
     session = Session()
     if dataset_name is None:
         model_records = session.query(
             ModelEntry.name).filter(ModelEntry.state == 'trained')
     else:
         model_records = session.query(ModelEntry.name).filter(
             ModelEntry.dataset == dataset_name,
             ModelEntry.state == 'trained')
     for model in model_records:
         model_names.append(model.name)
     session.close()
     return model_names
예제 #26
0
 def update_entries_from_source(self, source_entry_date, frequency,
                                indicators, area_resolution):
     session = Session()
     frequency_radius_delta = relativedelta(
         FREQUENCY_DAY_COUNTS[frequency] / 2)
     lower_threshold_date = source_entry_date - frequency_radius_delta
     upper_threshold_date = source_entry_date + frequency_radius_delta
     # noinspection PyComparisonWithNone
     entries_to_update = session.query(TargetEntry).filter(
         getattr(TargetEntry, indicators[0]) == None,
         TargetEntry.date >= lower_threshold_date,
         TargetEntry.date <= upper_threshold_date)
     for entry in entries_to_update:
         location = self.location_engine.convert_postcode(
             entry.postcode, area_resolution)
         for indicator in indicators:
             value = session.query(IndicatorValue).filter(
                 IndicatorValue == source_entry_date,
                 IndicatorValue.location == location)
             setattr(entry, indicator, value)
     session.commit()
     session.close()
예제 #27
0
 def train_model(self, model_name):
     session = Session()
     model_record = session.query(ModelEntry).filter(ModelEntry.name == model_name).one()
     model_record.state = 'training'
     session.commit()
     parent_models = self.model_manager.models[model_name].input_models
     for parent_model in parent_models:
         # Train any untrained parent models
         if session.query(ModelEntry).filter(ModelEntry.name == parent_model, ModelEntry.state == 'untrained')\
                 is not None:
             self.train_model(parent_model)
         # Wait for any parent models that are still in training, in cases where another process started the training
         while session.query(ModelEntry).filter(ModelEntry.name == parent_model,
                                                     ModelEntry.state == 'training').one_or_none()\
                 is not None:
             time.sleep(30)
     model = self.model_manager.models[model_name]
     dataset_name = model.dataset
     entry_count = model.training_entry_count
     if dataset_name == 'core_dataset':
         dataframe = pandas.read_sql(session.query(Base.metadata.tables['core_dataset']).order_by(func.rand()).limit(entry_count).statement,
                                     self.database_engine)
     else:
         dataframe = pandas.read_csv(DEFAULT_DATA_PATH, sep='\s+', names=DEFAULT_DATA_HEADERS)
         if entry_count > len(dataframe):
             entry_count = len(dataframe)
         dataframe = dataframe.sample(entry_count)
     for parent_model in parent_models:
         self.recursive_process(parent_model, dataframe)
     model.train(dataframe)
     self.model_manager.save_model(model_name)
     model_record.state = 'trained'
     session.commit()
     # Update the model manager max input count - done here to happen at the end of training
     self.model_manager.update_max_inputs(len(self.model_manager.get_model_inputs(model_name)))
     session.close()
예제 #28
0
def calorie_graph(request):
    session = Session()
    food_entries = session.query(FoodEntry).order_by(FoodEntry.date)
    food_days = FoodDay.group_days(food_entries)
    session.close()
    return {'title': 'Calorie Graph', 'food_entries': food_entries, 'food_days': food_days}
예제 #29
0
def food_entry_list(request):
    session = Session()
    food_entries = session.query(FoodEntry).order_by(FoodEntry.date)
    food_days = FoodDay.group_days(food_entries)
    session.close()
    return {'title': 'Food Entries', 'food_entries': food_entries, 'food_days': food_days}
예제 #30
0
# create / retrieve / update / delete
from tables import Emp, Students,Session
from sqlalchemy import distinct

# 创建到数据库的会话实例
session = Session()

#第1题
# print(38*"*")
# print('\033[31;1m第一题:\033[0m')
# print('\033[32;1m%-8s %-5s\033[0m' % ('id','学生姓名'))
# qset1 = session.query(Students)
# for data in qset1:
#     print('\033[35;1m%-8s %-5s\033[0m' % (data.id, data.name))
# print(38*"*")

# #第2题
# print(38*"*")
# print('\033[31;1m第二题:\033[0m')
# print('\033[32;1m%s:%s\033[0m' % ('学生姓名','语文成绩'))
# qset2 = session.query(Students)
# for data in qset2:
#     print('\033[35;1m%s:%s\033[0m' % (data.name, data.chinese))
#     # print(data)
# print(38*"*")

# #第3题
# print(38*"*")
# print('\033[31;1m第三题:\033[0m')
# print('\033[32;1m公司职务\033[0m')
# qset3 = session.query(Emp.job).distinct().all()
예제 #31
0
# create / retrieve / update / delete
from tables import Emp, Students,Session

# 创建到数据库的会话实例
session = Session()

# 执行增删改查操作
# hr = Emp(dep_id=1, dep_name='人事部')
# ops = Emp(dep_id=2, dep_name='运维部')
# dev = Emp(dep_id=3, dep_name='开发部')
# qa = Emp(dep_id=4, dep_name='测试部')
# market = Emp(dep_id=5, dep_name='市场部')
# sales = Emp(dep_id=6, dep_name='销售部')
# session.add_all([hr, ops, dev, qa, market, sales])
u1 = Emp(empno=1009, ename='曾阿牛',job='董事长',hiredate='2001-11-17',sal=50000.00,deptno=10)
u2 = Emp(empno=1004, ename='刘备',job='经理',mgr=1009,hiredate='2001-04-02',sal=29750.00,deptno=20)
u3 = Emp(empno=1006, ename='关羽',job='经理',mgr=1009,hiredate='2001-05-01',sal=28500.00,deptno=30)
u4 = Emp(empno=1007, ename='张飞',job='经理',mgr=1009,hiredate='2001-09-01',sal=24500.00,deptno=10)
u5 = Emp(empno=1008, ename='诸葛亮',job='分析师',mgr=1004,hiredate='2007-04-19',sal=30000.00,deptno=20)
u6 = Emp(empno=1013, ename='庞统',job='分析师',mgr=1004,hiredate='2001-12-03',sal=30000.00,deptno=20)
u7 = Emp(empno=1002, ename='黛绮丝',job='销售员',mgr=1006,hiredate='2001-02-20',sal=16000.00,COMM=3000.00,deptno=30)
u8 = Emp(empno=1003, ename='殷天正',job='销售员',mgr=1006,hiredate='2001-02-22',sal=12500.00,COMM=5000.00,deptno=30)
u9 = Emp(empno=1005, ename='谢逊',job='销售员',mgr=1006,hiredate='2001-09-28',sal=12500.00,COMM=14000.00,deptno=30)
u10 = Emp(empno=1010, ename='韦一笑',job='销售员',mgr=1006,hiredate='2001-09-08',sal=15000.00,COMM=0.00,deptno=30)
u11= Emp(empno=1012, ename='程普',job='文员',mgr=1006,hiredate='2001-12-03',sal=9500.00,deptno=30)
u12= Emp(empno=1014, ename='黄盖',job='文员',mgr=1007,hiredate='2002-01-23',sal=13000.00,deptno=10)
u13 = Emp(empno=1011, ename='周泰',job='文员',mgr=1008,hiredate='2007-05-23',sal=11000.00,deptno=20)
u14 = Students(id=1, name='张小明',chinese=89,english=98,math=90)
u15 = Students(id=2, name='李进',chinese=67,english=98,math=89)
u16 = Students(id=3, name='王五',chinese=87,english=78,math=77)
u17 = Students(id=4, name='李一',chinese=88,english=98,math=90)
예제 #32
0
def record(user):
    with Session(engine) as session, session.begin():
        t = user.today()
        t.status = True
        session.add(t)
예제 #33
0
        return

    check_password = bcrypt.check_password_hash(user.password, password)
    if not check_password:
        return

    return user

def identity(payload):
    user_id = payload['identity']
    return session.query(User).filter(User.id == user_id).one_or_none()

jwt = JWT(app, authenticate, identity)


session = Session()


class EventUserSchema(Schema):
    event_id = fields.Integer()
    user_id = fields.Integer()
    # event_id = fields.Nested('EventSchema', many=True)
    # user_id = fields.Nested('UserSchema', many=True)

    @post_load
    def make_event(self, data, **kwargs):
        return EventUser(**data)


class EventSchema(Schema):
    id = fields.Integer()
예제 #34
0
def food_list(request):
    session = Session()
    foods = session.query(Food).order_by(Food.name)
    session.close()
    return {'title': 'Foods', 'foods': foods}
예제 #35
0
from tables import Session, User, Event

session = Session()

user1 = User(
    email='*****@*****.**',
    username='******',
    password='******'
)
user2 = User(
    email='*****@*****.**',
    username='******',
    password='******'
)
user3 = User(
    email='*****@*****.**',
    username='******',
    password='******'
)

event1 = Event(name='Event1',
               description='Description1',
               event_date='2020-12-11',
               organizer=user1,
               users=[user2, user3])
event2 = Event(name='Event2',
               description='Description2',
               event_date='2020-12-12',
               organizer=user2,
               users=[user1, user3])
event3 = Event(name='Event3',
예제 #36
0
def food_tag_list(request):
    session = Session()
    food_tags = session.query(FoodTag).order_by(FoodTag.name)
    session.close()
    return {'title': 'Food Tags', 'food_tags': food_tags}
예제 #37
0
# create / retrieve / update / delete
from tables import Emp, Students,Session
from sqlalchemy import distinct
from sqlalchemy import or_,desc,func

# 创建到数据库的会话实例
session = Session()

#第1题
print(38*"*")
print('\033[31;1m第一题:查询所有的学生\033[0m')
print('\033[32;1m%-8s %-5s\033[0m' % ('id','学生姓名'))
qset1 = session.query(Students).order_by(Students.id)
for data in qset1:
    print('\033[35;1m%-8s %-5s\033[0m' % (data.id, data.name))
print(38*"*")

#第2题
print(38*"*")
print('\033[31;1m第二题:查询学生姓名,语文\033[0m')
print('\033[32;1m%s:%s\033[0m' % ('学生姓名','语文成绩'))
qset2 = session.query(Students).order_by(Students.id)
for data in qset2:
    print('\033[35;1m%s:%s\033[0m' % (data.name, data.chinese))
    # print(data)
print(38*"*")

#第3题
print(38*"*")
print('\033[31;1m第三题:查询一个公司里面的工作岗位–清除重复数据\033[0m')
print('\033[32;1m公司职务\033[0m')
예제 #38
0
 def pull_land_registry(self):
     session = Session()
     data = request.urlopen(LAND_REGISTRY_URL).read()
     data = str(data, 'utf-8')
     data_frame = pandas.read_csv(StringIO(data),
                                  header=None,
                                  names=LAND_REGISTRY_DATA_HEADERS)
     # Only include entries with PPD type A
     # data_frame = [data_frame.PDD_type == 'A']
     # Convert date strings to date objects
     data_frame['date'] = pandas.to_datetime(
         data_frame['date'], format=LAND_REGISTRY_TIMESTAMP_FORMAT)
     # Reorder columns
     data_frame = data_frame[STAGED_ENTRY_HEADERS]
     # Convert old or new character value to boolean
     data_frame['new_property_flag'] = data_frame['new_property_flag'].map(
         dict(Y=True, N=False))
     current_highest_id = -1
     latest_entry = session.query(StagedEntry).order_by(
         desc(StagedEntry.entry_id)).first()
     if latest_entry is not None:
         current_highest_id = latest_entry.id
     batch_start_id = current_highest_id + 1
     batch_end_id = batch_start_id + len(data_frame)
     # Add id column
     data_frame.insert(0, 'entry_id', range(batch_start_id, batch_end_id))
     data_frame.to_sql('staged_entries',
                       con=self.database_engine,
                       if_exists='append',
                       index=False)
     # Process deletion entries
     deletion_entries = session.query(StagedEntry).filter(
         StagedEntry.record_type == 'D')
     for deletion_entry in deletion_entries:
         session.query(TargetEntry).filter(
             TargetEntry.sale_id == deletion_entry.sale_id).delete()
         deletion_entry.delete()
     # Get update entries
     update_entries = session.query(StagedEntry).filter(
         StagedEntry.record_type == 'C')
     for update_entry in update_entries:
         existing_entry = session.query(TargetEntry).filter(
             TargetEntry.sale_id == update_entry.sale_id).one()
         if existing_entry is not None:
             update_entry_from_land_registry(update_entry, existing_entry)
         update_entry.delete()
         session.commit()
     session.close()
     return str(batch_end_id)
예제 #39
0
def set_result(data):
    session = Session()
    entry = Session(Result).filter(Result.id == data["id"]).one_or_none()
    if entry:
        entry.modules = data["modules"]
        entry.total_time = data["total_time"]
        entry.data = data["data"]
        entry.dev = data["dev"]
        session.commit()
    else:
        print("实体类不存在")
    session.close()
예제 #40
0
import os
from wsgiref.simple_server import make_server
from pyramid.config import Configurator
from sqlalchemy import create_engine
from tables import Session

if __name__ == "__main__":
    engine = create_engine("postgresql://[email protected]/my_metrics")
    Session.configure(bind=engine)

    config = Configurator()
    config.add_renderer(name=".html", factory="renderers.jinja2_renderer.Jinja2Renderer")

    config.add_route("hello", "/hello/{name}")
    config.add_route("calorie-graph", "/graph")
    config.add_route("calorie-graph-data", "/graph/data")
    config.add_route("food-list", "/food/list")
    config.add_route("food-edit-food-tags-form", "/food/{id}/food_tags/edit_form")
    config.add_route("food-edit-food-tags", "/food/{id}/food_tags/edit", request_method="POST")
    config.add_route("food-tag-list", "/food_tag/list")
    config.add_route("food-tag-add-form", "/food_tag/add_form")
    config.add_route("food-tag-add", "/food_tag/add", request_method="POST")
    config.add_route("food-entry-add-form", "/food_entry/add_form")
    config.add_route("food-entry-add", "/food_entry/add", request_method="POST")
    config.add_route("food-entry-list", "/food_entry/list")
    config.add_route("lose-it-upload-form", "/food_entry/lose_it_upload_form")
    config.add_route("lose-it-upload", "/food_entry/lose_it_upload", request_method="POST")

    config.add_route("dynamic-css", "/css/{name}.css")

    # TODO: do dynamic file merging for JS like CSS