예제 #1
0
def drop_all():
    """Drop appropriate tables."""
    User.__table__.drop(db.get_engine())
    Role.__table__.drop(db.get_engine())
    Mole.__table__.drop(db.get_engine())
    db.create_all()
    db.session.commit()
예제 #2
0
def teardown():
    app = create_app('test')
    with app.app_context():
        db.session.remove()
        db.drop_all()
        db.engine.execute("drop table alembic_version")
        db.get_engine(app).dispose()
예제 #3
0
def notify_db(notify_api, worker_id):
    assert 'test_notification_api' in db.engine.url.database, 'dont run tests against main db'

    # create a database for this worker thread -
    from flask import current_app
    current_app.config['SQLALCHEMY_DATABASE_URI'] += '_{}'.format(worker_id)
    current_app.config['SQLALCHEMY_DATABASE_READER_URI'] += '_{}'.format(worker_id)
    uri_db_writer = current_app.config['SQLALCHEMY_DATABASE_URI']
    uri_db_reader = current_app.config['SQLALCHEMY_DATABASE_READER_URI']
    current_app.config['SQLALCHEMY_BINDS'] = {
        'reader': uri_db_reader,
        'writer': uri_db_writer
    }
    create_test_db(uri_db_writer)

    BASE_DIR = os.path.dirname(os.path.dirname(__file__))
    ALEMBIC_CONFIG = os.path.join(BASE_DIR, 'migrations')
    config = Config(ALEMBIC_CONFIG + '/alembic.ini')
    config.set_main_option("script_location", ALEMBIC_CONFIG)

    with notify_api.app_context():
        upgrade(config, 'head')

    grant_test_db(uri_db_writer, uri_db_reader)

    yield db

    db.session.remove()
    db.get_engine(notify_api).dispose()
예제 #4
0
 def teardown_database(self):
     with self.app.app_context():
         db.session.remove()
         for table in reversed(db.metadata.sorted_tables):
             if table.name not in ["lots", "frameworks", "framework_lots"]:
                 db.engine.execute(table.delete())
         Framework.query.filter(Framework.id >= 100).delete()
         db.session.commit()
         db.get_engine(self.app).dispose()
예제 #5
0
    def get(self, search_term):
        """
        查询按资产类别分类的资产列表,
        返回匹配相关字符的全部分组结果(供搜索使用只返回前50条记录)
        """
        ret_dic = {"results": []}
        logger.debug('搜索条件:%s', search_term)
        param_str = "%%" + search_term + '%%'
        # 查找投资组合
        sql_str = """SELECT 'portfolio' AS asset_type, pl_id asset_code,
         name asset_name, concat(pl_id, ' : ', pl_info.name) text 
         FROM pl_info WHERE pl_info.name LIKE %s LIMIT 50"""
        data_df = pd.read_sql(sql_str, db.engine, params=[param_str])
        logger.debug('投资组合数据 %d 条数据', data_df.shape[0])
        if data_df.shape[0] > 0:
            data_dic = data_df.to_dict('record')
            ret_dic['results'].append({"text": "投资组合", "children": data_dic})
        # 查找指数
        sql_str = """SELECT 'index' AS asset_type, wind_code asset_code,
        sec_name asset_name, concat(wind_code, ' : ',sec_name) text
        FROM wind_index_info WHERE sec_name LIKE %s OR wind_code LIKE %s LIMIT 50"""
        data_df = pd.read_sql(sql_str,
                              db.get_engine(db.get_app(),
                                            bind=config.BIND_DB_NAME_MD),
                              params=[param_str, param_str])
        logger.debug('指数数据 %d 条数据', data_df.shape[0])
        if data_df.shape[0] > 0:
            data_dic = data_df.to_dict('record')
            ret_dic['results'].append({"text": "指数", "children": data_dic})
        # 查找股票
        sql_str = """SELECT 'stock' AS asset_type, wind_code asset_code, 
        sec_name asset_name, concat(wind_code, ' : ',sec_name) text 
        FROM wind_stock_info WHERE sec_name LIKE %s OR prename LIKE %s OR wind_code LIKE %s LIMIT 50"""
        data_df = pd.read_sql(sql_str,
                              db.get_engine(db.get_app(),
                                            bind=config.BIND_DB_NAME_MD),
                              params=[param_str, param_str, param_str])
        logger.debug('股票数据 %d 条数据', data_df.shape[0])
        if data_df.shape[0] > 0:
            data_dic = data_df.to_dict('record')
            ret_dic['results'].append({"text": "股票", "children": data_dic})
        # 查找期货
        sql_str = """SELECT 'future' AS asset_type, wind_code asset_code, 
        sec_name asset_name, concat(wind_code, ' : ',sec_name) text
        FROM wind_future_info WHERE sec_name LIKE %s OR wind_code LIKE %s LIMIT 50"""
        data_df = pd.read_sql(sql_str,
                              db.get_engine(db.get_app(),
                                            bind=config.BIND_DB_NAME_MD),
                              params=[param_str, param_str])
        logger.debug('期货数据 %d 条数据', data_df.shape[0])
        if data_df.shape[0] > 0:
            data_dic = data_df.to_dict('record')
            ret_dic['results'].append({"text": "期货", "children": data_dic})

        # logger.debug(ret_dic)
        # return jsonify(ret_dic)
        return ret_dic
예제 #6
0
 def teardown_database(self):
     with self.app.app_context():
         db.session.remove()
         for table in reversed(db.metadata.sorted_tables):
             if table.name not in ["lots", "frameworks", "framework_lots"]:
                 db.engine.execute(table.delete())
         Framework.query.filter(Framework.id >= 100).delete()
         db.session.commit()
         db.get_engine(self.app).dispose()
