Python Connections.exec_sql方法代码示例

# 需要导入模块: from lib.dbtools.connections import Connections [as 别名]
# 或者: from lib.dbtools.connections.Connections import exec_sql [as 别名]
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

# 需要导入模块: from lib.dbtools.connections import Connections [as 别名]
# 或者: from lib.dbtools.connections.Connections import exec_sql [as 别名]
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

# 需要导入模块: from lib.dbtools.connections import Connections [as 别名]
# 或者: from lib.dbtools.connections.Connections import exec_sql [as 别名]
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

# 需要导入模块: from lib.dbtools.connections import Connections [as 别名]
# 或者: from lib.dbtools.connections.Connections import exec_sql [as 别名]
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

# 需要导入模块: from lib.dbtools.connections import Connections [as 别名]
# 或者: from lib.dbtools.connections.Connections import exec_sql [as 别名]
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

# 需要导入模块: from lib.dbtools.connections import Connections [as 别名]
# 或者: from lib.dbtools.connections.Connections import exec_sql [as 别名]
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()

# 需要导入模块: from lib.dbtools.connections import Connections [as 别名]
# 或者: from lib.dbtools.connections.Connections import exec_sql [as 别名]
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"], ".")

# 需要导入模块: from lib.dbtools.connections import Connections [as 别名]
# 或者: from lib.dbtools.connections.Connections import exec_sql [as 别名]
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

# 需要导入模块: from lib.dbtools.connections import Connections [as 别名]
# 或者: from lib.dbtools.connections.Connections import exec_sql [as 别名]
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()
    

    

# 需要导入模块: from lib.dbtools.connections import Connections [as 别名]
# 或者: from lib.dbtools.connections.Connections import exec_sql [as 别名]
def turnover_over_time():
    cac40_query = "select distinct security_id from indice_component where INDEXID = 'IND_1'"
    cac40 =[str(x[0]) for x in Connections.exec_sql('KGR', cac40_query)]
        
    query = """select security_id, date, turnover, close_turnover from Market_data..trading_daily where
            trading_destination_id is NULL and
            security_id in (%s) and
            date > '20120101'""" % (",".join(cac40))    
    df = Connections.exec_sql("Market_data", query, as_dataframe = True)
    
    dates = []
    turnover = []
    auction = []
    for date, group in df.groupby('date'):
        dates.append(datetime.strptime(date,  '%Y-%m-%d' ))
        turnover.append(sum(group["turnover"]))
        auction.append(sum(group["close_turnover"]))
        
    aggregate = DataFrame({"date": dates, "turnover": turnover, "auction": auction})
    aggregate.index = aggregate["date"]
    
    plt.bar(aggregate["date"], 100.0 * aggregate["auction"] / aggregate["turnover"])
    plt.show()

