Python connections.Connections类代码示例

def top_traded():
    map_func = Code ("""function() {
                    if (this.occ_nb_replace == 0){
                        emit(this.cheuvreux_secid, this.turnover * this.rate_to_euro)
                        }
                    }""")
                    
    reduce_func = Code (""" function(sec_id, quantity) {
                        return Array.sum(quantity)
                        }
                        """)
    Connections.change_connections("production")
    db = Connections.getClient("MARS")["Mars"]["AlgoOrders"]
    result = db.map_reduce(map_func, reduce_func, "my_result")
    
    turnover_executed = {}
    for doc in result.find():    
        if doc[u"_id"] is not None and doc[u"value"] > 0: 
            turnover_executed[int(doc[u"_id"])] = int(doc[u"value"])
    
    sorted_x = sorted(turnover_executed.iteritems(), key= lambda x: x[1], reverse = True)
    
    sec_ids = []
    keys = []
    values = []
    for i in range(20):
        sec_ids.append(sorted_x[i][0])
        keys.append(convert_symbol(source = "security_id", dest = "security_name", value = sorted_x[i][0])[0][0])
        values.append(sorted_x[i][1])
        print convert_symbol(source = "security_id", dest = "security_name", value = sorted_x[i][0])
    for i in range(20):
        print sorted_x[i][1]    
    figure, ax = plt.subplots(1, 1)
    ax.bar(range(20), values)
    plt.show()

def get_data():
    Connections.change_connections("production")
    
    query = """ select occ.IdOccurence,
        occ.SecurityRateToEuro,
        occ.SecurityId,
        occ.Side,     
        seq.StartTime, 
        seq.EndTime, 
        seq.AutomatonName,         
        occ.OrderDate,
        seq.AutomatonStatTime, 
        seq.AutomatonEndTime, 
        seq.ExecutedAmount, 
        seq.ExecutedQuantity
            
        from dm_algo..Algo_Sequence seq, dm_algo..Algo_Occurence occ
        where occ.OrderDate > '20130101' and occ.OrderDate < '20130201' 
        and occ.IdOccurence = seq.IdOccurence 
        and seq.ExecutedQuantity is not NULL"""
    
    #query = """select top 10 * from dm_algo..Algo_Occurence        """
    
    result = Connections.exec_sql("VEGA", query, as_dict = True)
    print "Fetched", len(result), "lines"
    keys = result[0].keys()
    f = open("C:/st_sim/repository/export_cheuvreux_orders.csv", "w")
    f.writelines(";".join(keys)+"\n")
    for k in result:
        f.writelines(";".join([str(x) for x in k.values()]) + "\n")
    f.close()

def daily_buy_sell(security_id, start, end):
    db = Connections.getClient("Mars")["Mars"]["AlgoOrders"]
    deals = Connections.getClient("Mars")["Mars"]["OrderDeals"]
    #result_deals = deals.aggregate([{"$match": {"cheuvreux_secid": security_id, "TransactTime":{"$gte": start, "$lte": end}, "LastMkt": "BLNK"}}, {"$project": {"LastMkt": 1, "OrderQty": 1, "_id": 0}} ])        
    
    #deal_list = deals.find({"cheuvreux_secid": security_id, "TransactTime":{"$gte": start, "$lte": end}, "LastMkt": "BLNK"})        
        
    results = []
    
    day = start
    while day <= end:
        result_orders = db.aggregate([{"$match": {"cheuvreux_secid": security_id, "TransactTime":{"$gte": day, "$lt": day + timedelta(days=1)}}}, {"$project": {"Side": 1, "OrderQty": 1, "_id": 0}}])    
        #print result_orders        
        buy_volume = sum([x["OrderQty"] if x["Side"] == "1" else 0 for x in result_orders["result"]])
        sell_volume = sum([x["OrderQty"] if x["Side"] == "2" else 0 for x in result_orders["result"]])
        result_deals = deals.aggregate([{"$match": {"cheuvreux_secid": security_id, "TransactTime":{"$gte": day, "$lte": day + timedelta(days=1)}, "LastMkt": "BLNK"}}, {"$project": {"LastMkt": 1, "LastShares": 1, "_id": 0}} ])    
        blink_volume = sum([x["LastShares"] for x in result_deals["result"]])        
        print day        
        print blink_volume     
        print buy_volume, sell_volume        
        
        results.append({"date": day, "buy_volume": buy_volume, "sell_volume": sell_volume, "blink_volume": blink_volume})
        day = day + timedelta(days = 1)
    
    df = {"date": [k["date"] for k in results], 
          "buy_volume" : [x["buy_volume"] for x in results], 
          "sell_volume" : [x["sell_volume"] for x in results], 
          "blink_volume" : [x["blink_volume"] for x in results]}    
    return pd.DataFrame(df, index = df["date"])