def teardown():
    app = create_app('test')
    with app.app_context():
        db.session.remove()
        db.drop_all()
        db.engine.execute("drop table alembic_version")
        insp = inspect(db.engine)
        for enum in insp.get_enums():
            db.Enum(name=enum['name']).drop(db.engine)
        db.get_engine(app).dispose()
예제 #8
0
def teardown():
    app = create_app('test')
    with app.app_context():
        db.session.remove()
        db.engine.execute("drop sequence suppliers_supplier_id_seq cascade")
        db.drop_all()
        db.engine.execute("drop table alembic_version")
        insp = inspect(db.engine)
        for enum in insp.get_enums():
            db.Enum(name=enum['name']).drop(db.engine)
        db.get_engine(app).dispose()
예제 #9
0
def test_db(test_app):
    assert 'wxd_test' in db.engine.url.database, 'Only run tests against the test database'

    BASE_DIR = os.path.dirname(os.path.dirname(__file__))
    ALEMBIC_CONFIG = os.path.join(BASE_DIR, 'migrations')
    config = Config(ALEMBIC_CONFIG + '/alembic.ini')
    config.set_main_option("script_location", ALEMBIC_CONFIG)

    upgrade(config, 'head')

    yield db

    db.session.remove()
    db.get_engine(test_app).dispose()
예제 #10
0
    def get():
        try:
            db.engine.execute(sql)
        except exc.SQLAlchemyError:
            return {"message": "api is down -- error connecting to colin"}, 500

        try:
            db.get_engine(app, 'db2').execute(sql)
        except exc.SQLAlchemyError:
            return {
                "message": "api is down -- error connecting to namesU"
            }, 500

        return {"message": "api is healthy"}, 200
 def teardown_database(self):
     with self.app.app_context():
         db.session.remove()
         for table in reversed(db.metadata.sorted_tables):
             if table.name not in [
                     "lot", "framework", "framework_lot",
                     "service_category", "service_role", "domain", "agency",
                     "council"
             ]:
                 db.engine.execute(table.delete())
         FrameworkLot.query.filter(
             FrameworkLot.framework_id >= 100).delete()
         Framework.query.filter(Framework.id >= 100).delete()
         db.session.commit()
         db.get_engine(self.app).dispose()
예제 #12
0
def get_gif_ids_by_tags(tag_ids, inactive_tags=None):
    my_db_bind = db.get_engine(app, 'gifs_db')
    tag_ids = [tag_id for tag_id in tag_ids if str(tag_id)]
    result = [gif.id for gif in models.Gif.query.all()]
    if tag_ids:
        print 'Active tags passed!'
        ids_string = '(' + ','.join(map(str, tag_ids)) + ')'
        sql = 'SELECT gif_id FROM gif_tags WHERE tag_id IN %s' % ids_string
        if inactive_tags is not None:
            print 'Also passing inactive tags!'
            inactive_ids_string = '(' + ','.join(map(str, inactive_tags)) + ')'
            sql += ' AND tag_id NOT IN %s' % inactive_ids_string

        rows = db.session.execute(sql, bind=my_db_bind)
        result = [entry[0] for entry in rows]
    else:
        print 'No active tags passed...'
        if inactive_tags is not None:
            print 'But passing inactive tags!'
            all_gif_ids = [gif.id for gif in models.Gif.query.all()]
            inactive_ids_string = '(' + ','.join(map(str, inactive_tags)) + ')'
            sql = 'SELECT gif_id FROM gif_tags WHERE tag_id IN %s' % inactive_ids_string

            rows = db.session.execute(sql, bind=my_db_bind)
            inactive_gif_ids = [entry[0] for entry in rows]
            result = filter_inactive_tags(all_gif_ids, inactive_gif_ids)

    return result
예제 #13
0
def calc_asset_rr(asset_code, date_from, date_to, asset_type) -> pd.Series:
    """
    计算资产日期区间的收益率
    :param asset_code:
    :param date_from:
    :param date_to:
    :param asset_type:
    :return:
    """
    # if asset_type == 'stock':
    #     table_name = 'wind_stock_daily'
    # elif asset_type == 'index':
    #     table_name = 'wind_index_daily'
    # elif asset_type == 'future':
    #     table_name = 'wind_future_daily'
    # else:
    #     raise ValueError("asset_type:%s 无效" % asset_type)
    bind_db, table_name = ASSET_TYPE_TABLE_NAME_DIC[asset_type]
    # TODO: 增加对 portfolio, value 两类资产的处理
    sql_str = """select trade_date, close from {table_name} where wind_code=%s and trade_date between %s and %s order by trade_date""".format(
        table_name=table_name)
    md_df = pd.read_sql(
        sql_str,
        db.get_engine(bind=bind_db) if bind_db is not None else db.engine,
        params=[asset_code, date_from, date_to],
        index_col='trade_date')
    rr_s = md_df["close"].pct_change().fillna(0).rename(asset_code)
    return rr_s
예제 #14
0
def get_last_14_imported_days(last_import_date) -> pd.DataFrame:
    sql = """   SELECT incidence."bfsNr",
                    incidence.date,
                    incidence.cases, 
                    municipality.population,
                    municipality.area,
                    municipality.name,
                    municipality.region,
                    municipality.canton
                FROM public.incidence
                LEFT JOIN public.municipality ON (incidence."bfsNr" = municipality."bfsNr")
                WHERE date BETWEEN '{}' AND '{}' AND incidence.cases IS NOT NULL
                ORDER BY incidence.date ASC""".format(
        last_import_date - timedelta(days=14), last_import_date)

    df_db = pd.read_sql_query(sql, db.get_engine())

    dict_db_cols = {
        'population': 'Einwohner',
        'area': 'Gesamtflaeche_in_km2',
        'name': 'Gemeindename',
        'region': 'Bezirksname',
        'canton': 'Kanton',
        'bfsNr': 'BFS_Nr',
        'date': 'Datum',
        'cases': 'Neue_Faelle_Gemeinde'
    }
    df = df_db[dict_db_cols.keys()].copy()
    df.rename(columns=dict_db_cols, inplace=True)

    return df
