def insert_date_sum(trade_date,rows):
dbw.delete("date_sums",where="trade_date=$trade_date",vars=locals())
for r in rows:
dbw.insert('date_sums',trade_date=trade_date,stock_plate=r.stock_plate,
stock_count=r.stock_count,avg_open=r.open,avg_close=r.close,
avg_high=r.high,avg_low=r.low,avg_volume=r.volume,
avg_amount=r.amount,create_date=datetime.datetime.now())
def get_access_token(user_id,device_no,device_type,os_type,channel,version):
result = list(dbr.select('user_devices',
what="user_id,device_no,access_token,device_type,os_type",
where="user_id=$user_id and device_no=$device_no",vars=locals()))
access_token = generate_access_token()
if not result:
dbw.insert('user_devices',user_id=user_id,device_no=device_no,
device_type=device_type,os_type=os_type,
access_token=access_token,
channel=channel,
version=version,
creation_date=web.SQLLiteral('now()'),
last_update=web.SQLLiteral('now()'))
result = list(dbr.select('user_devices',
what="user_id,device_no,access_token,device_type,os_type",
where="user_id=$user_id and device_no=$device_no",vars=locals()))
else:
dbw.update('user_devices',
device_no=device_no,
device_type=device_type,os_type=os_type,
access_token=access_token,
channel=channel,
version=version,
last_update=web.SQLLiteral('now()'),
where="user_id=$user_id and device_no=$device_no",vars=locals())
result[0].access_token = access_token
return load_user(result[0].user_id)
def buy_and_sell(strategy_id,strategy_batch_no,stock_no,buy_date,hold_days=1,buy_price='open_price',sell_price='open_price',trade_hands=1):
stocks = load_dates_stock(stock_no,buy_date,hold_days)
if len(stocks)< hold_days + 1:
raise Exception('no-sell-date-stock-info')
buy_stock = stocks[0]
sell_stock = stocks[-1]
earnings = ( sell_stock[sell_price] - buy_stock[buy_price] )* trade_hands * 100
earnings_rate = ( sell_stock[sell_price] - buy_stock[buy_price] )/ buy_stock[buy_price] * 100
dbw.insert('trading_records',
strategy_id = strategy_id,
strategy_batch_no = strategy_batch_no,
buy_or_sell = 0,
stock_no = buy_stock.stock_no,
date = buy_stock.date,
open_or_close = buy_price,
price = buy_stock[buy_price],
hands = trade_hands,
input_output = 0 - buy_stock[buy_price] * trade_hands * 100 ,
earnings=earnings,earn_rate=earnings_rate,
create_date=web.SQLLiteral('now()'))
dbw.insert('trading_records',
strategy_id = strategy_id,
strategy_batch_no = strategy_batch_no,
stock_no = sell_stock.stock_no,
buy_or_sell = 1,
date = sell_stock.date,
open_or_close = sell_price,
price = sell_stock[sell_price],
hands = trade_hands,
input_output = sell_stock[sell_price] * trade_hands * 100 ,
earnings = earnings, earn_rate=earnings_rate,
create_date=web.SQLLiteral('now()'))
def insert_row(date, stock_no):
return dbw.insert(
'stock_daily',
trade_date=date,
stock_no=stock_no,
create_date=web.SQLLiteral('NOW()'),
)
def term_insert(term):
tname = 'terms'
result = list(dbr.select(tname, where="term=$term", vars=locals()))
if not result:
return dbw.insert(tname,
term=term,
term_len=len(term),
last_update=web.SQLLiteral('now()'))
return result[0].term_id
def insert(date,stock_no,open,close,high,low,volume):
return dbw.insert('stock_daily',trade_date=date,stock_no=stock_no,
open = open,
close = close,
high = high,
low = low,
volume = volume,
create_date = web.SQLLiteral('NOW()'),
last_update = web.SQLLiteral('NOW()'),)
def insert(user_id, val_sort, val_account, val_code):
return dbw.insert(tname,
user_id=user_id,
validate_sort=val_sort,
validate_account=val_account,
validate_code=val_code,
code_status=0,
creation_date=web.SQLLiteral('now()'),
last_update=web.SQLLiteral('now()'))
def insert2(user_id,body,device_type,device_no,local_id,created_date,last_update):
rows = list(dbw.select(table_name,what="pk_id,body", where="user_id=$user_id",vars=locals(),order="pk_id desc", limit=80))
for r in rows:
if cmp(r.body,body)==0:
return r.pk_id
return dbw.insert(table_name,user_id=user_id,subject="",body=body,
device_no = device_no, local_id=local_id, device_type=device_type,
app_created_date=created_date,
last_update=last_update,
created_date = web.SQLLiteral('now()'),
plan_start_date=web.SQLLiteral('now()'))
def insert(user_id,subject):
#避免重复提交
last_one = load_last_one(user_id)
if last_one and last_one.body.strip()==subject.strip():
return last_one.pk_id
return dbw.insert(table_name,user_id=user_id,subject="",body=subject,
created_date=web.SQLLiteral('now()'),
app_created_date = web.SQLLiteral('now()'),
last_update=web.SQLLiteral('now()'),
plan_start_date=web.SQLLiteral('now()'))
def get_access_token(user_id, device_no, device_type, os_type, channel,
version):
result = list(
dbr.select('user_devices',
what="user_id,device_no,access_token,device_type,os_type",
where="user_id=$user_id and device_no=$device_no",
vars=locals()))
access_token = generate_access_token()
if not result:
dbw.insert('user_devices',
user_id=user_id,
device_no=device_no,
device_type=device_type,
os_type=os_type,
access_token=access_token,
channel=channel,
version=version,
creation_date=web.SQLLiteral('now()'),
last_update=web.SQLLiteral('now()'))
result = list(
dbr.select(
'user_devices',
what="user_id,device_no,access_token,device_type,os_type",
where="user_id=$user_id and device_no=$device_no",
vars=locals()))
else:
dbw.update('user_devices',
device_no=device_no,
device_type=device_type,
os_type=os_type,
access_token=access_token,
channel=channel,
version=version,
last_update=web.SQLLiteral('now()'),
where="user_id=$user_id and device_no=$device_no",
vars=locals())
result[0].access_token = access_token
return load_user(result[0].user_id)
def update_date_sum(date, data, plate=0):
rows = list(
dbr.select('date_sum_infos',
where="date=$date and plate=$plate",
vars=locals()))
if len(rows) == 0:
dbw.insert('date_sum_infos',
date=date,
plate=plate,
create_date=web.SQLLiteral("NOW()"),
last_update=web.SQLLiteral("NOW()"))
dbw.update(
'date_sum_infos',
total_count=data.total_count,
price_up_count=data.price_up_count,
volumn_up_count=data.volumn_up_count,
price_up_percent=data.price_up_percent,
volumn_up_percent=data.volumn_up_percent,
# price_raise_sum = data.price_raise_sum,
where="date=$date and plate=$plate",
vars=locals())
def insert(date, stock_no, open, close, high, low, volume):
return dbw.insert(
'stock_daily',
trade_date=date,
stock_no=stock_no,
open=open,
close=close,
high=high,
low=low,
volume=volume,
create_date=web.SQLLiteral('NOW()'),
last_update=web.SQLLiteral('NOW()'),
)
def register(name,password):
#,device_type,device_no,os_type
# mobile = 0
# email =''
# if '@' in name:
# email = name
# else:
# mobile = name
#email=email,
user_id = dbw.insert(tname,mobile=name,password=password,
creation_date=web.SQLLiteral('now()'),
last_update=web.SQLLiteral('now()'),
last_login=web.SQLLiteral('now()'))
return user_id
def register(name, password):
#,device_type,device_no,os_type
# mobile = 0
# email =''
# if '@' in name:
# email = name
# else:
# mobile = name
#email=email,
user_id = dbw.insert(tname,
mobile=name,
password=password,
creation_date=web.SQLLiteral('now()'),
last_update=web.SQLLiteral('now()'),
last_login=web.SQLLiteral('now()'))
return user_id
def term_insert(term):
tname = 'terms'
result = list(dbr.select(tname,where="term=$term",vars=locals()))
if not result:
return dbw.insert(tname,term=term,term_len=len(term),last_update=web.SQLLiteral('now()'))
return result[0].term_id
def run_price_up_percent(strCondition,title,description):
strategy_id = dbw.insert('trading_strategies',title=title,description=description,create_date=web.SQLLiteral('now()'))
dates = [r.date for r in dbr.select('date_sum_infos',what="date",where=strCondition)]
stock_nos = [r.stock_no for r in dbr.select('stock_base_infos',what="stock_no",where="market_code_yahoo in ('ss','sz')")]
run_strategy_tradding(strategy_id,dates,stock_nos)
def run_strategy_1(year):
strategy_id = dbw.insert('trading_strategies',title="random_%s" % (year),description="YEAR(date)=%s,market_code_yahoo in ('ss','sz')"% (year))
dates = [r.date for r in dbr.select('date_sum_infos',what="date",where="YEAR(date)=%s" % (year) )]
stock_nos = [r.stock_no for r in dbr.select('stock_base_infos',what="stock_no",where="market_code_yahoo in ('ss','sz')")]
run_strategy_tradding(strategy_id,dates,stock_nos)
def insert_row(date,stock_no):
return dbw.insert('stock_daily',trade_date=date,stock_no=stock_no,create_date = web.SQLLiteral('NOW()'),)
def insert(user_id,val_sort,val_account,val_code):
return dbw.insert(tname,
user_id=user_id,validate_sort=val_sort,validate_account=val_account,validate_code=val_code,
code_status=0,creation_date=web.SQLLiteral('now()'),last_update=web.SQLLiteral('now()'))