def get_reference_param(context_id,domain_id,estimator_id):
    
    params=pd.DataFrame()
    context_name=None
    
    #-- params
    query=""" SELECT parameter_name, value, x_value
    FROM QUANT..quant_param 
    WHERE context_id = %d and domain_id = %d and estimator_id = %d """ % (context_id,domain_id,estimator_id)
            
    vals = Connections.exec_sql('QUANT',query,schema = True)
    
    if not vals[0]:
        logging.info('nothing in quant_param')
    else:
        params = pd.DataFrame.from_records(vals[0],columns=vals[1])            
    
    #-- params
    query=""" SELECT context_name
            FROM QUANT..context 
            WHERE context_id = %d and estimator_id = %d """ % (context_id,estimator_id)
            
    vals = Connections.exec_sql('QUANT',query,schema = True)
    
    if not vals[0]:
        logging.info('nothing in context')
    else:
        context_name = vals[0][0][0]
    
    return params,context_name

def trade_frequency():
    query = """select security_id, EXCHANGE from KGR..INDEXCOMPONENT where INDEXID = 'IND_1' and DATE = '20130905' """    
    result = Connections.exec_sql('KGR', query, as_dataframe = True)    
    eurostoxx_components = result[np.isfinite(result['security_id'])]['security_id']   
    
    #print eurostoxx_components, len(eurostoxx_components)
    frame = pd.DataFrame() 
    frame_turnover = pd.DataFrame()
    for stock in eurostoxx_components:        
        query = """select date, nb_deal, open_prc, high_prc, low_prc, close_prc, turnover from trading_daily where security_id = %s and trading_destination_id is NULL and DATE >= '20080101' """ % stock            
        trading_data = Connections.exec_sql('MARKET_DATA', query, as_dataframe = True)    
        trading_data.index = trading_data['date']
        
        if( len(trading_data[np.isfinite(trading_data['nb_deal'])]) < 900):
            continue
        
        trading_data['vol'] = vol_gk(trading_data['open_prc'], trading_data['high_prc'], trading_data['low_prc'],trading_data['close_prc'], )
        trading_data['ratio'] = np.sqrt(trading_data['nb_deal']) / trading_data['vol']
                
        #trading_data['ratio'] = trading_data['nb_deal'] / trading_data['turnover']                
        #frame = frame.join(trading_data['nb_deal'], how = "outer", rsuffix = "%s_" % int(stock))
        frame = frame.join(trading_data['ratio'], how = "outer", rsuffix = "%s_" % int(stock))
        frame_turnover = frame_turnover.join(trading_data['turnover'], how = "outer", rsuffix = "%s_" % int(stock))
    frame['sum'] = frame.sum(axis = 1) 
    frame_turnover['sum'] = frame_turnover.sum(axis = 1)
    return frame, frame_turnover

def ImportIndicators(DataBase, l_indicator, perimeter, path):
    
    
    outfile = open('%s/security_indicator_dump.txt' %path,'w')
    Connections.change_connections('production')
    
    cursor, cxn = Connections.getCursor(DataBase)
    
    if perimeter == 'all':
        for indicator_id in l_indicator:
            query = "select ci.indicator_id, cii.name, ci.indicator_value, ci.security_id, ci.trading_destination_id ,erc.EXCHGID " \
            "from MARKET_DATA..ci_security_indicator ci " \
            "left join KGR..EXCHANGEREFCOMPL erc on (erc.EXCHANGE = ci.trading_destination_id) " \
            "left join MARKET_DATA..ci_indicator cii on (cii.indicator_id = ci.indicator_id) " \
            "where (erc.GLOBALZONEID = 1 or erc.GLOBALZONEID is NULL) and ci.indicator_id in (%s) " \
            "and (ci.trading_destination_id > 0 or ci.trading_destination_id is NULL) " \
            "order by ci.indicator_id, ci.security_id, ci.trading_destination_id" %(indicator_id)
    
            cursor.execute(query)
            result = cursor.fetchall()
            
            for line in result:
                if line[5] is None:
                    line[5] = 'AGGR'
                str_line  = ''
                for item in line:
                    str_line = "%s%s;" %(str_line, item)
                str_line = '%s\n' %str_line[:-1]
                outfile.write(str_line)
    
    outfile.close()