예제 #15
0
def crypto_data(subreddit):
    if subreddit in SUBREDDITS:
        all_logs = Log.query.filter(Log.subreddit == subreddit)
        df = pandas.read_sql(all_logs.statement, db.get_engine())

        df.drop(['id', 'subreddit', 'currency_sentiment'],
                axis=1,
                inplace=True)

        df_dict = df.to_dict(orient="records")

        status_and_results = {
            "subreddit": subreddit,
            "status": "success",
            "code": 200,
            "results": df_dict
        }
        response = make_response(json.dumps(status_and_results), 200)
        response.headers.add('Access-Control-Allow-Origin', '*')
        return response
    else:
        status_and_results = {
            "subreddit": subreddit,
            "status": "bad_request",
            "code": 400
        }
        response = make_response(json.dumps(status_and_results), 400)
        response.headers.add('Access-Control-Allow-Origin', '*')
        return response
예제 #16
0
    def query_friends_list(params):
        user_id = params["user_id"]
        if not user_id:
            return []

        sql = "SELECT t1.* FROM a_social.follow t1, a_social.follow t2 WHERE t1.user_id = t2.follow_id AND t1.follow_id = t2.user_id AND t1.status = 1 AND t2.status = 1 AND t1.user_id = "
        sql += str(params["user_id"])

        if params["last_id"]:
            sql += " AND t1.id < " + str(params["last_id"])

        sql += " ORDER BY t1.id DESC"

        if params["limit"]:
            sql += " LIMIT " + str(params["limit"])

        from heron import app
        result_dict_list = db.session.execute(
            sql, bind=db.get_engine(app=app, bind='a_social')).fetchall()
        if not result_dict_list:
            result_dict_list = []

        result = []
        for item in result_dict_list:
            model = FollowModel()
            model.id = item[0]
            model.user_id = item[1]
            model.follow_id = item[2]
            model.status = item[3]
            result.append(model)

        return result
예제 #17
0
def fetch_to_dict(sql, params={}, fecth='all', bind=None):
    """
    dict的方式返回数据
    :param sql: select * from xxx where name=:name
    :param params:{'name':'zhangsan'}
    :param fecth:默认返回全部数据,返回格式为[{},{}],如果fecth='one',返回单条数据,格式为dict
    :param bind:连接的数据,默认取配置的SQLALCHEMY_DATABASE_URL,
    :return:
    """
    resultProxy = db.session.execute(sql, params, bind=db.get_engine(bind=bind))
    if fecth == 'one':
        result_tuple = resultProxy.fetchone()
        if result_tuple:
            result = dict(zip(resultProxy.keys(), list(result_tuple)))
        else:
            return None
    else:
        result_tuple_list = resultProxy.fetchall()
        if result_tuple_list:
            result = []
            keys = resultProxy.keys()
            for row in result_tuple_list:
                result_row = dict(zip(keys, row))
                result.append(result_row)
        else:
            return None
    return result
예제 #18
0
def notify_db(notify_api):
    assert db.engine.url.database != 'notification_api', 'dont run tests against main db'
    Migrate(notify_api, db)
    Manager(db, MigrateCommand)
    BASE_DIR = os.path.dirname(os.path.dirname(__file__))
    ALEMBIC_CONFIG = os.path.join(BASE_DIR, 'migrations')
    config = Config(ALEMBIC_CONFIG + '/alembic.ini')
    config.set_main_option("script_location", ALEMBIC_CONFIG)

    with notify_api.app_context():
        upgrade(config, 'head')

    yield db

    db.session.remove()
    db.get_engine(notify_api).dispose()
예제 #19
0
def detection():
    datas = request.json
    detectionType = datas['detectionType']
    imageId = datas['imageid']
    username = datas['username']

    print("detection type %s , imageID %s, username %s." % (detectionType, imageId, username))

    #send msg to websocket to deteciton 

    user = User.query.filter_by(username=username).first()
    engine = db.get_engine(bind=user.dbname)
    DBSession = sessionmaker(bind=engine)
    db_session = DBSession()

    existResult = db_session.query(DetectionResult).filter_by(pathimage_imageid=imageId, detectionType=detectionType).all()
    if len(existResult) == 0:
        detectionResult = DetectionResult()
        detectionResult.detectionType = detectionType
        detectionResult.pathimage_imageid = imageId
        db_session.add(detectionResult)
        db_session.commit()

        image = db_session.query(PathImage).filter_by(imageid=imageId).first()
        db_session.close()

        detection_task.doDetection.apply_async(args=[image.filepath,imageId,detectionType,user.dbname])
        return "detecting"
    elif existResult[0].filepath is None or existResult[0].filepath == "":
        db_session.close()
        return "detecting"
    else:
        db_session.close()
        return existResult[0].filepath
예제 #20
0
    def tearDown(self):
        db.session.remove()

        # Replacement for db.session.drop_all()
        # http://www.mbeckler.org/blog/?p=218
        engine = db.get_engine(self.app)
        conn = engine.connect()
        trans = conn.begin()
        inspector = reflection.Inspector.from_engine(engine)
        metadata = MetaData()

        tbs = []
        all_fks = []

        for table_name in inspector.get_table_names():
            fks = []
            for fk in inspector.get_foreign_keys(table_name):
                if not fk['name']:
                    continue
                fks.append(
                    ForeignKeyConstraint((), (), name=fk['name'])
                )
            t = Table(table_name, metadata, *fks)
            tbs.append(t)
            all_fks.extend(fks)

        for fkc in all_fks:
            conn.execute(DropConstraint(fkc))

        for table in tbs:
            conn.execute(DropTable(table))

        trans.commit()

        engine.dispose()
