def getAllStockInfo(): df = get_stock_basics() conn = sqlite3API.get_conn('stock.db') df.to_sql('stock_info',con=conn,flavor='sqlite', if_exists='replace') #取得流通市值 getLiutong_from_qq()
def insertPosition(position): data = [] for dic in position: dicList = [] dicList.append(dic['买入冻结']) dicList.append(dic['交易市场']) dicList.append(dic['卖出冻结']) dicList.append(dic['参考市价']) dicList.append(dic['参考市值']) dicList.append(dic['参考成本价']) dicList.append(dic['参考盈亏']) dicList.append(dic['当前持仓']) dicList.append(dic['盈亏比例(%)']) dicList.append(dic['股东代码']) dicList.append(dic['股份余额']) dicList.append(dic['股份可用']) dicList.append(dic['证券代码']) dicList.append(dic['证券名称']) data.append(dicList) sql = 'insert into chicang values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)' conn = sqlite3API.get_conn('stock.db') sqlite3API.truncate(conn, 'chicang') sqlite3API.save(conn, sql, data) print('insertPosition OK!') print(data) return data
def getLiutong_from_qq(): q = easyquotation.use('qq') #取上市300天内的股票 stock_list = getCixinCode() stockinfo, stockinfo_zhangting = q.stocks(stock_list) data = [] for key, value in stockinfo.items(): try: infoLiutong = (stockinfo[key]['code'], stockinfo[key]['流通市值']) data.append(infoLiutong) except Exception as e: print(e) for key, value in stockinfo_zhangting.items(): try: infoLiutong = (stockinfo_zhangting[key]['code'], stockinfo_zhangting[key]['流通市值']) data.append(infoLiutong) except Exception as e: print(e) #sql_truncat = 'truncat table liutong_from_qq' sql = 'insert into liutong_from_qq values(?,?)' conn = sqlite3API.get_conn('stock.db') #sqlite3API.save(conn,sql_truncat,data) sqlite3API.truncate(conn, 'liutong_from_qq') sqlite3API.save(conn, sql, data) print('getLiutong_from_qq OK!')
def getListBisi(url): try: html = requests.get(url, timeout=5) # rege1 = re.compile( '>([^<]+)</a>\]</em> <a href="thread-([0-9]+)-1-[0-9]+.html"[^>]+>([^<]+)</a>\n[^\n]+\n[^\n]+\n[^\n]+\n[^\n]+\n[^\n]+\n<a href="([^"]+)"[^>]+>([^<]+)</a>[^\n]+\n<em><span>([^<]+)</span></em>\n</td>\n<td class="num"><a [^>]+>([0-9]+)</a><em>([0-9]+)</em></td>') regex = re.compile( '>([^<]+)</a>\]</em> <a href="thread-([0-9]+)-1-[0-9]+.html"[^>]+>([^<]+)</a>[^\n]*\n[^\n]+\n[^\n]+\n[^\n]+\n[^\n]+\n[^\n]+\n<a href="([^"]+)"[^>]+>([^<]+)</a>[^\n]+\n<em><span>([^<]+)</span></em>[^\n]*\n[^\n]*\n<td class="num"><a [^>]+>([0-9]+)</a><em>([0-9]+)</em></td>' ) rows = regex.findall(html.text) print((rows)) print('regex', len(rows)) # return conn = sqlite3API.get_conn(DB_FILE_PATH) save_sql = 'insert ignore into bisi_discuz_test values(?,?,?,?,?,?,?,?,now())' sqlite3API.save(conn, save_sql, rows) # mysql = mysqlAPI('test') # mysql.insert_mysql('insert ignore into bisi_discuz_test values(%s,%s,%s,%s,%s,%s,%s,%s,now())',rows) # mysql.close_mysql() except: pass time.sleep(1)
def getAllPositionFromSqlite(): conn = sqlite3API.get_conn('stock.db') sql_tid = ''' select mairu_dongjie,jiaoyi_shichang,maichu_dongjie,shijia,shizhi,chengbenjia,yingkui,tangqian_chicang,yingkui_bili,gudong_daima,gufen_yue,gufen_keyong,code,name from chicang ; ''' info_tid = sqlite3API.fetchmany(conn, sql_tid) # print(info_tid) list_dic = [] # dic = dict() for info_temp in info_tid: temp_item = { '买入冻结': info_temp[0], '交易市场': info_temp[1], '卖出冻结': info_temp[2], '参考市价': info_temp[3], '参考市值': info_temp[4], '参考成本价': info_temp[5], '参考盈亏': info_temp[6], '当前持仓': info_temp[7], '盈亏比例(%)': info_temp[8], '股东代码': info_temp[9], '股份余额': info_temp[10], '股份可用': info_temp[11], '证券代码': info_temp[12], '证券名称': info_temp[13] } # dic[info_temp[12]]=temp_item # print(temp_item) list_dic.append(temp_item) return list_dic
def delPositionHuatai(data): conn = sqlite3API.get_conn('stock.db') sql_tid=''' delete from position where code=?; ''' sqlite3API.save(conn,sql_tid,data)
def insertPositionHuatai(data): conn = sqlite3API.get_conn('stock.db') sql_tid=''' insert into position values(?,?); ''' sqlite3API.save(conn,sql_tid,data)
def insertTradeHistory(position_info,min_liutong): sql = 'insert into trade_history values(' + ('?,'*47) + "?,datetime('now'))" conn = sqlite3API.get_conn('stock.db') data = [] data.append(editStockInfo(position_info,'S')) data.append(editStockInfo(min_liutong,'B')) sqlite3API.save(conn,sql,data) print('insertTradeHistory OK!') print (data)
def checkTraderNone(code_position): conn = sqlite3API.get_conn('stock.db') sql_tid = ''' select gufen_keyong from chicang where code = '%s'; ''' info_tid = sqlite3API.fetchmany(conn, sql_tid % code_position) if info_tid and info_tid[0][0] > 0: return True else: return False
def checkFadingJiari(str_time=str(datetime.datetime.now())[:10]): conn = sqlite3API.get_conn('stock.db') sql_tid = ''' select isOpen from trade_calender where calendarDate = '%s'; ''' info_tid = sqlite3API.fetchmany(conn, sql_tid % str_time) if info_tid and info_tid[0][0] == 1: return True else: return False
def checkExistsCode(code): conn = sqlite3API.get_conn('stock.db') sql_tid = ''' select code from chicang where code = '%s' ; ''' info_tid = sqlite3API.fetchmany(conn, sql_tid % code) if info_tid and len(info_tid) > 0: return True else: return False
def getKeyongGufen(code): conn = sqlite3API.get_conn('stock.db') sql_tid = ''' select gufen_keyong from chicang where code = '%s' ; ''' info_tid = sqlite3API.fetchmany(conn, sql_tid % code) if info_tid and len(info_tid) > 0: return info_tid[0][0] else: return 0
def selectSqliteTest(): conn = sqlite3API.get_conn('stock.db') sql_tid = 'select dataTime,lastPrice from t_399006 limit 100' info_tid = sqlite3API.fetchmany(conn, sql_tid) info_x = [] info_y = [] for info in info_tid: info_x.append(info[0]) info_y.append(info[1]) print(info_x, info_y)
def getCixinCode(): conn = sqlite3API.get_conn('stock.db') sql_tid = ''' select code from stock_info where substr(stock_info.timeToMarket,1,4) || '-' || substr(stock_info.timeToMarket,5,2) || '-' || substr(stock_info.timeToMarket,7,2) > date('now','-300 days') --and substr(code,1,1) != '3' ; ''' info_tid = sqlite3API.fetchmany(conn, sql_tid) stock_list = [] for info_temp in info_tid: stock_list.append(info_temp[0]) return stock_list
def getPositionHuatai(): conn = sqlite3API.get_conn('stock.db') sql_tid=''' select * from position ; ''' info_tid=sqlite3API.fetchmany(conn,sql_tid) dic = dict() for info_temp in info_tid: dic[info_temp[0]] = [info_temp[1],info_temp[2],info_temp[3],info_temp[4],info_temp[5]] return dic
def getminline(stockno): conn = sqlite3API.get_conn('stock.db') sql_tid='select dataTime,lastPrice from t_399006 limit 100' info_tid=sqlite3API.fetchmany(conn,sql_tid) info_tid=sqlite3API.fetchmany(conn,sql_tid) info_x = [] info_y = [] for info in info_tid: info_x.append(info[0]) info_y.append(info[1]) return str({"x":info_x,"y":info_y})
def delChiyou(): try: code = request.form['code'] sql = 'delete from chicang where code=?' conn = sqlite3API.get_conn('stock.db') # sqlite3API.truncate(conn,'chicang') data = [[code]] sqlite3API.save(conn,sql,data) return 'delete %s OK' % code except Exception as e: #print(e) return e
def getPosition(): conn = sqlite3API.get_conn('stock.db') sql_tid = ''' select code,gufen_keyong from chicang ; ''' info_tid = sqlite3API.fetchmany(conn, sql_tid) # print(info_tid) dic = dict() stock_list = [] for info_temp in info_tid: dic[info_temp[0]] = str(info_temp[1]) stock_list.append(info_temp[0]) # print('OK' if '6013192' in dic.keys() else '') # print(dic.keys()) return dic
def getReplyContent(url): try: html = requests.get(url, timeout=5) regex = re.compile('<td class="t_f"[^\n]+\n([^<]+)<') rows = regex.findall(html.text) # print(rows) conn = sqlite3API.get_conn(DB_FILE_PATH) save_sql = "insert ignore into reply_content (content,flg) values(?,'0')" sqlite3API.save(conn, save_sql, rows) # mysql = mysqlAPI('test') # mysql.insert_mysql("insert ignore into reply_content (content,flg) values(%s,'0')",rows) # mysql.close_mysql() except: pass time.sleep(1)
def toChiyou(): try: code = request.form['code'] name = request.form['name'] print(code) sql = 'insert into chicang (gufen_keyong,code,name) values(100,?,?)' conn = sqlite3API.get_conn('stock.db') # sqlite3API.truncate(conn,'chicang') data = [[code,name]] sqlite3API.save(conn,sql,data) return 'insert chicang OK' except Exception as e: #print(e) return e
def test_sqlite(): stock_list = [ '002858', '603041', '002857', '603388', '603178', '002816', '603031', '603701', '603991', '002806', '603319', '002796', '603090', '603038', '603990', '603029', '002800', '603908', '002810', '002837', '002835', '603738', '002805', '603960', '603266', '603037', '603819', '603633', '603887', '002856', '603033', '603663', '002830', '603637', '603089', '603032', '002808' ] conn = sqlite3API.get_conn('stock.db') sql_tid = "select code,timeToMarket from stock_info where code in ('" + "','".join( stock_list) + "')" info_tid = sqlite3API.fetchmany(conn, sql_tid) print(type(info_tid)) print(info_tid) dic = dict() for info_temp in info_tid: dic[info_temp[0]] = info_temp[1] print(dic)
def gettimeToMarket(): conn = sqlite3API.get_conn('stock.db') #sql_tid ="select code,timeToMarket from stock_info where code in ('" + "','".join(stock_list) + "')" sql_tid = ''' select stock_info.code,stock_info.timeToMarket from liutong_from_qq inner join stock_info on liutong_from_qq.code = stock_info.code where liutong_from_qq.liutong<13 and substr(liutong_from_qq.code,1,1) != '3' and substr(stock_info.timeToMarket,1,4) || '-' || substr(stock_info.timeToMarket,5,2) || '-' || substr(stock_info.timeToMarket,7,2) > date('now','-270 days') order by liutong_from_qq.liutong limit 40; ''' info_tid = sqlite3API.fetchmany(conn, sql_tid) dic = dict() stock_list = [] for info_temp in info_tid: dic[info_temp[0]] = str(info_temp[1]) stock_list.append(info_temp[0]) return dic, stock_list
def bisi_reply_mulit(userid='marx88', total=1): # mysql = mysqlAPI('test') # info_tid = mysql.select_mysql('select id from bisi_discuz where reply_num > 40 order by RAND() limit ' + str(total)) # info_content = mysql.select_mysql('select content from reply_content order by RAND() limit ' + str(total*3)) # mysql.close_mysql() sql_tid = 'select id,title from bisi_discuz where reply_num > 10 order by RANDOM() limit ' + str( total) sql_content = 'select content from reply_content order by RANDOM() limit ' + str( total * 3) conn = sqlite3API.get_conn(DB_FILE_PATH) info_tid = sqlite3API.fetchmany(conn, sql_tid) info_content = sqlite3API.fetchmany(conn, sql_content) data = userid info_user = sqlite3API.fetchone( conn, "select url,user,password from master where user=?", data) url = info_user[0] user = info_user[1] password = info_user[2] # print (password) discuz = DiscuzAPI(url, user, password) discuz.login() discuz.sign() #访问他人空间得2金钱 discuz.access() #发记录 discuz.speak(msg=str(random.randint(0, 10000))) total = len(info_content) for tid in info_tid: # print(info_content[random.randint(0,total-1)][0]) msg = info_content[random.randint(0, total - 1)][0] try: discuz.reply(tid[0], msg='%s,%s' % (tid[1], msg)) except: pass time.sleep(random.randint(65, 70))
def getPositionHistory(): conn = sqlite3API.get_conn('stock.db') sql_tid=''' select * from position_history ; ''' info_tid=sqlite3API.fetchmany(conn,sql_tid) dic = dict() for info_temp in info_tid: dic[info_temp[0]] = [info_temp[1],info_temp[2],info_temp[3],info_temp[4],info_temp[5],info_temp[6]] dic[info_temp[0]] = { 'name':info_temp[1], 'num':info_temp[2], 'start_price':info_temp[3], 'start_time':info_temp[4], 'end_price':info_temp[5], 'end_time':info_temp[6], 'bizhong':info_temp[7] } return dic
def test_sqlite3(): sql = 'select * from student order by RANDOM() limit 2' DB_FILE_PATH = 'hongten.db' conn = sqlite3API.get_conn(DB_FILE_PATH) sqlite3API.fetchall(conn, sql)