/
SetUpDatabase.py
104 lines (90 loc) · 3.93 KB
/
SetUpDatabase.py
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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
# 爬取人口数据,在数据库data.db中,建一个table
import sqlite3
import NationalStat
import Patent
keys = ['total', 'male', 'female']
def insertdata(cursor, table, year, key, value, type='year'): # 插入数据,若存在,就更新
try:
cursor.execute('insert into %s (%s, %s) values (%s, %s)' % (table, type, key, year, value))
except sqlite3.IntegrityError:
print('已经存在的数据,更新该数据。')
cursor.execute('update %s set %s=? where %s=? ' % (table, key, type), (value, year))
else:
print('成功插入')
def getfromnationalstat(): # 获取数据,建table
getdata = NationalStat.getstat()
print(type(getdata))
print(getdata)
conn = sqlite3.connect('data.db')
# 创建一个Cursor:
cursor = conn.cursor()
try: # 若已建,则提示“已被创建”
cursor.execute('create table population ( \
year varchar(20) primary key, \
total varchar(20), \
male varchar(20), \
female varchar(20))')
except sqlite3.OperationalError:
print('数据库已被创建!')
else:
print("成功创建数据库!")
for i in range(0, 60): # 将20年的数据插入table
insertdata(cursor, 'population', getdata['returndata']['datanodes'][i]['wds'][1]['valuecode'], keys[int(i/20)],
getdata['returndata']['datanodes'][i]['data']['strdata'])
print(type(getdata['returndata']['datanodes'][i]['data']['strdata']))
# 关闭Cursor:
cursor.close()
# 提交事务:
conn.commit()
# 关闭Connection:
conn.close()
def getfrompatent():
getdata = Patent.getstat()
conn = sqlite3.connect('data.db')
# 创建一个Cursor:
cursor = conn.cursor()
try: # 使用try,可以在已被创建时,继续运行代码
cursor.execute('create table patent ( \
year varchar(20) primary key, \
patent varchar(20))')
except sqlite3.OperationalError:
print('数据库已被创建!')
else:
print("成功创建数据库!")
for i in range(0, 8): # 最近八年
insertdata(cursor, 'patent', getdata['returndata']['datanodes'][i]['wds'][1]['valuecode'], 'patent',
getdata['returndata']['datanodes'][i]['data']['data'])
# print(type(getdata['returndata']['datanodes'][i]['data']['strdata']))
# 关闭Cursor:
cursor.close()
# 提交事务:
conn.commit()
# 关闭Connection:
conn.close()
def getfrompatenttype():
getdata = Patent.getstat()
conn = sqlite3.connect('data.db')
# 创建一个Cursor:
cursor = conn.cursor()
try: # 使用try,可以在已被创建时,继续运行代码
cursor.execute('create table patent2016 ( \
type varchar(20) primary key, \
patentnum varchar(20))')
except sqlite3.OperationalError:
print('数据库已被创建!')
else:
print("成功创建数据库!")
for i in range(0, 200): # 最近八年总情况
if getdata['returndata']['datanodes'][i]['wds'][1]['valuecode'] == '2016':
insertdata(cursor, 'patent2016', str(int(i / 10)), 'patentnum',
getdata['returndata']['datanodes'][i]['data']['data'], type='type') # primary key为type
# print(type(getdata['returndata']['datanodes'][i]['data']['strdata']))
# 关闭Cursor:
cursor.close()
# 提交事务:
conn.commit()
# 关闭Connection:
conn.close()
getfromnationalstat() # 更新人口数据
getfrompatent() # 更新年度专利数据
getfrompatenttype() # 更新2016年专利申请情况