def app():
    app = get_test_app()

    with app.app_context():

        server_config = ConfigManager.get_instance().get_server_config()
        application_root = ConfigManager.get_instance().get_application_root()

        app.register_blueprint(incidence_controller,
                               url_prefix=application_root)
        app.register_blueprint(municipality_controller,
                               url_prefix=application_root)
        app.register_blueprint(swaggerui_controller,
                               url_prefix=application_root)

        app.config['DEVELOPMENT'] = server_config["development"]

        with app.open_resource(
                "tests/api_blackbox/testdata/dump_municipalities.sql"
        ) as f_municipalities:
            with app.open_resource(
                    "tests/api_blackbox/testdata/dump_incidences.sql"
            ) as f_incidences:
                engine = db.get_engine()

                with engine.connect() as con:

                    create_incidence = '''
                    CREATE TABLE incidence (
                        "incidencesId" integer NOT NULL,
                        "bfsNr" integer NOT NULL,
                        date date NOT NULL,
                        incidence double precision NOT NULL,
                        cases integer NOT NULL,
                        cases_cumsum_14d integer NOT NULL
                    );
                    '''

                    create_municipality = '''
                    CREATE TABLE municipality (
                        "bfsNr" integer NOT NULL,
                        name character varying(256) NOT NULL,
                        canton character varying(2) NOT NULL,
                        area double precision NOT NULL,
                        population integer NOT NULL,
                        region character varying(256) NOT NULL
                    );
                    '''

                    con.execute(create_municipality)
                    con.execute(create_incidence)

                    query_municipalities = text(
                        f_municipalities.read().decode("utf8"))
                    con.execute(query_municipalities)
                    query_incidences = text(f_incidences.read().decode("utf8"))
                    con.execute(query_incidences)

        yield app
예제 #22
0
def image(filename):
    user = current_user
    engine = db.get_engine(bind=user.dbname)
    DBSession = sessionmaker(bind=engine)
    db_session = DBSession()

    annotationsInfo = db_session.query(Annotation).filter_by(pathimage_imageid=filename).all()

    slug = slugify(filename)
    slidempp = 'slide_mpp'
    imagesize = 'imagesize'
    if not hasattr(app,'slides'):
        app.slides = {}
        print('app.slides is null')


    if not app.slides.has_key(slug):
        print('image not exsits ' + slug)
        image = db_session.query(PathImage).filter_by(filename=filename).first()
        slidefile = image.filepath
        config_map = {
            'DEEPZOOM_TILE_SIZE': 'tile_size',
            'DEEPZOOM_OVERLAP': 'overlap',
            'DEEPZOOM_LIMIT_BOUNDS': 'limit_bounds',
        }
        opts = dict((v, app.config[k]) for k, v in config_map.items())

        if 'kfb' in slidefile:
            slide = kfbslide.KfbSlide(slidefile)
            deepzoom = kfb_deepzoom.KfbDeepZoomGenerator(slide, **opts)
        else:
            slide = open_slide(slidefile)
            deepzoom = DeepZoomGenerator(slide, **opts)

        slideinfo = {SLIDE_NAME: deepzoom}

        try:
            mpp_x = slide.properties[openslide.PROPERTY_NAME_MPP_X]
            mpp_y = slide.properties[openslide.PROPERTY_NAME_MPP_Y]
            slideinfo[slidempp] = (float(mpp_x) + float(mpp_y)) / 2
        except (KeyError, ValueError):
            slideinfo[slidempp] = 0
        slideinfo[imagesize] = [int(deepzoom.level_dimensions[-1][0]), int(deepzoom.level_dimensions[-1][1])]
        slideinfo['active'] = True
        app.slides[slug] = slideinfo

    annotaions = getannotations(annotationsInfo, app.slides[slug][imagesize], db_session,user)
    db_session.close()
    slidename = SLIDE_NAME + slug
    slide_url = url_for('dzi', slug=slidename)
    return render_template("display.html",
                           user=user,
                           slide_url=slide_url,
                           slide_mpp=app.slides[slug][slidempp],
                           canlogout=True,
                           image_name=filename,
                           annotations=annotaions,
                           imagesize=app.slides[slug][imagesize]
                           )
예제 #23
0
def test_user_add():
    # u = UserTest(name='fire')
    #db.session.add(u)
    #db.session.commit()
    name = 'fire'
    sql = ("insert into test_user(name) values('%s'),('feizi')") % name
    query = db.get_engine(app, bind='kakou').execute(sql)
    query.close()
예제 #24
0
    def teardown(self):
        db.session.remove()
        for table in reversed(db.metadata.sorted_tables):
            if table.name not in ["lots", "frameworks", "framework_lots"]:
                db.engine.execute(table.delete())
        FrameworkLot.query.filter(FrameworkLot.framework_id >= 100).delete()
        Framework.query.filter(Framework.id >= 100).delete()

        # Remove any framework variation details
        frameworks = db.session.query(Framework).filter(Framework.framework_agreement_details is not None)
        for framework in frameworks.all():
            framework.framework_agreement_details = None
            db.session.add(framework)

        db.session.commit()
        db.get_engine(self.app).dispose()
        self.app_context.pop()
    def teardown(self):
        db.session.remove()
        for table in reversed(db.metadata.sorted_tables):
            if table.name not in ["lots", "frameworks", "framework_lots"]:
                db.engine.execute(table.delete())
        FrameworkLot.query.filter(FrameworkLot.framework_id >= 100).delete()
        Framework.query.filter(Framework.id >= 100).delete()

        # Remove any framework variation details
        frameworks = db.session.query(Framework).filter(
            Framework.framework_agreement_details is not None)
        for framework in frameworks.all():
            framework.framework_agreement_details = None
            db.session.add(framework)

        db.session.commit()
        db.get_engine(self.app).dispose()
        self.app_context.pop()
