[020量化交易] python读取股票信息并写入数据库2

import json
import tushare as ts
import time
import sqlite3
import pandas as pd

# 初始化pro接口
pro = ts.pro_api('')


# # Python 字典类型转换为 JSON 对象
# data1 = {
#     'no': 1,
#     'name': 'C君莫笑',
#     'url': 'https://blog.csdn.net/qq_34623621/article/details/124109182'
# }
#
# json_str = json.dumps(data1)  # json.dumps(): 对数据进行编码。
# print("Python 原始数据：", repr(data1))  # 返回一个对象的 string 格式。
# print("JSON 对象：", json_str)  # json.loads(): 对数据进行解码。
#
# # 将 JSON 对象转换为 Python 字典
# data2 = json.loads(json_str)  # json.loads(): 对数据进行解码。
# print("data2['name']: ", data2['name'])
# print("data2['url']: ", data2['url'])
#
# # # 写入 JSON 数据
# # with open('data.json', 'w') as f:
# #     json.dump(data1, f)
#
# print('Read json file.')
# # 读取数据
# with open('stock_pool.json', 'r', encoding='UTF-8') as f:
#     data = json.load(f)
#     # print(data)
#     print(data['股票']['平安银行'])
#     print(data['指数']['上证综指'])

def json_to_str():
    # load: 将文件中的字符串变换为数据类型
    with open("stock_pool.json", 'r', encoding='UTF-8') as load_f:
        stock_index = json.load(load_f)
    print(stock_index)  # 
    # {'指数': {'上证综指': '000001.SH', ..... '上证180': '000010.SH'}, '股票': {'平安银行': '000001.SZ', '万科A': '000002.SZ', .....}}
    print(type(stock_index))  # 

    print(stock_index['指数']['上证综指'])  # sh.000001
    print(stock_index['股票']['平安银行'])  # 000001.SZ
    return stock_index


stock_code = json_to_str()
num = 0
print("打印股票代码code:")
for code in stock_code['股票'].values():
    print("num:" + str(num), end=' ')
    print(code)
    num = num + 1

'''
num:4880 871857.BJ
num:4881 871970.BJ
num:4882 871981.BJ
num:4883 872925.BJ
num:4884 873169.BJ
num:4885 873223.BJ
num:4886 689009.SH
'''

print('open data base.')
conn = sqlite3.connect('stock-data.db')
c = conn.cursor()

try:
    print("获取股票数据.")
    print(code)
    data = pro.daily(ts_code='000006.SZ', start_date='20190101', end_date='20190301')
    time.sleep(0.2)
    data.to_sql('STOCK000651', conn, index=False, if_exists='append')
    print("right code is %s" % code)
except:
    print("error code is %s" % code)


print("read sqlite data to show.")
sql_gldq = pd.read_sql_query("select * from 'STOCK000651';", conn)
print(sql_gldq)


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89

import json
import tushare as ts
import time
import sqlite3
import pandas as pd

# 初始化pro接口
pro = ts.pro_api('9f61340e69c5695ac3fe99b33ea58ce9dc55fb235f10aacd781c37df')

print('open data base.')
conn = sqlite3.connect('stock-data.db')
c = conn.cursor()

# establish form  # 创建表
if True:
    try:
        c.execute('''CREATE TABLE SZ000006
           (ts_code           TEXT    NOT NULL,
           trade_date          TEXT    NOT NULL,
           open          REAL,
           high           REAL,
           low         REAL,
           close          REAL,
           pre_close         REAL,
           change         REAL,
           pct_chg         REAL,
           vol         REAL,
           amount         REAL);''')
        conn.commit()
    except:
        print("establish table error.")

try:
    # print("获取股票数据.")
    # print(code)
    data = pro.daily(ts_code='000006.SZ', start_date='20190101', end_date='20190301')
    time.sleep(0.2)
    # print(data)
    data.to_sql('SZ000006', conn, index=False, if_exists='append')
    # print("right code is %s" % code)
except:
    print("error code is .")

print("read sqlite data to show.")
sql_gldq = pd.read_sql_query("select * from 'SZ000006';", conn)
print(sql_gldq)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47