jq php 获取机器码,JQData数据提取及MySQL简单操作——基于Python

无敌小轩肉

于 2021-04-13 23:52:55 发布

阅读量163

点赞数

文章标签： jq php 获取机器码

1 #-*- coding: utf-8 -*-

2 """

3 Created on Sat May 26 19:50:50 20184

5 @author: Luove6 """

8 from jqdatasdk import *#平台给的包，务必加载，地址：https://github.com/JoinQuant/jqdatasdk/archive/master.zip

9 importnumpy as np10 importpandas as pd11 importcsv12 auth('***','***')#依次输入账号、密码，链接到平台数据库

14 #市场整体数据获取

15 #get_all_securities(types='stock',date)

16 #types，为列表，可以为stock/fund/index/futures/etf/lof/fja(分级A)/fjb,为空时返回所有股票不含基金指数期货；date日期表获取这一日期还在上市的股票默认None表获取所有日期股票

17 #上海证券交易所 .XSHG ‘600519.XSHG’ 贵州茅台

18 #深圳证券交易所 .XSHE ‘000001.XSHE’ 平安银行

19 #中金所 .CCFX ‘IC9999.CCFX’ 中证500主力合约

20 #大商所 .XDCE ‘A9999.XDCE’ 豆一主力合约

21 #上期所 .XSGE ‘AU9999.XSGE’ 黄金主力合约

22 #郑商所 .XZCE ‘CY8888.XZCE’ 棉纱期货指数

23 #上海国际能源期货交易所 .XINE ‘SC9999.XINE’ 原油主力合约

24 index = get_all_securities(types='index')25 stock =get_all_securities()26 df = get_all_securities(['fund'])27 get_all_securities(date='2015-10-24')[:5]28

29 stock[:2]30 len(stock)31 type(stock)32

34 #单个标的信息采集

35 get_security_info('000001.XSHG').type36 assert get_security_info('502050.XSHG').parent == '502048.XSHG'

38 #return component stocks,list

39 hs300 = get_index_stocks('000300.XSHG') #hs300 component stocks

41 #股票代码格式转化,转化为XSHG/XSHE

42 #normalize_code(code)

43 for code in ('000001', 'SZ000001', '000001SZ', '000001.sz', '000001.XSHE'):44 print(normalize_code(code))45 #融资融券标的(股票列表)

46 get_margincash_stocks()47 get_marginsec_stocks()48

51 #交易行情获取

52 #获取交易日，都那天交易

53 get_trade_days(start_date='2018-01-01',end_date='2018-05-05')54

55 #get_price(security, start_date=None, end_date=None, frequency='daily', fields=None, skip_paused=False, fq='pre', count=None)

56 #获取一支或者多只股票的行情数据, 按天或者按分钟，这里在使用时注意 end_date 的设置，不要引入未来的数据

57 get_price()58 hsdata = get_price('000300.XSHG',start_date='2013-01-01',end_date='2016-10-31',frequency='5m',skip_paused=True)59 zxbdata = get_price('399005.XSHE',start_date='2013-01-01',end_date='2016-10-31',frequency='5m',skip_paused=True)60 cybdata = get_price('399006.XSHE',start_date='2013-01-01',end_date='2016-10-31',frequency='5m',skip_paused=True)61

62 #hsdata = pd.DataFrame(hsdata)

63 #zxbdata = pd.DataFrame(zxbdata)

64 #cybdata = pd.DataFrame(cybdata)

65 #pandas 文件输出输入操作

66 #save as csv file/pandas.DataFrame.to_excel/csv

67 test.to_csv('D:/Analyze/Python Matlab/Python/DataLib Py/test.csv')68 hsdata.to_csv('D:/Analyze/Python Matlab/Pyxlrdthon/DataLib Py/hs.csv')69 zxbdata.to_csv('D:/Analyze/Python Matlab/Python/DataLib Py/zxb.csv')70 cybdata.to_csv('D:/Analyze/Python Matlab/Python/DataLib Py/cyb.csv')71

72 #defaults to ',' for read_csv(), \t for read_table()

74 c = pd.read_csv('D:/Analyze/Python Matlab/Python/DataLib Py/test.csv')75

77 #save as excel file

78 writer = pd.ExcelWriter('output.xlsx')79 test.to_excel(writer,'Sheet1')80 writer.save()81

82 b = pd.read_excel('output.xlsx', sheet_name='Sheet1')83

84 #操作DataFrame行列名

85 c.columns.values.tolist()86 c1=c.rename(columns={'Unnamed: 0':'date'})87 c.rename(columns={'Unnamed: 0':'date'},inplace=True) #inpalce表示改变自身而非另外生成一个拷贝

88 c.columns.values.tolist()89 c1.columns.values.tolist()90

92 #链接MySQL

93 importpymysql94 #查询，需要事先熟悉数据库

95 ##host='127.0.0.1',user='***',password='***',db='test'；星号号处填上数据库的对应口令即可，test是你要预先MySql中建立的库

96 db = pymysql.connect(host='127.0.0.1',user='***',password='***',db='test')97 cur=db.cursor()98

99 sq1='select * from test_table'

100 try:101 cur.execute(sq1)102 results=cur.fetchall()103 print('date','height','weight','bmi')104 for row inresults:105 date=row[0]106 height=row[1]107 weight=row[2]108 bmi=row[3]109 print(date,height,weight,bmi)110 exceptException as e:111 raisee112 finally:113 db.close()114

115 #创建表

116 #creat a table,named hs5

117 #存在则先删除

118 #cur.execute('DROP TABLE IF EXISTS hs5') #和下面效果一致

119 #cur.execute("drop table if exists hs5") # 无此句，若已存在，会报错

120 sq2='''create table hs5(date datetime,open float(16),close float(16),high float(16),low float(16),volume float(16),money float(16)) engine=innodb charset utf8'''

121 cur.execute(sq2)122

123 #插入表

124

125 from sqlalchemy importcreate_engine126 #conn = create_engine('mysql+mysqldb://root:password@localhost:3306/databasename?charset=utf8')

127 engine = create_engine('mysql+pymysql://root:1024@localhost:3306/test')128 hsdata.to_sql('hs5',con=engine,if_exists='replace')129 zxbdata.to_sql('zxb',con=engine,if_exists='replace')130 cybdata.to_sql('cyb',con=engine,if_exists='replace')131

132 db.close() #用完close好习惯