예제 #26
0
 def teardown_database(self):
     with self.app.app_context():
         db.session.remove()
         for table in reversed(db.metadata.sorted_tables):
             if table.name not in [
                     "lot",
                     "framework",
                     "framework_lot",
                     "service_category",
                     "service_role",
                     "domain",
                     "agency",
                     "council"]:
                 db.engine.execute(table.delete())
         FrameworkLot.query.filter(FrameworkLot.framework_id >= 100).delete()
         Framework.query.filter(Framework.id >= 100).delete()
         db.session.commit()
         db.get_engine(self.app).dispose()
예제 #27
0
def get_last_import_date():
    max_date = None
    sql = "SELECT max(date) AS max_date FROM public.incidence WHERE cases IS NOT NULL"

    with db.get_engine().connect() as connection:
        result = connection.execute(sql)
        for row in result:
            max_date = row['max_date']

    return max_date
예제 #28
0
def test_stat():
    st = '2017-03-20 12:00:00'
    et = '2017-03-20 13:00:00'
    kkdd = 441302001
    sql = (
        "select count(*) from traffic_vehicle_pass where pass_time >='{0}' and pass_time <='{1}' and crossing_id = {2}"
        .format(st, et, kkdd))
    query = db.get_engine(app, bind='kakou').execute(sql)
    r = query.fetchone()[0]
    print r
예제 #29
0
def db(app):
    assert _db.engine.url.database.endswith('_test'), 'dont run tests against main db'

    create_test_db_if_does_not_exist(_db)

    Migrate(app, _db)
    Manager(_db, MigrateCommand)
    BASE_DIR = os.path.dirname(os.path.dirname(__file__))
    ALEMBIC_CONFIG = os.path.join(BASE_DIR, 'migrations')
    config = Config(ALEMBIC_CONFIG + '/alembic.ini')
    config.set_main_option("script_location", ALEMBIC_CONFIG)

    with app.app_context():
        upgrade(config, 'head')

    yield _db

    _db.session.remove()
    _db.get_engine(app).dispose()
예제 #30
0
def get_tag_gif_counts():
    my_db_bind = db.get_engine(app, 'gifs_db')
    all_tags = models.Tag.query.all()
    for tag in all_tags:
        sql = 'SELECT count(gif_id) FROM gif_tags WHERE tag_id = %s' % str(tag.id)
        rows = db.session.execute(sql, bind=my_db_bind)
        result = [entry[0] for entry in rows]
        tag.gif_count = int(result[0])

    return all_tags
예제 #31
0
def test_traffic_add2():
    vals = [
        u"('441302123', 1, 'IN', '粤L12345', '', '2015-12-13 01:23:45', '0')",
        u"('441302123', 1, 'IN', '粤L12345', '', '2015-12-13 01:23:45', '0')"
    ]
    print vals
    #print ','.join(vals)
    sql = (
        "insert into traffic(crossing_id, lane_no, direction_index, plate_no, plate_type, pass_time, plate_color) values %s"
    ) % ','.join(vals)
    query = db.get_engine(app, bind='kakou').execute(sql)
    query.close()
예제 #32
0
def allowed_table_name(table_schema=None):
    table_name = []
    if table_schema:
        data = db.get_engine(bind="information_schema").execute(
            "select distinct t.TABLE_NAME from columns t where t.TABLE_SCHEMA='{0}'"
            .format(table_schema)).fetchall()
        for value, lable in enumerate(data):
            table_name.append((value, lable))
        return table_name
    # columns = db.get_engine(bind="information_schema").execute(
    #     "select t.TABLE_SCHEMA,t.TABLE_NAME,t.COLUMN_NAME,t.ORDINAL_POSITION,t.IS_NULLABLE,t.DATA_TYPE,t.COLUMN_KEY,t.EXTRA from columns t where t.TABLE_SCHEMA='report'").fetchall()
    return None
예제 #33
0
    def get(self):
        df1 = pd.read_sql("select * from ttt", db.engine)
        # df1["registerdate"] = df1["registerdate"].apply(lambda x: x.strftime("%Y-%m-%d %H:%M:%S") if x is not pd.NaT else None)

        df1 = df1.replace({np.nan: None})
        for dtype in df1.dtypes:
            print(dtype)
        df1["birthday"] = df1["birthday"].apply(
            lambda x: x.strftime("%Y-%m-%d") if x is not None else None)
        df1["registerdate"] = df1["registerdate"].apply(lambda x: x.strftime(
            "%Y-%m-%d %H:%M:%S") if x is not None else None)

        df2 = pd.read_sql("select * from clinic_accrual_reg_rate limit 12",
                          db.get_engine(bind="xiaonuan")).replace(
                              {np.nan: None})

        # df3 = pd.read_sql("select brand,brand_code,clinic_name from data_xiaonuan_final.hospital_base_information", db.get_engine(bind="impala"))
        # print(df3)

        df4 = pd.read_sql(
            "select part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers from kylin_sales group by part_dt order by part_dt",
            db.get_engine(bind="kylin"))
        # print(df4)

        # data = {
        #     "local": df1.to_json(orient="records"),
        #     "xiaonuan": df2.to_json(orient="records"),
        #     "impala": df3.to_json(orient="records"),
        #     "kylin": df4.to_json(orient="records"),
        #     "testing": "I'm 中文测试"
        # }
        data = {
            "local": df1.to_dict("records"),
            "xiaonuan": df2.to_dict("records"),
            # "impala": df3[:12].to_dict("records"),
            "kylin": df4[:4].to_dict("records"),
            "testing": "I'm 中文测试"
        }

        return data
