def update_stock_name():
stock_code_list = get_all_stock_code()
val_list = []
conn = mysqlcli.get_connection()
c = mysqlcli.get_cursor(conn)
for i, code in enumerate(stock_code_list):
try:
code_i = int(code)
if code_i >= 600000:
continue
#val_list.append(tuple([code, stock_info['name']]))
sql = 'update {0} set code = "%s" where code = "%d"'.format(
config.sql_tab_basic_info) % tuple([code, code_i])
c.execute(sql)
conn.commit()
except Exception as e:
print(e)
#for val in val_list:
# print(val)
#sql = 'insert into basic_info (code, name) values (%s, %s)'
#c.executemany(sql, val_list)
#conn.commit()
c.close()
conn.close()
def sum_trade_date(code):
with mysqlcli.get_cursor() as c:
sql = 'select count(code) from {0} where code = "{1}"'.format(
config.sql_tab_quote, code)
c.execute(sql)
r = c.fetchone()
return list(r.values())[0]
def save_stock_name():
stock_code_list = get_all_stock_code()
val_list = []
conn = mysqlcli.get_connection()
c = mysqlcli.get_cursor(conn)
for i, code in enumerate(stock_code_list):
if i % 3 == 0:
stock_info = price.getChinaStockIndividualPriceInfo(code)
elif i % 3 == 1:
stock_info = price.getChinaStockIndividualPriceInfoTx(code)
else:
stock_info = price.getChinaStockIndividualPriceInfoWy(code)
if not stock_info:
print(code)
continue
try:
#val_list.append(tuple([code, stock_info['name']]))
time.sleep(0.1)
sql = 'insert into {0} (code, name) values ("%s", "%s")'.format(
config.sql_tab_basic_info) % tuple([code, stock_info['name']])
c.execute(sql)
conn.commit()
except Exception as e:
print(e)
#for val in val_list:
# print(val)
#sql = 'insert into basic_info (code, name) values (%s, %s)'
#c.executemany(sql, val_list)
#conn.commit()
c.close()
conn.close()
def get_all_stock_code():
with mysqlcli.get_cursor() as c:
#sql = 'SELECT DISTINCT code FROM {0}'.format(config.sql_tab_quote)
sql = "SELECT code FROM {0} where type = 'A股'".format(
config.sql_tab_basic_info)
c.execute(sql)
stock_code_list = c.fetchall()
return [code['code'] for code in stock_code_list]
def get_selected_stock_code():
code_list = []
with mysqlcli.get_cursor() as c:
sql = 'select code from {0}'.format(config.sql_tab_selected)
c.execute(sql)
r = c.fetchall()
for item in r:
code_list.append(item['code'])
return code_list
def insert_or_update_into_fund_basic(data):
# sql_str = 'insert ignore into fund_basic (code, name, scale) values (%s, %s, %s)'
sql_str = 'insert ignore into fund_basic (code, name, scale, last_date) values (%s, %s, %s, %s) as alias ON DUPLICATE KEY UPDATE scale=alias.scale, last_date=alias.last_date'
key_list = ['fund_code', 'fund_name', 'scale', 'last_date']
val_list = [data[key] for key in key_list]
with mysqlcli.get_cursor() as c:
try:
c.executemany(sql_str, [val_list])
except Exception as e:
print(e)
def save_stock_list_into_db(stock_list):
with mysqlcli.get_cursor() as cursor:
sql_fmt = u"INSERT INTO basic_info (code, name) VALUES ('{}', '{}')"
for code, name in stock_list:
sql = sql_fmt.format(code, name)
try:
cursor.execute(sql, None)
except pymysql.err.IntegrityError as e:
pass
except Exception as e:
print(e)
def get_stock_name(code):
with mysqlcli.get_cursor() as c:
try:
sql = 'select name from {0} where code = "{1}"'.format(
config.sql_tab_basic_info, code)
c.execute(sql)
#name = c.fetchall()
r = c.fetchone()
return r['name']
except Exception as e:
print(e)
def get_price_stat_db(code, pv, day, w):
with mysqlcli.get_cursor() as c:
if pv == 'p':
pv = 'close'
else:
pv = 'volume'
sql = 'select {4}({3}) as avg{3} from (select close from {0} where code = "{1}" order by trade_date desc limit {3}) as tmp'.format(
config.sql_tab_quote, code, pv, day, w)
c.execute(sql)
r = c.fetchone()
return list(r.values())[0]
def save_quote_wy():
df_quote = wy.get_quote()
# 修改数据库
'''
update quote set
percent=FORMAT(percent*100, 2), hs=FORMAT(hs*100, 2), lb=FORMAT(lb, 2),
wb=FORMAT(wb*100, 2), zf=FORMAT(zf*100, 2), five_minute=FORMAT(five_minute*100, 2)
where trade_date >= '2017-04-05 00:00:00';
'''
# 部分值转换
key_list = ['PERCENT', 'HS', 'WB', 'ZF', 'FIVE_MINUTE']
for key in key_list:
df_quote[key] = round(df_quote[key] * 100, 2)
key = 'LB'
df_quote[key] = round(df_quote[key], 2)
#print(df_quote[df_quote['CODE'] == '600839'])
with mysqlcli.get_cursor() as c:
try:
# clear temp table
c.execute('truncate table temp_quote')
# MySql connection in sqlAlchemy
engine = create_engine(
'mysql+pymysql://{0}:{1}@127.0.0.1:3306/stock?charset=utf8mb4'.
format(config.db_user, config.db_passwd))
connection = engine.connect()
# Do not insert the row number (index=False)
df_quote.to_sql(name='temp_quote',
con=engine,
if_exists='append',
index=False,
chunksize=20000)
#connection.close()
sql_str = "select code, close, high, low, open, yestclose from quote where code in ('000001', '000002', '000003', '000004', '000005') and trade_date in (select max(trade_date) from quote);"
c.execute(sql_str)
r1 = c.fetchall()
sql_str = "select code, close, high, low, open, yestclose from temp_quote where code in ('000001', '000002', '000003', '000004', '000005') and trade_date in (select max(trade_date) from temp_quote);"
c.execute(sql_str)
r2 = c.fetchall()
r1_sorted = sorted(r1, key=lambda x: x['code'])
r2_sorted = sorted(r2, key=lambda x: x['code'])
if r1_sorted != r2_sorted:
c.execute('insert into quote select * from temp_quote;')
#c.execute('insert into temp_quote_test select * from temp_quote;')
else:
print('not trade day')
except Exception as e:
print(e)
def insert_into_fund_stock(data_arr):
key_list = ['fund_code', 'fund_date', 'code', 'percent', 'price', 'num', 'market_value', 'fund_url', 'crawl_date']
fmt_list = ['%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s']
key = ', '.join(key_list)
fmt = ', '.join(fmt_list)
sql_str = 'insert ignore into fund_stock ({0}) values ({1})'.format(key, fmt)
# val_list = [data[key] for key in key_list]
val_list_arr = [[data[key] for key in key_list] for data in data_arr]
with mysqlcli.get_cursor() as c:
try:
c.executemany(sql_str, val_list_arr)
except Exception as e:
print(e)
def add_selected_history(code):
with mysqlcli.get_cursor() as c:
sql = 'select added_date, class, rank from {0} where code = "{1}" order by added_date desc'.format(
config.sql_tab_selected, code)
c.execute(sql)
r = c.fetchone()
if not r:
return
sql = 'insert into {0} (code, added_date, class, rank) values("{1}", "{2}", "{3}", {4})'.format(
config.sql_tab_selected_history, code, str(r['added_date']),
r['class'], r['rank'])
c.execute(sql)
def get_future_name(code):
if code.find('1') > 0:
code = code[:-4]
elif code[-1] == '0':
code = code[:-1]
with mysqlcli.get_cursor() as c:
try:
sql = 'select name from future_variety where code = "{1}"'.format(
config.sql_tab_basic_info, code)
c.execute(sql)
#name = c.fetchall()
r = c.fetchone()
return r['name']
except Exception as e:
print(e)
def insert_into_quote(val_list):
key_list = [
'code', 'trade_date', 'open', 'high', 'low', 'close', 'volume',
'turnover'
]
fmt_list = ['%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s']
key = ', '.join(key_list)
fmt = ', '.join(fmt_list)
sql_str = 'insert ignore into quote ({0}) values ({1})'.format(key, fmt)
# print(sql_str % tuple(val_list))
with mysqlcli.get_cursor() as c:
try:
c.executemany(sql_str, val_list)
except Exception as e:
print(e)
def add_selected(code, cls='HP', rank=9):
with mysqlcli.get_cursor() as c:
sql = 'select added_date from {0} where code = "{1}" order by added_date desc'.format(
config.sql_tab_selected, code)
c.execute(sql)
r = c.fetchone()
if r:
import datetime
# r['added_date'], datetime.date
#tm = time.strptime(r['added_date'], '%Y-%m-%d')
#t = time.mktime(tm)
#d = datetime.date.fromtimestamp(t)
# datetime.timedelta
if (datetime.date.today() - r['added_date']).days <= 5:
return
remove_selected_keep_history(code)
sql = 'insert into {0} (code, added_date, class, rank) values("{1}", current_date(), "{2}", {3})'.format(
config.sql_tab_selected, code, cls, rank)
# code varchar(8), added_date date, class varchar(8), rank integer
c.execute(sql)
def get_price_info_list_db(code, trade_date=1):
with mysqlcli.get_cursor() as c:
key_list = [
'code', 'trade_date', 'open', 'high', 'low', 'close', 'volume',
'turnover'
]
table = [config.sql_tab_basic_info, config.sql_tab_quote]
on = 'quote.code = basic_info.code'.format(code, trade_date)
where = 'quote.code = "{0}" order by trade_date desc limit {1}'.format(
code, trade_date)
sql = 'SELECT {0} FROM {1} WHERE {5}'.format(', '.join(key_list),
table[1], on, 'name',
table[0], where)
#print(sql)
c.execute(sql)
r = c.fetchall()
if not r:
return None
r = sorted(r, key=lambda x: x['trade_date']) #, reverse=True)
return r
def get_price_info_db(code, trade_date=None):
with mysqlcli.get_cursor() as c:
key_list = [
'code', 'name', 'trade_date', 'open', 'high', 'low', 'close',
'volume', 'turnover'
]
key_list = [
'name', 'trade_date', 'open', 'high', 'low', 'close', 'volume',
'turnover'
]
table = [config.sql_tab_basic_info, config.sql_tab_quote]
where = 'quote.code = {0} and trade_date = "{1}"'.format(
code, trade_date)
where = 'quote.code = {0} and trade_date = "{1}" and quote.code = basic_info.code'.format(
code, trade_date)
where = 'quote.code = basic_info.code'.format(code, trade_date)
on = 'quote.code = basic_info.code'.format(code, trade_date)
if not trade_date:
where = 'basic_info.code = "{0}" order by trade_date desc limit 1'.format(
code)
else:
if type(trade_date) == int:
where = 'basic_info.code = "{0}" order by trade_date desc limit {1},1'.format(
code, trade_date)
else:
where = 'basic_info.code = {0} and trade_date = "{1}"'.format(
code, trade_date)
sql = 'SELECT {0} FROM {1} inner join {4} on {2} WHERE {5}'.format(
', '.join(key_list), table[1], on, 'name', table[0], where)
#print(sql)
c.execute(sql)
r = c.fetchone()
if not r:
return None
r.update({'code': code})
#print(r['trade_date'], r['close'])
return r
def update_stock_list_into_db(stock_list):
with mysqlcli.get_cursor() as cursor:
# Create a new record
#sql_fmt = u"INSERT INTO basic_info (code, name) VALUES ('{}', '{}') ON DUPLICATE KEY update name = {}"
sql_ins = "INSERT INTO basic_info (code, name) VALUES ('{}', '{}')"
sql_sel = 'select name from basic_info where code = "{0}"'
sql_upd = 'update basic_info set name = "{1}" where code = "{0}"'
for code, name in stock_list:
#sql = sql.format(code, name.decode('unicode-escape'))
try:
sql = sql_sel.format(code)
n = cursor.execute(sql, None)
if n == 0:
sql = sql_ins.format(code, name)
cursor.execute(sql, None)
continue
r = cursor.fetchone()
if r['name'] == name:
continue
sql = sql_upd.format(code, name)
#sql = sql_fmt.format(code, name)
cursor.execute(sql, None)
except Exception as e:
print(e) #(1062, "Duplicate entry '603999' for key 'PRIMARY'")
def save(csv_queue):
ok = False
while True:
with mysqlcli.get_cursor() as c:
try:
file_csv = csv_queue.get_nowait()
#if file_csv == 'data/csv/300386.csv':
# ok = True
# print('ok')
# #continue
#if not ok:
# continue
print(file_csv)
#import re
#r = re.search('.*#(.*).txt', file_csv)
#if not r:
# exit(0)
#symbol = r.group(1)
symbol = get_symbol_from_filename(file_csv)
code = gen_stock_code(symbol)
with open(file_csv, 'r', encoding='gbk') as fp:
val_many = []
#reader = csv.DictReader(fp)
#for row in reader:
#AttributeError: 'dict' object has no attribute 'iteritems'
#key_list = ['code', 'trade_date', 'open', 'high', 'low', 'close', 'volume', 'turnover']
#key_list = ['code', 'trade_date', 'close', 'high', 'low', 'open', 'yestclose', 'updown', 'percent', 'hs', 'volume', 'turnover', 'tcap', 'mcap']
key_list = [
'code', 'trade_date', 'close', 'high', 'low', 'open',
'yestclose', 'updown', 'percent', 'hs', 'volume',
'turnover', 'zf'
]
#日期,股票代码,名称,收盘价,最高价,最低价,开盘价,前收盘,涨跌额,涨跌幅,换手率,成交量,成交金额,总市值,流通市值
#key_csv = [日期,收盘价,最高价,最低价,开盘价,成交量,成交金额]
#su = [1, 0, 6, 4, 5, 3, 11, 12]
#indice = [0, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14] #subscript
indice = [0, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12] #subscript
#fmt_list = ['%s', '%s', '%f', '%f', '%f', '%f', '%d', '%d']
#fmt_list = ['%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s']
key = ', '.join(key_list)
#fmt = ', '.join(fmt_list)
fmt = ', '.join(['%s' for i in range(len(key_list))])
sql_str = 'insert into quote({0}) values ({1})'.format(
key, fmt)
for row in fp:
if row.find('股票') >= 0:
continue
val_list = [code]
#for k,v in row.items():
row = row.split(',')
volume = row[11]
if int(volume) <= 0:
continue
for idx in indice:
#key_list.append(k)
val_list.append(
row[idx] if row[idx] != 'None' else 0)
if float(row[7]) == 0:
val_list.append(0.0)
else:
val_list.append(
round((float(row[4]) - float(row[5])) /
float(row[7]) * 100, 2))
val = tuple(val_list)
val_many.append(val)
#c.execute(sql_str, val)
#connection.commit()
c.executemany(sql_str, val_many)
#conn.commit()
#time.sleep(1);
except pymysql.err.IntegrityError as e:
pass
except Empty:
break
except Exception as e:
print(e, file_csv)
def remove_selected_keep_history(code):
with mysqlcli.get_cursor() as c:
add_selected_history(code)
sql = 'delete from {0} where code = "{1}"'.format(
config.sql_tab_selected, code)
c.execute(sql)
