def getengine(): host = '176.1.11.55' #host = 'localhost' user = '******' password = '******' #password = '******' return dbaccessor.getdb(host, user, password)
def testInsert(): engine = dbaccessor.getdb('localhost', 'zhenggq', 'Yuzhong0931', 'test', 1433) conn = engine.connect() metadata = MetaData() account = Table('account', metadata, Column('Id', Integer(), nullable=False), Column('Name', String(50), nullable=False), Column('Email', String(50)), Column('OpType', Integer()), Column('PassCode', String(50)) #Column('Addition', Numeric(12, 2)) ) ins = account.insert().values(Id=15, Name='Sqlalchemy', Email='*****@*****.**', OpType=1, PassCode='Sqlalchemy') print(str(ins)) result = conn.execute(ins) print(result.inserted_primary_key)
def testSession(): engine = dbaccessor.getdb('localhost', 'zhenggq', 'Yuzhong0931', 'test', 1433) metadata = MetaData(bind=engine) session = Session(bind=engine) mytable = Table('account', metadata, autoload=True) #select s = mytable.select() result = session.execute(s) out = result.fetchall() print(result.keys()) print(out) #insert """i = insert(mytable) i = i.values({"Id": 14, 'Name': 'Youare', 'Email': '*****@*****.**', 'OpType': 1, 'PassCode': 'Youarewelcome'}) try: session.execute(i) session.commit() except: session.rollback() finally: session.close() """ session.close() print('Finish')
def handleOneDay(stdpath, residpath, dtype, td, removeCols, keyCols, includCols, excludeCols, createDate, settleDate): df = dataapi.getFactorData(dtype, td) actualKeyCols = list(keyCols) if settleDate in df.columns: actualKeyCols.insert(0, settleDate) if createDate in df.columns: actualKeyCols.insert(0, createDate) t1 = datetime.datetime.now() stddf = std.handleOneDay(df, removeCols, actualKeyCols, includeCols, excludeCols) t2 = datetime.datetime.now() print 'Cost std: {0} on {1}'.format(t2 - t1, td.strftime('%Y%m%d')) #TODO: save fileutil.savePickle(stdpath, td, stddf) residdf = residual.calcResidual(td, stddf, keyCols, includeCols, excludeCols) t3 = datetime.datetime.now() print 'Cost resid: {0} on {1}'.format(t3 - t2, td.strftime('%Y%m%d')) #TODO: save the residual data fileutil.savePickle(residpath, td, residdf) engine = dbaccessor.getdb('localhost', 'zhenggq', 'Yuzhong0931', 'advancedb', 1433) residdb.insertData(dtype, engine, residdf) #residdb.insertDataClosePrice(dtype, engine, residdf) t4 = datetime.datetime.now() print 'Cost insertdb: {0} on {1}'.format(t4 - t3, td.strftime('%Y%m%d'))
def insertAllStocks(filepath, stocks, dtype): engine = dbaccessor.getdb('localhost', 'zhenggq', 'Yuzhong0931', 'advancedb', 1433) for stock in stocks: print 'insert: {0}'.format(stock) start = datetime.datetime.now() df = fileutil.loadStockPickle(filepath, stock) if df is not None and len(df) > 0: conn = engine.connect() df['SecuAbbr'] = df['SecuAbbr'].apply(lambda x:x.encode('raw-unicode-escape').decode('gbk')) insertMonthly(conn, df) end = datetime.datetime.now() print 'Cost: {0} on {1}'.format(end-start, stock)
def testWeeklyData(td): filepath = 'D:/workspace/python/residual/resid/weekly/' df = fileutil.loadPickle(filepath, td) if df is not None: t1 = datetime.datetime.now() engine = dbaccessor.getdb('localhost', 'zhenggq', 'Yuzhong0931', 'advancedb', 1433) conn = engine.connect() t2 = datetime.datetime.now() print 'cost connect db: {0}'.format(t2 - t1) insertWeekly(conn, df) t3 = datetime.datetime.now() print 'cost insert db: {0}'.format(t3 - t2)
def insertAllData(filepath, tradingDays, dtype): #engine = dbaccessor.getdb('176.1.11.55', 'zhenggq', 'Yuzhong0931', 'advancedb', 1433) #engine = dbaccessor.getdb('localhost', 'zhenggq', 'yuzhong', 'advancedb', 1433) engine = dbaccessor.getdb('localhost', 'zhenggq', 'Yuzhong0931', 'advancedb', 1433) for td in tradingDays: print 'insert: {0}'.format(td.strftime('%Y%m%d')) start = datetime.datetime.now() df = fileutil.loadPickle(filepath, td) if df is not None: #中文被错误处理成unicode编码,而实际上是gbk编码 df['SecuAbbr'] = df['SecuAbbr'].apply(lambda x:x.encode('raw-unicode-escape').decode('gbk')) insertData(dtype, engine, df) end = datetime.datetime.now() print 'Cost: {0} on {1}'.format(end-start, td.strftime('%Y%m%d'))
def insertResid(df): """ 向数据库表advancedb.dbo.FactorDailyResidual中插入数据 df - pandas DataFrame对象 """ engine = dbaccessor.getdb('localhost', 'zhenggq', 'Yuzhong0931', 'advancedb', 1433) conn = engine.connect() metadata = MetaData() residtb = Table( 'FactorDailyResidual', metadata, Column('TradingDay', DateTime(), nullable=False), Column('SecuCode', String(10), nullable=False), Column('ClosePrice', Numeric(16, 4)), Column('OpenPrice', Numeric(16, 4)), Column('HighPrice', Numeric(16, 4)), Column('LowPrice', Numeric(16, 4)), Column('ExeClosePrice', Numeric(16, 4)), Column('ExeOpenPrice', Numeric(16, 4)), Column('ExeHighPrice', Numeric(16, 4)), Column('ExeLowPrice', Numeric(16, 4)), Column('NonRestrictedShares', Numeric(16, 0)), Column('AFloats', Numeric(16, 0)), Column('TotalShares', Numeric(16, 0)), Column('TurnoverVolume', Numeric(20, 0)), Column('NonRestrictedCap', Numeric(36, 4)), Column('AFloatsCap', Numeric(36, 4)), Column('TotalCap', Numeric(36, 4)), Column('SecuAbbr', String(50)), Column('IndustrySecuCode_I', String(10)), Column('PE', Numeric(38, 6)), Column('PB', Numeric(38, 6)), Column('PS', Numeric(38, 6)), Column('PCF', Numeric(38, 6)), Column('DividendYield', Numeric(16, 6)), Column('DividendRatio', Numeric(16, 6)), Column('TTMIncome', Numeric(36, 4)), Column('GP_Margin', Numeric(16, 4)), Column('NP_Margin', Numeric(16, 4)), Column('ROA', Numeric(16, 4)), Column('ROE', Numeric(16, 4)), Column('AssetsTurnover', Numeric(16, 4)), Column('EquityTurnover', Numeric(16, 4)), Column('Cash_to_Assets', Numeric(16, 4)), Column('Liability_to_Assets', Numeric(16, 4)), Column('EquityMultiplier', Numeric(16, 4)), Column('CurrentRatio', Numeric(16, 4)), Column('Income_Growth_YOY_Comparable', Numeric(16, 4)), Column('NPPC_Growth_YOY_Comparable', Numeric(16, 4)), Column('GP_Margin_Comparable', Numeric(16, 4)), Column('GP_Margin_Growth_YOY_Comparable', Numeric(16, 4)), Column('NP_Margin_Comparable', Numeric(16, 4)), Column('NP_Margin_Growth_YOY_Comparable', Numeric(16, 4)), Column('Income_Growth_Pre_Comparable', Numeric(16, 4)), Column('NPPC_Growth_Pre_Comparable', Numeric(16, 4)), Column('GP_Margin_Growth_Pre_Comparable', Numeric(16, 4)), Column('NP_Margin_Growth_Pre_Comparable', Numeric(16, 4)), Column('NPPC_Growth_Pre_Season', Numeric(16, 4)), Column('NPPC_Growth_YOY_Season', Numeric(16, 4)), Column('NPLNRP_Growth_Pre_Season', Numeric(32, 14)), Column('NPLNRP_Growth_YOY_Season', Numeric(32, 15)), Column('Income_Growth_Pre_Season', Numeric(16, 4)), Column('Income_Growth_YOY_Season', Numeric(16, 4)), Column('Income_Growth_Qtr_Comparable', Numeric(16, 4)), Column('NPPC_Growth_Qtr_Comparable', Numeric(16, 4)), Column('GP_Margin_Qtr', Numeric(16, 4)), Column('GP_Margin_Growth_Qtr_Comparable', Numeric(16, 4)), Column('IPS_Qtr', Numeric(32, 21)), Column('EPS_Qtr', Numeric(32, 21)), Column('ROE_Qtr', Numeric(16, 4)), Column('Income_Growth_Pre', Numeric(16, 4)), Column('NPPC_Growth_Pre', Numeric(16, 4)), Column('NPLNRP_Growth_Pre', Numeric(32, 13)), Column('GP_Margin_Growth_Pre', Numeric(16, 4)), Column('NP_Margin_Growth_Pre', Numeric(16, 4)), Column('Income_Growth_YOY', Numeric(16, 4)), Column('NPPC_Growth_YOY', Numeric(16, 4)), Column('NPLNRP_Growth_YOY', Numeric(32, 13)), Column('GP_Margin_Growth_YOY', Numeric(16, 4)), Column('NP_Margin_Growth_YOY', Numeric(16, 4)), Column('IPS', Numeric(32, 21)), Column('EPS', Numeric(32, 21)), Column('CFPS', Numeric(32, 21)), Column('Pre_IPS', Numeric(32, 21)), Column('Pre_EPS', Numeric(32, 21)), Column('Pre_CFPS', Numeric(32, 21)), Column('YOY_IPS', Numeric(32, 21)), Column('YOY_EPS', Numeric(32, 21)), Column('YOY_CFPS', Numeric(32, 21)), Column('rPE', Numeric(32, 10)), Column('rPB', Numeric(32, 10)), Column('rPS', Numeric(32, 10)), Column('rPCF', Numeric(32, 10)), Column('SettlePrice', Numeric(16, 10)), Column('IndusInnerCode', Integer()), Column('IndusCreatePrice', Numeric(16, 10)), Column('IndusSettlePrice', Numeric(16, 10)), Column('ExecutivesProp', Numeric(16, 10)), Column('InstitutionNum', Integer()), Column('InstitutionProp', Numeric(16, 10)), Column('RegionScore', Integer()), Column('ExeClosePrice_CreateDate_Wind', Numeric(16, 4)), Column('ExeClosePrice_SettleDate_Wind', Numeric(16, 4)), Column('Momentum20Day', Numeric(20, 4)), Column('Momentum40Day', Numeric(20, 4)), Column('Momentum60Day', Numeric(20, 4)), Column('Momentum120Day', Numeric(20, 4)), Column('Momentum180Day', Numeric(20, 4)), Column('Momentum240Day', Numeric(20, 4)), Column('PriceDiff', Numeric(20, 4)), Column('DayDiff', Integer()), Column('TurnoverRatio', Numeric(20, 4)), Column('AvgTurnoverPrice', Numeric(20, 4)), Column('AvgTurnoverPriceFactor', Numeric(20, 4)), Column('AvgTurnoverRatio5Day', Numeric(20, 5)), Column('AvgTurnoverRatio10Day', Numeric(20, 5)), Column('AvgTurnoverRatio20Day', Numeric(20, 5)), Column('AvgTurnoverRatio40Day', Numeric(20, 5)), Column('AvgTurnoverRatio60Day', Numeric(20, 5)), Column('AvgTurnoverRatio120Day', Numeric(20, 5)), ) ins = residtb.insert() dataList = df.T.to_dict().values() try: result = conn.execute(ins, dataList) finally: conn.close()