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)