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()