예제 #34
0
def detection_result(imageid,detectionType,username):
    user = User.query.filter_by(username=username).first()
    engine = db.get_engine(bind=user.dbname)
    DBSession = sessionmaker(bind=engine)
    db_session = DBSession()

    existResult = db_session.query(DetectionResult).filter_by(pathimage_imageid=imageid,
                                                              detectionType=detectionType).first()
    if existResult is not None and existResult.filepath is not None:
        with open(existResult.filepath,'rb') as f:
            resp = base64.b64encode(f.read())
            return resp
    return ''
예제 #35
0
def delete_where_cases_is_null(new_dates):
    if (len(new_dates) >= 1):
        string_dates = [
            f"'{new_date.strftime('%Y-%m-%d')}'" for new_date in new_dates
        ]
        sep = ', '
        date_filter = sep.join(string_dates)

        sql = f'DELETE FROM public.incidence WHERE cases IS NULL AND date IN ({date_filter});'

        with db.get_engine().connect() as connection:
            result = connection.execute(sql)
            return result
예제 #36
0
def fetch_usage_data_from_db():
    """
    Fetch the usage data to use it further
    """
    # big_data = FlaskUsage.query.all()
    pd_db = pd.read_sql_table('flask_usage', db.get_engine(bind='trackusage'))
    usage_dict = {}
    col_names = []
    for col_name in pd_db.columns:
        usage_dict[col_name] = pd_db[col_name]  # it's a dataframe!
        col_names.append(col_name)

    return usage_dict, col_names, pd_db
예제 #37
0
def get_gif_ids_by_tags(tags):
    tag_ids = []
    for tag in tags:
        tag_ids.append(models.Tag.query.filter_by(name=tag).first().id)

    ids_string = '(' + ','.join(map(str, tag_ids)) + ')'
    sql = 'SELECT gif_id FROM gif_tags WHERE tag_id IN %s' % ids_string

    my_db_bind = db.get_engine(app, 'gifs_db')
    rows = db.session.execute(sql, bind=my_db_bind)

    gifs_ids = [entry[0] for entry in rows]
    return gifs_ids
예제 #38
0
def get_gif_ids_by_tags(tags):
    tag_ids = []
    for tag in tags:
        tag_ids.append(models.Tag.query.filter_by(name=tag).first().id)

    ids_string = '(' + ','.join(map(str, tag_ids)) + ')'
    sql = 'SELECT gif_id FROM gif_tags WHERE tag_id IN %s' % ids_string

    my_db_bind = db.get_engine(app, 'gifs_db')
    rows = db.session.execute(sql, bind=my_db_bind)

    gifs_ids = [entry[0] for entry in rows]
    return gifs_ids
예제 #39
0
파일: service.py 프로젝트: eric-systex/chat
 def getUser(userId):
     logger.info('getUser start')
     result = ()
     try:
         engine = db.get_engine()
         conn = engine.connect()
         sql = "select userId id, userName name, photo avatar from vw_user where userId = %(userId)s and status != 'D'"
         rs = conn.execute(sql, {'userId': userId})
         result = rs.fetchall()
         result = [dict(row.items()) for row in result]
     except Exception as e:
         logger.error("getUser fail")
         logger.error(e)
     logger.info(result)
     return result[0]
예제 #40
0
def getNF(cname):
    nF='['
    try:
        resultset = db.session.execute("select * from "+ cname +"  order by 1",bind=db.get_engine(app, 'data'))
    except:        
        import sys
        resultset=''
    for r in resultset:
        nF+='[new Date("'+str(r[0])+'"),'+','.join(str(x) for x in list(r)[1:])+'],\n'
    config=Control_container.query.filter_by(name=cname).first()
    if config and str(config.g_config)!='None':
        config=str(config.g_config)
    else:
        if resultset!='':
            config='labels: ["'+'","'.join(resultset.keys())+'"],'
        else:
            config=''
    return nF+'],{'+config 
예제 #41
0
def main(app=create_app(), options=[]):

    with app.app_context():

        engine = db.get_engine(app)
        conn = engine.connect()
        trans = conn.begin()
        inspector = reflection.Inspector.from_engine(engine)
        metadata = MetaData()

        tbs = []
        all_fks = []

        for table_name in inspector.get_table_names():
            if table_name != 'alembic_version':
                fks = []
                for fk in inspector.get_foreign_keys(table_name):
                    if not fk['name']:
                        continue
                    fks.append(
                        ForeignKeyConstraint((), (), name=fk['name'])
                    )
                t = Table(table_name, metadata, *fks)
                tbs.append(t)
                all_fks.extend(fks)

        for fkc in all_fks:
            conn.execute(DropConstraint(fkc))

        for table in tbs:
            conn.execute(DropTable(table))

        trans.commit()
        db.create_all()
        engine.dispose()

        print "Deleted all existing data"

        # Audit triggers aren't in SQLAlchemy schema definition, so create_all
        # won't recreate them. Run the separate script to add them.
        if '-local' in options:
            os.system('psql -d {} -a -f app/audit_triggers.sql'.format(engine.url.database))
            print "Added audit triggers"
예제 #42
0
 def tearDownClass(cls):
     db.session.remove()
     db.drop_all()
     db.get_engine(cls.app).dispose()