def upload_file(filename = 'C:\st_sim\projects\FixedIncomeReferential\Datas.xlsx'):
    xls = pd.ExcelFile(filename)
    data = xls.parse('Paris', header = 0 )
    client = Connections.getClient('HPP')
    # get mapping
    mapping = client['FixedIncome']['FieldMapping']    
    mapping_dictionary = {}
    for m in mapping.find():
        mapping_dictionary[m['field']] = m
        
    print mapping_dictionary
    
    collection = client['FixedIncome']['Referential']
    collection.remove()
    
    for i in range(len(data.index)):        
        row = {}
        for k in data.ix[i].keys():
            if k in mapping_dictionary.keys():
                row[k] = mapping_dictionary[k][convertStr(data.ix[i][k], '%d/%m/%Y')]
            else:
                row[k] = convertStr(data.ix[i][k], '%d/%m/%Y')
        
        #row = dict((k, convertStr(data.ix[i][k], '%d/%m/%Y')) for k in data.ix[i].keys() if k not in mapping_dictionary.keys() else (k, convertStr(data.ix[i][k], '%d/%m/%Y')) )    
        collection.insert(row)
    
    print "--------------------------------------------"
    for doc in collection.find():
        print doc
        print " "

def check_db_update(date):
    
    out = False
    
    try:
        
        date_s = dt.datetime.strftime(date.date(),'%Y%m%d')
        date_e = dt.datetime.strftime((date + dt.timedelta(days=1)).date(),'%Y%m%d')
        
        query=""" SELECT date , jobname ,status
                  FROM QUANT..upd_quant_data_report
                  WHERE date >= '%s' and date < '%s' """ % (date_s,date_e)
                  
        vals = Connections.exec_sql('QUANT',query,schema = True)
        if not vals[0]:
            logging.warning('No info of curve update for date : ' + date_s)
        else:
            data = pd.DataFrame.from_records(vals[0],columns=vals[1])
            #-- upd_quant_data
            if any(data['jobname'] == 'upd_quant_data'):
                out = data[data['jobname'] == 'upd_quant_data']['status'].values[0] == 'O'
                
            #--- quant_reference_update_context
            if out and any(data['jobname'] == 'quant_reference_update_context'):
                out = data[data['jobname'] == 'quant_reference_update_context']['status'].values[0] == 'O'
                
            if not out:
                logging.warning('Curve database has not been update properly for date : ' + date_s) 
               
    except:
        logging.error('error in check_db_update func')
    
    return out

def get_reference_run(estimator_id=None,level=None):
    
    out=pd.DataFrame()
    
    if estimator_id == 2:
        
        #-- query
        if level is None:
            raise ValueError('level is mandatory')
            
        elif level == 'specific':
            query=""" SELECT context_id, domain_id, estimator_id, security_id, EXCHANGE, rank, active, default_context, run_id, varargin 
                 FROM QUANT..quant_reference 
                 WHERE security_id is not null and estimator_id = %d and active = 1
                 ORDER BY security_id, EXCHANGE, rank """  % (estimator_id)
                
        elif level == 'generic':
            query=""" SELECT context_id, domain_id, estimator_id, security_id, EXCHANGE, rank, active, default_context, run_id, varargin 
                 FROM QUANT..quant_reference 
                 WHERE security_id is null and estimator_id = %d and active = 1
                 ORDER BY varargin, EXCHANGE, rank """ % (estimator_id)
                     
        #-- query
        vals = Connections.exec_sql('QUANT',query,schema = True)
        if not vals[0]:
            logging.info('nothing in quant_reference')
            return out
            
        out = pd.DataFrame.from_records(vals[0],columns=vals[1])
        
    else:
        
        raise ValueError('bad input estimator_id')
    
    return out

