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)
