有一个同学用我的推荐链接注册了tushare社区帐号https://tushare.pro/register?reg=671815,现在有了170分积分。目前使用数据的频率受限制。不过可以在调试期间通过python控制台获取数据,将数据保存在本地以后使用不用高频率访问tushare数据接口,访问频率限制影响不大。
>>> data = pro.stock_basic(fields='ts_code,symbol,name,area,industry,list_date,market,is_hs,list_status,exchange,delist_date,curr_type')
>>> type(data)
<class 'pandas.core.frame.DataFrame'>
>>> data
ts_code symbol name area ... list_status list_date delist_date is_hs
0 000001.SZ 000001 平安银行 深圳 ... L 19910403 None S
1 000002.SZ 000002 万科A 深圳 ... L 19910129 None S
2 000004.SZ 000004 国华网安 深圳 ... L 19910114 None N
3 000006.SZ 000006 深振业A 深圳 ... L 19920427 None S
4 000007.SZ 000007 *ST全新 深圳 ... L 19920413 None N
... ... ... ... ... ... ... ... ... ...
5360 873726.BJ 873726 卓兆点胶 江苏 ... L 20231019 None N
5361 873806.BJ 873806 云星宇 北京 ... L 20240111 None N
5362 873833.BJ 873833 美心翼申 重庆 ... L 20231108 None N
5363 920002.BJ 920002 万达轴承 None ... L 20240530 None N
5364 689009.SH 689009 九号公司-WD 北京 ... L 20201029 None None
[5365 rows x 12 columns]
>>> data.info
<bound method DataFrame.info of ts_code symbol name area ... list_status list_date delist_date is_hs
0 000001.SZ 000001 平安银行 深圳 ... L 19910403 None S
1 000002.SZ 000002 万科A 深圳 ... L 19910129 None S
2 000004.SZ 000004 国华网安 深圳 ... L 19910114 None N
3 000006.SZ 000006 深振业A 深圳 ... L 19920427 None S
4 000007.SZ 000007 *ST全新 深圳 ... L 19920413 None N
... ... ... ... ... ... ... ... ... ...
5360 873726.BJ 873726 卓兆点胶 江苏 ... L 20231019 None N
5361 873806.BJ 873806 云星宇 北京 ... L 20240111 None N
5362 873833.BJ 873833 美心翼申 重庆 ... L 20231108 None N
5363 920002.BJ 920002 万达轴承 None ... L 20240530 None N
5364 689009.SH 689009 九号公司-WD 北京 ... L 20201029 None None
[5365 rows x 12 columns]>
>>> data.describe()
ts_code symbol name area ... list_status list_date delist_date is_hs
count 5365 5365 5365 5358 ... 5365 5365 0 5364
unique 5365 5365 5364 32 ... 1 2727 0 3
top 000001.SZ 000001 三维股份 浙江 ... L 20200727 NaN N
freq 1 1 2 706 ... 5365 31 NaN 2481
[4 rows x 12 columns]
>>> data.index
RangeIndex(start=0, stop=5365, step=1)
>>> data.columns
Index(['ts_code', 'symbol', 'name', 'area', 'industry', 'market', 'exchange',
'curr_type', 'list_status', 'list_date', 'delist_date', 'is_hs'],
dtype='object')
>>> data.shape
(5365, 12)
>>> data.shape[0]
5365
>>> data.shape[1]
12
>>> data.values
array([['000001.SZ', '000001', '平安银行', ..., '19910403', None, 'S'],
['000002.SZ', '000002', '万科A', ..., '19910129', None, 'S'],
['000004.SZ', '000004', '国华网安', ..., '19910114', None, 'N'],
...,
['873833.BJ', '873833', '美心翼申', ..., '20231108', None, 'N'],
['920002.BJ', '920002', '万达轴承', ..., '20240530', None, 'N'],
['689009.SH', '689009', '九号公司-WD', ..., '20201029', None, None]],
dtype=object)
>>>
>>> print(data.dtypes)
ts_code object
symbol object
name object
area object
industry object
market object
exchange object
curr_type object
list_status object
list_date object
delist_date object
is_hs object
dtype: object
>>>
1、DataFrame操作
tushare pro接口返回的数据类型<class 'pandas.core.frame.DataFrame'>
>>> type(data)
<class 'pandas.core.frame.DataFrame'>
从上面可以看到data = pro.stock_basic(fields='ts_code,symbol,name,area,industry,list_date,market,is_hs,list_status,exchange,delist_date,curr_type')返回的数据是[5365 rows x 12 columns]
pandas.DataFrame.info
打印一个DataFrame的简要介绍(index范围、columns的dtype、非空值的数量和内存的使用情况):
DataFrame.info(verbose=None, buf=None, max_cols=None, memory_usage=None, show_counts=None)[source]
verbose(adj 冗长的): bool, optional,决定是否打印完整的摘要, 如果为False,那么会省略一部分
buf: writable buffer, defaults to sys.stdout,,决定将输出发送到哪里,默认情况下, 输出打印到sys.stdout
max_cols: int, optional 从“详细输出”转换为“缩减输出”,如果DataFrame的列数超过max_cols,则缩减输出。
memory_usage: bool, str, optional 决定是否应显示DataFrame元素(包括索引)的总内存使用情况,默认情况下为True。True始终显示内存使用情况;False永远不会显示内存使用情况。
show_counts: bool, optional,是否显示非空值的数量,值为True始终显示计数,而值为False则不显示计数
>>> data.info(verbose=True)
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5365 entries, 0 to 5364
Data columns (total 12 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 ts_code 5365 non-null object
1 symbol 5365 non-null object
2 name 5365 non-null object
3 area 5358 non-null object
4 industry 5358 non-null object
5 market 5365 non-null object
6 exchange 5365 non-null object
7 curr_type 5365 non-null object
8 list_status 5365 non-null object
9 list_date 5365 non-null object
10 delist_date 0 non-null object
11 is_hs 5364 non-null object
dtypes: object(12)
memory usage: 251.5+ KB
>>> data.info(verbose=False)
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5365 entries, 0 to 5364
Columns: 12 entries, ts_code to is_hs
dtypes: object(12)
memory usage: 251.5+ KB
>>>
>>> print(data.tail())
ts_code symbol name area ... list_status list_date delist_date is_hs
5360 873726.BJ 873726 卓兆点胶 江苏 ... L 20231019 None N
5361 873806.BJ 873806 云星宇 北京 ... L 20240111 None N
5362 873833.BJ 873833 美心翼申 重庆 ... L 20231108 None N
5363 920002.BJ 920002 万达轴承 None ... L 20240530 None N
5364 689009.SH 689009 九号公司-WD 北京 ... L 20201029 None None[5 rows x 12 columns]
>>> print(data.head())
ts_code symbol name area ... list_status list_date delist_date is_hs
0 000001.SZ 000001 平安银行 深圳 ... L 19910403 None S
1 000002.SZ 000002 万科A 深圳 ... L 19910129 None S
2 000004.SZ 000004 国华网安 深圳 ... L 19910114 None N
3 000006.SZ 000006 深振业A 深圳 ... L 19920427 None S
4 000007.SZ 000007 *ST全新 深圳 ... L 19920413 None N[5 rows x 12 columns]
>>>
# 获得DataFrame行索引信息
data.index
# 获得
DataFrame列索引信息
data.columns
# 获得DataFrame的size
data.shape
# 获得
DataFrame的行数
data.shape[0]
# 获得DataFrame的 列数
data
.shape[1]# 获得DataFrame中的值
data
.values# 获得DataFrame中列值数据类型
data.dtypes
Pandas describe()
Pandas describe()用于查看一些基本的统计详细信息,例如每列的均值、标准差、最大值、最小值和众数
>>> data.describe()
ts_code symbol name area ... list_status list_date delist_date is_hs
count 5365 5365 5365 5358 ... 5365 5365 0 5364
unique 5365 5365 5364 32 ... 1 2727 0 3
top 000001.SZ 000001 三维股份 浙江 ... L 20200727 NaN N
freq 1 1 2 706 ... 5365 31 NaN 2481[4 rows x 12 columns]
>>> type(data.describe())
<class 'pandas.core.frame.DataFrame'>
>>>
describe()的输出也是DataFrame
>>> import pandas as pd
>>> import pdb
>>>
dict_data={"X":list("abcdef"),"Y":list("defghi"),"Z":list("ghijkl")}
df=pd.DataFrame.from_dict(dict_data)
df.index=["A","B","C","D","E","F"]
>>> df
X Y Z
A a d g
B b e h
C c f i
D d g j
E e h k
F f i l
>>> df.describe()
X Y Z
count 6 6 6
unique 6 6 6
top a d g
freq 1 1 1
>>>
>>> type(df.describe())
<class 'pandas.core.frame.DataFrame'>
>>>
>>> # A 行 X 列数据,必须两个数据都输入,否则报错
print(df.at["A","X"])
# 第二 行 第二 列数据,序号从0开始
print(df.iat[2,2])
a
i
>>>
>>> # 指定行名和列名的方式,和at的用法相同
print(df.loc["A","X"],"\n","*"*20)
# 可以完整切片,这是 at 做不到的
print(df.loc[:,"X"],"\n","*"*20)
# 可以从某一行开始切片
print(df.loc["B":,"X"],"\n","*"*20)
# 可以只切某一列
print(df.loc["B",:],"\n","*"*20)
# 和指定上一条代码效果是一样的
print(df.loc["B"],"\n","*"*20)
a
********************
A a
B b
C c
D d
E e
F f
Name: X, dtype: object
********************
B b
C c
D d
E e
F f
Name: X, dtype: object
********************
X b
Y e
Z h
Name: B, dtype: object
********************
X b
Y e
Z h
Name: B, dtype: object
********************
>>>
>>> # 指定行号和列号的方式,和 loc 的用法相同
print(df.iloc[0,0],"\n","*"*20)
# 可以完整切片
print(df.iloc[:,0],"\n","*"*20)
# 可以从某一行开始切片
print(df.iloc[1:,0],"\n","*"*20)
# 可以只切某一列
print(df.iloc[1,:],"\n","*"*20)
# 和指定上一条代码效果是一样的
print(df.iloc[1],"\n","*"*20)
a
********************
A a
B b
C c
D d
E e
F f
Name: X, dtype: object
********************
B b
C c
D d
E e
F f
Name: X, dtype: object
********************
X b
Y e
Z h
Name: B, dtype: object
********************
X b
Y e
Z h
Name: B, dtype: object
********************
>>>
DataFrame索引数据
at 函数:通过行名和列名来取值
loc函数主要通过 行标签 索引行数据
iloc函数主要通过行号、索引行数据
导出数据
dataframe可以使用to_csv方法方便地导出到csv文件中,如果数据中含有中文,一般encoding指定为”utf-8″,否则导出时程序会因为不能识别相应的字符串而抛出异常,index指定为False表示不用导出dataframe的index数据。
>>> data.to_csv("C:\\Users\\Downloads\\stock.csv", index=False)
>>> data.to_csv("C:\\Users\\Downloads\\stock_indx.csv", index=True)
如果没有tushare访问权限可以下载csv表格:https://download.csdn.net/download/dangdanding/89525807
index为False和True时区别如下
从文件读取数据到pandas
pandas在读取csv文件是通过read_csv这个函数读取
base_data = pd.read_csv("C:\\Users\\Downloads\\stock.csv")
base_data1 = pd.read_csv("C:\\Users\\Downloads\\stock_idx.csv") #比上一个文件多一列
看我发现了什么神奇的宝藏:从零开始用Python实现股票量化交易之小白笔记(1)-CSDN博客
躺平了,照着做吧。
mysql数据库
mysql -u root -p
alter user root@localhost identified by 'password';
create database stock;
use stock
CREATE TABLE `stock_basic` (
`item_id` int(11) DEFAULT NULL,
`ts_code` varchar(12) DEFAULT NULL,
`symbol` varchar(10) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
`area` varchar(10) DEFAULT NULL,
`industry` varchar(50) DEFAULT NULL,
`market` varchar(10) DEFAULT NULL,
`exchange` varchar(10) DEFAULT NULL,
`curr_type` varchar(10) DEFAULT NULL,
`list_status` varchar(5) DEFAULT NULL,
`list_date` varchar(10) DEFAULT NULL,
`delist_date` varchar(20) DEFAULT NULL,
`is_hs` varchar(5) DEFAULT NULL,
KEY `ix_stock_basic_index` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `stock_daily_qfq` (
`item_id` int(11) NOT NULL AUTO_INCREMENT,
`trade_date` varchar(10) DEFAULT '' COMMENT '交易日',
`ts_code` varchar(12) DEFAULT '' COMMENT '股票代码',
`open` decimal(10,2) DEFAULT '0.00' COMMENT '开盘价',
`high` decimal(10,2) DEFAULT '0.00' COMMENT '最高价',
`low` decimal(10,2) DEFAULT '0.00' COMMENT '最低价',
`close` decimal(10,2) DEFAULT '0.00' COMMENT '收盘价',
`pre_close` decimal(10,2) DEFAULT '0.00' COMMENT '昨日收盘价',
`change` decimal(10,2) DEFAULT '0.00' COMMENT '价格变化',
`pct_chg` double(16,4) DEFAULT '0.0000' COMMENT '涨跌幅',
`vol` decimal(10,2) DEFAULT '0.00' COMMENT '成交量(手)',
`amount` double(16,4) DEFAULT '0.0000' COMMENT '成交额(千元)',
`turnover_rate` double(16,4) DEFAULT NULL COMMENT '换手率',
`volume_ratio` decimal(10,2) DEFAULT '0.00' COMMENT '量比',
`ma5` decimal(10,2) DEFAULT '0.00' COMMENT '五日均线',
`ma_v_5` decimal(10,2) DEFAULT '0.00' COMMENT '5日指数平均值',
`ma10` decimal(10,2) DEFAULT '0.00',
`ma_v_10` decimal(10,2) DEFAULT '0.00',
`ma30` decimal(10,2) DEFAULT '0.00',
`ma_v_30` decimal(10,2) DEFAULT '0.00',
`ma60` decimal(10,2) DEFAULT '0.00',
`ma_v_60` decimal(10,2) DEFAULT '0.00',
`ma13` decimal(10,2) DEFAULT '0.00',
`ma_v_13` decimal(10,2) DEFAULT '0.00',
`ma21` decimal(10,2) DEFAULT '0.00',
`ma_v_21` decimal(10,2) DEFAULT '0.00',
`ma55` decimal(10,2) DEFAULT '0.00',
`ma_v_55` decimal(10,2) DEFAULT '0.00',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_key` (`trade_date`,`ts_code`) USING BTREE,
KEY `ts_code` (`ts_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=203 DEFAULT CHARSET=utf8
mysql> show tables;
+-----------------+
| Tables_in_stock |
+-----------------+
| stock_basic |
+-----------------+
1 row in set (0.00 sec)
mysql>
mysql> describe stock_basic;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| item_id | int(11) | YES | MUL | NULL | |
| ts_code | varchar(12) | YES | | NULL | |
| symbol | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| area | varchar(10) | YES | | NULL | |
| industry | varchar(50) | YES | | NULL | |
| market | varchar(10) | YES | | NULL | |
| exchange | varchar(10) | YES | | NULL | |
| curr_type | varchar(10) | YES | | NULL | |
| list_status | varchar(5) | YES | | NULL | |
| list_date | varchar(10) | YES | | NULL | |
| delist_date | varchar(20) | YES | | NULL | |
| is_hs | varchar(5) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
13 rows in set (0.00 sec)
quit
在这儿遇到一个坑折腾好几天,只能为啥不是一个dba呢?只依稀记得一些sql语句,建数据库表的时候用了index作为字段名,很奇怪因为建表时没有任何错误提示所以潜意识不认为index作为字段名有什么错。实际index是保留关键字,通过pymysql插入数据时总是报错:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'index
) values (1)' at line 1
mysql>
将index用别的字符串替换一切正常,看一下表大小:
mysql> SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = 'stock' AND table_name = 'stock_basic';
+-------------+------------+
| table_name | table_rows |
+-------------+------------+
| stock_basic | 16069 |
+-------------+------------+
1 row in set (0.01 sec)
mysql>
一口气insert了好几遍,实际应该只有5361多行,
>>> import pandas as pd
import tushare
print (tushare.__version__)
tushare.set_token('f9069ca5e3931347503e81967e161590b3c3859e8cba31e94da1f517')
pro = tushare.pro_api()
data = pro.stock_basic(fields='ts_code,symbol,name,area,industry,market,exchange,curr_type,list_status,list_date,delist_date,is_hs')
print(data.shape[0])
1.4.6
5361
>>>
删除所有数据:
TRUNCATE TABLE stock_basic;
>>> import pymysql
>>> db = pymysql.connect(host='127.0.0.1', user='root', passwd='password', db='stock', charset='utf8')
>>> cursor = db.cursor()
>>> cursor.execute("TRUNCATE TABLE stock_basic;")
0
>>> db.commit()
>>>
mysql> SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = 'stock' AND table_name = 'stock_basic';
+-------------+------------+
| table_name | table_rows |
+-------------+------------+
| stock_basic | 0 |
+-------------+------------+
1 row in set (0.01 sec)
mysql>
所有stock列表都保存到本地数据库了,总结一下代码:
mysql> SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = 'stock' AND table_name = 'stock_basic';
+-------------+------------+
| table_name | table_rows |
+-------------+------------+
| stock_basic | 5317 |
+-------------+------------+
1 row in set (0.01 sec)
mysql>
#coding='utf-8'
import pandas as pd
import tushare
print (tushare.__version__)
tushare.set_token('f9069ca5e3931347503e81967e161590b3c3859e8cba31e94da1f517')
pro = tushare.pro_api()
data = pro.stock_basic(fields='ts_code,symbol,name,area,industry,market,exchange,curr_type,list_status,list_date,delist_date,is_hs')
print(data.shape[0])
#print(data)
'''
create database stock;
use stock;
CREATE TABLE `stock_basic` (
`item_id` int(11) DEFAULT NULL,
`ts_code` varchar(12) DEFAULT NULL,
`symbol` varchar(10) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
`area` varchar(10) DEFAULT NULL,
`industry` varchar(50) DEFAULT NULL,
`market` varchar(10) DEFAULT NULL,
`exchange` varchar(10) DEFAULT NULL,
`curr_type` varchar(10) DEFAULT NULL,
`list_status` varchar(5) DEFAULT NULL,
`list_date` varchar(10) DEFAULT NULL,
`delist_date` varchar(20) DEFAULT NULL,
`is_hs` varchar(5) DEFAULT NULL,
KEY `ix_stock_basic_index` (`index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
'''
import pymysql
try:
db = pymysql.connect(host='127.0.0.1', user='root', passwd='password', db='stock', charset='utf8')
cursor = db.cursor()
cursor.execute("TRUNCATE TABLE stock_basic;")
db.commit()
except Exception as e:
print("failed to connect to database: %s"%e)
#ts_code,symbol,name,area,industry,list_date,market,is_hs,list_status,exchange,delist_date,curr_type
query = "INSERT INTO stock_basic (item_id, ts_code,symbol,name,area,industry,list_date,market,is_hs,list_status,exchange,delist_date,curr_type) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
#解决方案:The format string is not really a normal Python format string. Youmust always use %s for all fields. 也就是MySQLdb的字符串格式化不是标准的python的字符串格式化,应当一直使用%s用于字符串格式化
try:
for r in range(0, len(data)):
item_id = r
ts_code = data.iloc[r,0]
symbol = data.iloc[r,1]
name = data.iloc[r,2]
area = data.iloc[r,3]
industry = data.iloc[r,4]
list_date = data.iloc[r,5]
market = data.iloc[r,6]
is_hs = data.iloc[r,7]
list_status = data.iloc[r,8]
exchange = data.iloc[r,9]
delist_date = data.iloc[r,10]
curr_type = data.iloc[r,11]
values = (item_id, ts_code,symbol,name,area,industry,list_date,market,is_hs,list_status,exchange,str(delist_date),curr_type)
#print(values)
cursor.execute(query,values)
db.commit()
except Exception as e:
print("got error while inserting values to table stock_basic: %s"%e)
finally:
cursor.close()
db.close()
print("数据库连接关闭!")
mysql> select ts_code,name,industry,list_date from stock_basic where item_id=1;
+-----------+-------+----------+-----------+
| ts_code | name | industry | list_date |
+-----------+-------+----------+-----------+
| 000002.SZ | 万科A | 全国地产 | 主板 |
+-----------+-------+----------+-----------+
1 row in set (0.00 sec)
mysql> select ts_code,name,industry,list_date from stock_basic where item_id=0;
+-----------+----------+----------+-----------+
| ts_code | name | industry | list_date |
+-----------+----------+----------+-----------+
| 000001.SZ | 平安银行 | 银行 | 主板 |
+-----------+----------+----------+-----------+
1 row in set (0.00 sec)
mysql> select ts_code,name,industry,list_date from stock_basic where item_id=1000;
+-----------+----------+----------+-----------+
| ts_code | name | industry | list_date |
+-----------+----------+----------+-----------+
| 002506.SZ | 协鑫集成 | 电气设备 | 主板 |
+-----------+----------+----------+-----------+
1 row in set (0.00 sec)
这里数据库mysql有一个坑,建表时用的sql语句里面的字段顺序和pymysql插入insert语句的顺序不一致导致stock列表保存到本地数据库里的列名不一致。特意搜了一下有说在python中使用insert语句插入值时如果指定了列名称不用要求列顺序和表实际列名顺序一致,我这样做了实际结果在mysql中并没有按python中指定的列名存入相应的值。
今天登陆看了下有270积分了,感谢使用了我的推荐码的亲,这个积分现在还是每小时只能访问1次数据接口。不过数据存入本地后也不用太频繁访问tushare接口。
File "C:\Program Files\Thonny\lib\site-packages\tushare\pro\client.py", line 44, in query
raise Exception(result['msg'])
Exception: 抱歉,您每小时最多访问该接口1次,权限的具体详情访问:https://tushare.pro/document/1?doc_id=108。
https://tushare.pro/register?reg=671815 分享此链接,成功注册一个有效用户(指真正会使用tushare数据的用户)可获得50积分
权限还是不够,不能下载 pro_bar接口的复权行情数据,2000分才可以。
>>> df = tushare.pro_bar(ts_code=ts_code, adj='qfq', start_date=list_date, end_date=end_d, ma=[5, 10, 30, 60, 13, 21, 55], factors=['tor', 'vr'])
df = df.ix[:, 1:] #遇到了返回值有相同列,做了这个处理
抱歉,您没有访问该接口的权限,权限的具体详情访问:https://tushare.pro/document/1?doc_id=108。
https://tushare.pro/register?reg=671815 分享此链接,成功注册一个有效用户(指真正会使用tushare数据的用户)可获得50积分