def check_db_update(date):
    
    out = False
    
    try:
        
        date_s = dt.datetime.strftime(date.date(),'%Y%m%d')
        date_e = dt.datetime.strftime((date + dt.timedelta(days=1)).date(),'%Y%m%d')
        date_f = dt.datetime.strftime((date - dt.timedelta(days=1)).date(),'%Y%m%d')
        
        query=""" SELECT date , jobname ,status
                  FROM MARKET_DATA..ciupdate_report
                  WHERE date >= '%s' and date < '%s' """ % (date_f,date_e)
                  
        vals = Connections.exec_sql('MARKET_DATA',query,schema = True)
        if not vals[0]:
            logging.warning('No info of indicator update for date : ' + date_s)
        else:
            data = pd.DataFrame.from_records(vals[0],columns=vals[1])
            #-- ciupdatesecurityindicator_all : day before
            if any((data['jobname'] == 'ciupdatesecurityindicator_all') & (data['date'] >= dt.datetime.strptime(date_f,'%Y%m%d'))):
                out = any(data[(data['jobname'] == 'ciupdatesecurityindicator_all') & (data['date'] >= dt.datetime.strptime(date_f,'%Y%m%d'))]['status'].values == 'O')
                
            #-- ciupdatesecurityindicator_all : day before
            if out and any((data['jobname'] == 'ciupdatesecurityindicator_ost') & (data['date'] >= dt.datetime.strptime(date_s,'%Y%m%d'))):
                out = any(data[(data['jobname'] == 'ciupdatesecurityindicator_ost') & (data['date'] >= dt.datetime.strptime(date_s,'%Y%m%d'))]['status'].values == 'O')                
                
            if not out:
                logging.warning('Indicator database has not been update properly for date : ' + date_s)
               
    except:
        logging.error('error in check_db_update func')
    
    return out

def extract_unique(field):
    client = Connections.getClient('HPP')
    collection = client['FixedIncome']['Referential']
    
    result = collection.aggregate([{'$project': {field: 1, '_id': 0}}])
    a = result['result']
    l = np.unique(np.array([str(x[field]) for x in result['result']]))
    print l

def daily_vs_auction():
    query = """select security_id, date, turnover, close_turnover from MARKET_DATA..trading_daily where
            trading_destination_id is NULL and
            security_id=110 and
            date > '20120101'"""
    df = Connections.exec_sql("MARKET_DATA", query, as_dataframe = True)   
    
    plt.plot(df["close_turnover"], df["turnover"]-df["close_turnover"], ".")

def stoxx_timeline():
    snp = read_csv('C:/st_sim/repository/table_snp.csv', sep = ',')        
    snp.index = [datetime.strptime(x, '%Y-%m-%d') for x in snp['Date']]
    print snp
    
    query = """select DATE, VALUE from Market_data..HISTOINDEXTIMESERIES where INDEXID = 'IND_37' and ATTRIBUTEID = 43 and DATE > '20100101' order by DATE"""
    eurostoxx = Connections.exec_sql('MARKET_DATA', query, as_dataframe = True)
    eurostoxx.index = eurostoxx['DATE']
    return eurostoxx, snp

def algos_on_a_day(bloom_code, date):

    db = Connections.getClient("Mars")["Mars"]["AlgoOrders"]
    mapping = Connections.getClient("Mars")["Mars"]["map_tagFIX"]    
    strategy_name = lambda x : mapping.aggregate([{"$match":{"tag_name": "StrategyName", "tag_value": x}}])["result"][0]["strategy_name"]
    
    
    security_id = int(convert_symbol(source = "bloomberg", dest = "security_id", value = bloom_code))
    print security_id
    date = datetime.strptime(date, '%d/%m/%Y')
    date_end = date + timedelta(days = 1)
    print date
    
    result = db.aggregate([{'$match': {"cheuvreux_secid": security_id, 'SendingTime': {'$gte': date, "$lte": date_end}, 'occ_nb_replace': 0}}])
    print len(result["result"])
    for r in result["result"]:
        #print r["StrategyName"]
        print "Algo:", r["strategy_name_mapped"], "Id", r["p_cl_ord_id"], "Size:", r["OrderQty"], "StartTime:" , r["TransactTime"], "Trader:", r["TraderName"] if r.has_key("TraderName") else "-"
    
    return result["result"]

def export_to_csv(server, query, filename, delimiter = ';'):
    result = Connections.exec_sql(server, query, as_dict = True)
    f = open(filename, "w")
    row = result[0]
    f.write( delimiter.join(row.keys()) + '\n')
    for row in result:     
        f.writelines( delimiter.join([str(x) if x else '' for x in row.values()])  + '\n')
    f.close()