예제 #43
0
def index():
    form = SearchPatientForm()
    if request.method == 'POST':
        session['first_name'] = form.search_patient_first_name.data
        session['last_name'] = form.search_patient_last_name.data
        # session['dob'] = form.search_patient_dob.data
        session['ssn'] = form.search_patient_ssn.data
        return redirect(url_for('screener.new_patient'))

    """Display the initial landing page, which lists patients in the
    network and allows users to search and filter them.
    """        

    all_patients = Patient.query.all()

    # ORGANIZATION-BASED QUERIES
    org_users = [user.id for user in AppUser.query.filter(
        AppUser.service_id == current_user.service_id
    )]

    # Get patients that this organization referred out who have results entered
    org_completed_referrals_outgoing = Patient.query.join(Patient.referrals).filter(
        Patient.referrals.any(
            and_(
                PatientReferral.from_app_user_id.in_(org_users),
                PatientReferral.status == 'COMPLETED'
            )
        )
    ).order_by(func.coalesce(PatientReferral.last_modified, PatientReferral.created))

    # Get patients that this organization referred out who are waiting for results
    org_open_referrals_outgoing = Patient.query.join(Patient.referrals).filter(
        Patient.referrals.any(
            and_(
                PatientReferral.from_app_user_id.in_(org_users),
                PatientReferral.status.in_(('SENT', 'RECEIVED'))
            )
        )
    ).order_by(func.coalesce(PatientReferral.last_modified, PatientReferral.created))

    # Get patients with open referrals at this user's organization
    org_open_referrals_incoming = Patient.query.join(Patient.referrals).filter(
        Patient.referrals.any(and_(
            PatientReferral.to_service_id == current_user.service_id,
            PatientReferral.status.in_(('SENT', 'RECEIVED'))
        ))
    ).order_by(func.coalesce(PatientReferral.last_modified, PatientReferral.created))

    # Get patients with completed referrals at this user's organization
    org_completed_referrals_incoming = Patient.query.join(Patient.referrals).filter(
        Patient.referrals.any(and_(
            PatientReferral.to_service_id == current_user.service_id,
            PatientReferral.status == 'COMPLETED'
        ))
    ).order_by(func.coalesce(PatientReferral.last_modified, PatientReferral.created))

    # Get patients who were most recently screened and found eligible for this organization
    # more than 11 months ago
    # No idea how to do this in SQLAlchemy
    query = text(
        "select * from ( "
            "select "
                "patient.id, "
                "patient.first_name, "
                "patient.middle_name, "
                "patient.last_name, "
                "patient.dob, "
                "max(patient_screening_result.created) as most_recent_result "
            "from "
                "patient, "
                "patient_screening_result "
            "where "
                "patient.id = patient_screening_result.patient_id "
                "and patient_screening_result.eligible_yn = 'Y' "
                "and patient_screening_result.service_id = :service_id "
            "group by "
                "patient.id, "
                "patient.first_name, "
                "patient.middle_name, "
                "patient.last_name, "
                "patient.dob "
        ") subquery where most_recent_result < :eleven_months_ago "
        "order by subquery.most_recent_result "
    )
    conn = db.get_engine(current_app).connect()
    org_need_renewal = conn.execute(
        query,
        service_id=current_user.service_id,
        eleven_months_ago=datetime.date.today() - relativedelta(months=11)
    ).fetchall()

    # USER-BASED QUERIES
    # Get patients this user created or updated in the last week
    your_recently_updated = Patient.query.filter(or_(
        and_(
            Patient.last_modified > datetime.date.today() - datetime.timedelta(days=7),
            Patient.last_modified_by_id == current_user.id
        ),
        and_(
            Patient.created > datetime.date.today() - datetime.timedelta(days=7),
            Patient.created_by_id == current_user.id
        )
    )).order_by(func.coalesce(Patient.last_modified, Patient.created))

    # Get patients this user referred out who have results entered
    your_completed_referrals_outgoing = Patient.query.join(Patient.referrals).filter(
        Patient.referrals.any(
            and_(
                PatientReferral.from_app_user_id == current_user.id,
                PatientReferral.status == 'COMPLETED'
            )
        )
    ).order_by(func.coalesce(PatientReferral.last_modified, PatientReferral.created))

    # Get patients this user referred out who are waiting for results
    your_open_referrals_outgoing = Patient.query.join(Patient.referrals).filter(
        Patient.referrals.any(
            and_(
                PatientReferral.from_app_user_id == current_user.id,
                PatientReferral.status.in_(('SENT', 'RECEIVED'))
            )
        )
    ).order_by(func.coalesce(PatientReferral.last_modified, PatientReferral.created))

    # Queries to maybe add later:
    # Your starred patients
    # Applications that are inactive/patient not responding

    return render_template(
        'index.html',
        user=current_user,
        all_patients=all_patients,
        org_completed_referrals_outgoing=org_completed_referrals_outgoing,
        org_open_referrals_outgoing=org_open_referrals_outgoing,
        org_open_referrals_incoming=org_open_referrals_incoming,
        org_completed_referrals_incoming=org_completed_referrals_incoming,
        org_need_renewal=org_need_renewal,
        your_recently_updated=your_recently_updated,
        your_completed_referrals_outgoing=your_completed_referrals_outgoing,
        your_open_referrals_outgoing=your_open_referrals_outgoing,
        form=form
    )
예제 #44
0
def test_final_find():
    sql = ("select max(id) from final_lm")
    q = db.get_engine(app, bind='kakou').execute(sql).fetchone()
    print q[0]
예제 #45
0
 def teardown():
     db.session.remove()
     db.drop_all()
     db.engine.execute("drop table alembic_version")
     db.get_engine(notify_api).dispose()