# 需要导入模块: from lib.dbtools.connections import Connections [as 别名]
# 或者: from lib.dbtools.connections.Connections import exec_sql [as 别名]
def histocurrencypair(date = None, last_date_from = None, start_date = None, end_date = None, 
                      currency = None, currency_ref = None,
                      all_business_day = None):
    
    out = pd.DataFrame()
    
    ##############################################################
    # input handling
    ##############################################################
    #---- date info
    if date is not None:
        start_date = end_date = date
        req_n = 1
    elif last_date_from is not None:
        req_n = 2
    elif start_date is not None or end_date is not None:
        if start_date is None or end_date is None:
            raise NameError('read_dataset:histocurrencypair - Bad input for dates')        
        req_n = 1
        
    #---- currencyref info
    if currency_ref is not None: 
        raise NameError('read_dataset:histocurrencypair - currency_ref not available NOW') 
    else:
        curr_ref=['EUR']
        
    str_curr_ref="("+"".join(["'"+x+"'," for x in curr_ref])
    str_curr_ref=str_curr_ref[:-1]+")"
    
    #---- currency info
    if currency is not None:
        curr = currency 
        if isinstance(currency, basestring):
            curr=[currency]
        elif (not isinstance(currency, list)) and (not isinstance(curr, np.ndarray)):
            raise NameError('read_dataset:histocurrencypair - Bad input for currency ref')
        str_curr = "("+"".join(["'"+x+"'," for x in curr])
        str_curr=str_curr[:-1]+")" 
    else:
        curr=[]
        str_curr=[]
    
    ##############################################################
    # request and format
    ##############################################################
    ####  Build request
    if req_n == 1:
        req=("""SELECT 
                DATE,CCY,CCYREF,VALUE
                FROM
                    KGR..HISTOCURRENCYTIMESERIES
                WHERE
                    DATE>= '%s'
                    and DATE<= '%s'
                    and SOURCEID=1
                    and ATTRIBUTEID=43
                    and CCYREF in %s""" ) % (start_date, end_date, str_curr_ref)
    
    elif req_n == 2:
        req=( """SELECT TOP(%d)
              DATE,CCY,CCYREF,VALUE
              FROM
              KGR..HISTOCURRENCYTIMESERIES
              WHERE
                DATE<= '%s'
                and SOURCEID = 1
                and ATTRIBUTEID = 43
                and CCYREF in %s """) % (len(curr), last_date_from, str_curr_ref)    
    
    if not curr == []:
        req=req+((" and CCY in %s ") % (str_curr))
    req += 'ORDER BY DATE DESC  '
          
    #### EXECUTE REQUEST 
    vals=Connections.exec_sql('KGR',req)
    
    ####  OUTPUT 
    if not vals:
        return out  
        
    out=pd.DataFrame.from_records(vals,columns=['date','ccy','ccyref','rate'],index=['date'])
    out=out.sort_index()
    
    
    ##############################################################
    # request and format
    ##############################################################    
    if all_business_day:
        uni_currency_pair = matlabutils.uniqueext(out[['ccy','ccyref']].values,rows = True )
        #-- dates
        min_date = out.index[0].to_datetime()
        max_date = out.index[-1].to_datetime()
        all_date = []
        date = min_date
        while date <= max_date:
            if date.weekday() not in [5, 6]:
                all_date.append(date)
            date += timedelta(days=1)
            
        #-- for each cuurency pair (get the last value)
#.........这里部分代码省略.........

# 需要导入模块: from lib.dbtools.connections import Connections [as 别名]
# 或者: from lib.dbtools.connections.Connections import exec_sql [as 别名]
dico['VWAP (no dark)'] = []
dico['VWAP (with dark)'] = []
dico['Slippage VWAP(bp)'] = []
dico['Slippage VWAP(Eur)'] = []

dico['Volume (no dark)'] = []
dico['Volume (with dark)'] = []

dico['%Volume (no dark)'] = []
dico['%Volume (with dark)'] =[]

for i in range(len(data)):
    row = data.ix[i]    
    print row
    security_id_query = "select SYMBOL6 from SECURITY where SYMBOL5 ='%s'" % row['SECID']
    result = Connections.exec_sql('KGR', security_id_query)
    if not result :
        continue
    security_id = int(result[0][0])
    date  = datetime.strptime(row['CREATETIME'], '%d/%m/%Y %H:%M') + timedelta(hours = 2)
    start = datetime.strptime(row['CREATETIME'], '%d/%m/%Y %H:%M') + timedelta(hours = 2)
    end   = datetime.strptime(row['LASTFILLTIME'], '%d/%m/%Y %H:%M') + timedelta(hours = 2)
    print date
    print security_id
    market = ft(security_id = security_id, date = datetime.strftime(date, "%d/%m/%Y"))    
    print market    
    market = market[np.logical_and(market.index >= start, market.index <= end)]
    market_dark = market    
    market = market[market["dark"] == 0]    
    market = market[market["cross"] == 0]        
    print market

