| 124 | 2017-08-31 |
| 125 | """ |
| 126 | def insert_tables(self, name, table_name_list,table_date_list, url_list): |
| 127 | #打开数据库连接:host-连接主机地址,port-端口号,user-用户名,passwd-用户密码,db-数据库名,charset-编码 |
| 128 | conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='yourpasswd',db='financialdata',charset='utf8') |
| 129 | #使用cursor()方法获取操作游标 |
| 130 | cursor = conn.cursor() |
| 131 | #插入信息 |
| 132 | for i in range(len(table_name_list)): |
| 133 | sys.stdout.write(' [正在下载 ] %s' % table_name_list[i] + '\r') |
| 134 | #获取数据地址 |
| 135 | url = self.server + 'hk/service/cwsj_service.php?symbol={}&start={}&end={}&type={}&unit=yuan'.format(code,table_date_list[i][-1],table_date_list[i][0],url_list[i]) |
| 136 | req_table = requests.get(url = url, headers = self.headers) |
| 137 | table = req_table.json() |
| 138 | nums = len(table) |
| 139 | value_dict = {} |
| 140 | for num in range(nums): |
| 141 | sys.stdout.write(' [正在下载 %.2f%%] ' % (((num+1) / nums)*100) + '\r') |
| 142 | sys.stdout.flush() |
| 143 | value_dict['股票名'] = name |
| 144 | value_dict['股票代码'] = code |
| 145 | for key, value in table[i].items(): |
| 146 | if key in self.table_dict[url_list[i]]: |
| 147 | value_dict[self.table_dict[url_list[i]][key]] = value |
| 148 | |
| 149 | sql1 = """ |
| 150 | INSERT INTO %s (`股票名`,`股票代码`,`报表日期`) VALUES ('%s','%s','%s')""" % (url_list[i],value_dict['股票名'],value_dict['股票代码'],value_dict['报表日期']) |
| 151 | try: |
| 152 | cursor.execute(sql1) |
| 153 | # 执行sql语句 |
| 154 | conn.commit() |
| 155 | except: |
| 156 | # 发生错误时回滚 |
| 157 | conn.rollback() |
| 158 | |
| 159 | for key, value in value_dict.items(): |
| 160 | if key not in ['股票名','股票代码','报表日期']: |
| 161 | sql2 = """ |
| 162 | UPDATE %s SET %s='%s' WHERE `股票名`='%s' AND `报表日期`='%s'""" % (url_list[i],key,value,value_dict['股票名'],value_dict['报表日期']) |
| 163 | try: |
| 164 | cursor.execute(sql2) |
| 165 | # 执行sql语句 |
| 166 | conn.commit() |
| 167 | except: |
| 168 | # 发生错误时回滚 |
| 169 | conn.rollback() |
| 170 | value_dict = {} |
| 171 | print(' [下载完成 ') |
| 172 | |
| 173 | # 关闭数据库连接 |
| 174 | cursor.close() |
| 175 | conn.close() |
| 176 | |
| 177 | if __name__ == '__main__': |
| 178 | print('*' * 100) |