예제 #46
0
def index():
    """Display the initial landing page, which lists patients in the
    network and allows users to search and filter them.
    """

    form = SearchPatientForm()
    if request.method == 'POST':
        session['first_name'] = form.search_patient_first_name.data
        session['last_name'] = form.search_patient_last_name.data
        # session['dob'] = form.search_patient_dob.data
        session['ssn'] = form.search_patient_ssn.data
        return redirect(url_for('screener.new_patient'))

    all_patients = Patient.query.all()

    # ORGANIZATION-BASED QUERIES
    org_users = [user.id for user in AppUser.query.filter(
        AppUser.service_id == current_user.service_id
    )]

    # Get patients that this organization referred out who have open referrals or
    # referrals closed in the last month
    org_referrals_outgoing = db.session.query(
        Patient.id,
        Patient.first_name,
        Patient.last_name,
        Patient.dob,
        func.max(func.coalesce(PatientReferral.last_modified, PatientReferral.created)).label(
            "referral_last_modified"
        )
    ).join(Patient.referrals).filter(
        and_(
            or_(
                and_(
                    and_(
                        PatientReferral.from_app_user_id.in_(org_users),
                        PatientReferral.status == 'COMPLETED'
                    ),
                    func.coalesce(
                        PatientReferral.last_modified, PatientReferral.created
                    ) > datetime.date.today() - relativedelta(months=1)
                ),
                and_(
                    PatientReferral.from_app_user_id.in_(org_users),
                    PatientReferral.status == 'SENT'
                )
            ),
            Patient.deleted == None
        )
    ).group_by(
        Patient.id, Patient.first_name, Patient.last_name
    ).order_by(
        desc(func.max(func.coalesce(PatientReferral.last_modified, PatientReferral.created)))
    )

    # Get patients with open referrals or referrals closed in the last month at
    # this user's organization
    org_referrals_incoming = db.session.query(
        Patient.id,
        Patient.first_name,
        Patient.last_name,
        Patient.dob,
        func.max(func.coalesce(PatientReferral.last_modified, PatientReferral.created)).label(
            "referral_last_modified"
        )
    ).join(Patient.referrals).filter(
        and_(
            or_(
                and_(
                    PatientReferral.to_service_id == current_user.service_id,
                    PatientReferral.status == 'SENT'
                ),
                and_(
                    and_(
                        PatientReferral.to_service_id == current_user.service_id,
                        PatientReferral.status == 'COMPLETED'
                    ),
                    func.coalesce(
                        PatientReferral.last_modified, PatientReferral.created
                    ) > datetime.date.today() - relativedelta(months=1)
                )
            ),
            Patient.deleted == None
        )
    ).group_by(
        Patient.id, Patient.first_name, Patient.last_name
    ).order_by(
        desc(func.max(func.coalesce(PatientReferral.last_modified, PatientReferral.created)))
    )

    # Get patients who were most recently screened and found eligible for this organization
    # more than 11 months ago
    # No idea how to do this in SQLAlchemy
    query = text(
        "select * from ( "
            "select "
                "patient.id, "
                "patient.first_name, "
                "patient.middle_name, "
                "patient.last_name, "
                "patient.dob, "
                "max(patient_screening_result.created) as most_recent_result "
            "from "
                "patient, "
                "patient_screening_result "
            "where "
                "patient.id = patient_screening_result.patient_id "
                "and patient_screening_result.eligible_yn = 'Y' "
                "and patient_screening_result.service_id = :service_id "
                "and patient.deleted is null "
            "group by "
                "patient.id, "
                "patient.first_name, "
                "patient.middle_name, "
                "patient.last_name, "
                "patient.dob "
        ") subquery where most_recent_result < :eleven_months_ago "
        "order by subquery.most_recent_result "
    )
    conn = db.get_engine(current_app).connect()
    org_need_renewal = conn.execute(
        query,
        service_id=current_user.service_id,
        eleven_months_ago=datetime.date.today() - relativedelta(months=11)
    ).fetchall()

    # USER-BASED QUERIES
    # Get patients this user created or updated in the last week
    your_recently_updated = Patient.query.filter(or_(
        and_(
            Patient.last_modified > datetime.date.today() - datetime.timedelta(days=7),
            Patient.last_modified_by_id == current_user.id,
            Patient.deleted == None
        ),
        and_(
            Patient.created > datetime.date.today() - datetime.timedelta(days=7),
            Patient.created_by_id == current_user.id,
            Patient.deleted == None
        )
    )).order_by(desc(func.coalesce(Patient.last_modified, Patient.created)))

    # Get patients this user referred out who have open referrals or referrals closed in
    # the last month
    your_referrals_outgoing = db.session.query(
        Patient.id,
        Patient.first_name,
        Patient.last_name,
        Patient.dob,
        func.max(func.coalesce(PatientReferral.last_modified, PatientReferral.created)).label(
            "referral_last_modified"
        )
    ).join(Patient.referrals).filter(
        and_(
            or_(
                and_(
                    and_(
                        PatientReferral.from_app_user_id == current_user.id,
                        PatientReferral.status == 'COMPLETED'
                    ),
                    func.coalesce(
                        PatientReferral.last_modified, PatientReferral.created
                    ) > datetime.date.today() - relativedelta(months=1)
                ),
                and_(
                    PatientReferral.from_app_user_id == current_user.id,
                    PatientReferral.status == 'SENT'
                )
            ),
            Patient.deleted == None
        )
    ).group_by(
        Patient.id, Patient.first_name, Patient.last_name
    ).order_by(
        desc(func.max(func.coalesce(PatientReferral.last_modified, PatientReferral.created)))
    )

    patient_ids = []
    for patient_list in [
        org_referrals_outgoing,
        org_referrals_incoming,
        org_need_renewal,
        your_recently_updated,
        your_referrals_outgoing
    ]:
        patient_ids += [patient.id for patient in patient_list]

    patients = Patient.query.filter(Patient.id.in_(patient_ids))
    patient_dict = {patient.id: patient for patient in patients}

    return render_template(
        'index.html',
        user=current_user,
        all_patients=all_patients,
        patient_dict=patient_dict,
        org_referrals_outgoing=org_referrals_outgoing,
        org_referrals_incoming=org_referrals_incoming,
        org_need_renewal=org_need_renewal,
        your_recently_updated=your_recently_updated,
        your_referrals_outgoing=your_referrals_outgoing,
        one_month_ago=datetime.datetime.today() - relativedelta(months=1),
        form=form
    )
예제 #47
0
파일: __init__.py 프로젝트: shcalm/Moments
def del_post():
    engine = db.get_engine(db.get_app())
    post_up.drop(engine)

    return 'post_up is del'