# 需要导入模块: from lib.dbtools.connections import Connections [as 别名]
# 或者: from lib.dbtools.connections.Connections import exec_sql [as 别名]
def export_symdata(data_security_referential = None,
                   path_export = None, filename_export = None,
                   indicators2export = [1, 2, 3, 21, 22, 24, 25, 27, 29, 31, 33, 35, 37, 38, 39, 40 , 73, 75 , 86]):

    #--------------------
    #- TEST INPUT
    #--------------------
    if data_security_referential is None or path_export is None or filename_export is None or indicators2export is None:
        raise ValueError('bad inputs')
        
    if not os.path.exists(path_export):
        raise ValueError('path not defined')
        
    if data_security_referential.shape[0]==0:
        raise ValueError('no security ref')
        
    logging.info('START export_symdata')
    
    
    #--------------------
    #- NEEDED DATA
    #--------------------   
    all_sec_ids = np.unique(data_security_referential['cheuvreux_secid'].values)
    str_indicators2export = "".join([str(x)+',' for x in indicators2export])[:-1]
    map_flid_indid = mappingflid()
    if np.any([x not in map_flid_indid['indicator_id'].values for x in indicators2export]):
        raise ValueError('one of the asked indictor do not have yet a flid')
       
    #--------------------
    #- ADD DATA (by chunks of securities in order to avoid memory leaks)
    #--------------------
    with_data_symbol = []
    out = open( os.path.join(path_export, filename_export), 'w' )
    
    NB_MIN_SYMBOL = 5000
    NB_MAX_SEC = 2000
    last_idx = -1
    NONE_ = -999
    
    while last_idx < len(all_sec_ids)-1:
        
        # logging.info('pct adv'+str(last_idx/(len(all_sec_ids))))
        #--------------------
        #- get indicators from database
        #--------------------
        s_idx = np.min([ last_idx+1, len(all_sec_ids)-1 ])
        last_idx = np.min([ last_idx+NB_MAX_SEC, len(all_sec_ids)-1 ])
        str_secids = "".join([str(x)+',' for x in all_sec_ids[range( s_idx, last_idx+1 )]])[:-1]
        
        query_indicatops=" SELECT si.security_id,isnull(si.trading_destination_id, %d) as trading_destination_id,si.indicator_id,si.indicator_value " \
            " FROM MARKET_DATA..ci_security_indicator si " \
            " LEFT JOIN KGR..EXCHANGEREFCOMPL erefcompl on ( " \
            " si.trading_destination_id=erefcompl.EXCHANGE " \
            " ) " \
            " WHERE si.security_id in (%s)  " \
            " AND si.indicator_id in (%s) " \
            " AND ( erefcompl.EXCHANGETYPE is NULL or erefcompl.EXCHANGETYPE = 'M' ) " \
            " ORDER BY si.security_id, trading_destination_id, si.indicator_id " % (NONE_,str_secids,str_indicators2export)
        
        vals=Connections.exec_sql('MARKET_DATA',query_indicatops,schema = True)
        if not vals[0]:
            continue
        
        vals = pd.DataFrame.from_records(vals[0],columns=vals[1])
        
        
        
        
        uni_ = mutils.uniqueext(vals[['security_id','trading_destination_id']].values, rows = True)
        
        #--------------------
        #- add to symdata
        #--------------------                
        for i in range(0, len(uni_)):
            #--- for each couple (sec_id, td_id)
            tmp_data = vals[(vals['security_id'] == uni_[i][0]) & (vals['trading_destination_id'] == uni_[i][1])]
            
            #--- for each flex symbol
            this_sec_info = data_security_referential[ data_security_referential['cheuvreux_secid'] ==  uni_[i][0]]
            
            for isec in range(0, this_sec_info.shape[0]):
                #-- get flex symbol
                symbol = this_sec_info['ticker'].values[isec]
                
                if uni_[i][1] == NONE_:
                    symbol = this_sec_info['tickerAG'].values[isec]
                    
                if symbol is None or not isinstance(symbol,basestring):
                    continue
                
                #-- write str
                add_str = ''
                for idata in range(0,tmp_data.shape[0]):
                    #--- for each indicators 
                    flidid = map_flid_indid[map_flid_indid['indicator_id'] == int(tmp_data.iloc[idata]['indicator_id'])]['flid'].values[0]
                    add_str += str(symbol) + ':' + str(flidid) + '=' + str(tmp_data.iloc[idata]['indicator_value']) + '\n'
                    
                add_str += '\n'
                out.write(add_str)
                with_data_symbol.append(symbol)
#.........这里部分代码省略.........

# 需要导入模块: from lib.dbtools.connections import Connections [as 别名]
# 或者: from lib.dbtools.connections.Connections import exec_sql [as 别名]
myfile.close()


################################
## Place Table
################################
Connections.change_connections('production')

pref_ = "LUIDBC01_" if Connections.connections == "dev" else  ""
req=("SELECT  flexexch.SUFFIX,exch.EXCHGNAME "
    " FROM %sKGR..FlextradeExchangeMapping flexexch "
    " LEFT JOIN %sKGR..EXCHANGEMAPPING exchmap on ( "
    " flexexch.EXCHANGE=exchmap.EXCHANGE ) "
    " LEFT JOIN %sKGR..EXCHANGE exch on ( "
    " exchmap.EXCHGID=exch.EXCHGID ) ")  % (pref_,pref_,pref_)
vals=Connections.exec_sql('KGR',req)
data=pd.DataFrame.from_records(vals,columns=['suffix','name'])
data=pd.DataFrame(matlabutils.uniqueext(data.values,rows=True),columns=['suffix','name'])

myfile = open(path_export+'place_table_'+datetime.strftime(datetime.today(),'%Y%m%d')+'.txt','w')
myfile.write('  <user-defined name="Place Name (active)" numeric="no">\n')
myfile.write('      <mapping property="TAG_100 (active)">\n')
for i in range(0,data.shape[0]):
    if not data.ix[i]['suffix']=='AG':
        myfile.write('          <rule predicate="EQUALS" source="'+data.ix[i]['suffix']+'" constant="'+data.ix[i]['name']+'"  />\n')

myfile.write('      </mapping>\n')
myfile.write('  </user-defined>\n')
myfile.close()

# 需要导入模块: from lib.dbtools.connections import Connections [as 别名]
# 或者: from lib.dbtools.connections.Connections import exec_sql [as 别名]
    collection.insert(doc)

"""
Connections.change_connections("production")
# first we add the lit primary markets
query = """select exc.MARKET, exc.COUNTRY, exc.EXCHGID, exc.CDBEXCHGID, ref.MIC, ref.GLOBALZONEID, ref.TIMEZONE, ref.PLATFORM, ref.EXCHANGETYPE, tz.OFFSET from KGR..EXCHANGE exc, KGR..EXCHANGEREFCOMPL ref, KGR..TIMEZONE tz
           where 
           ref.EXCHANGE = exc.MARKET and
           ref.TIMEZONE = tz.TIMEZONE and
           ref.GLOBALZONEID = 1 and
           ref.EXCHANGETYPE = 'M' and
           GETDATE() < tz.ENDDATE and
           GETDATE() >= tz.BEGINDATE
           
           """
entries = Connections.exec_sql("KGR", query, as_dict = True)

trading_venue_id = 1

for e in entries:  
    documents.append({"trading_venue_id": trading_venue_id, 
                      "exchange_id": e['MARKET'], 
                      "exchange": e['EXCHGID'] , 
                      'name' : e["PLATFORM"], 
                      'type': "L", 
                      'is_primary': 1, 
                      'code': e["MIC"],                       
                      'destination':"TEST",                       
                      "feed": flex_code[int(e["MARKET"])] if flex_code.has_key(int(e["MARKET"])) else "",
                      "flex_code": flex_code[int(e["MARKET"])] if flex_code.has_key(int(e["MARKET"])) else "",
                      'ranking': 5,