Python连接Mysql获取数据写入excel

 

共涉及到三个库：

1. pymysql 操作数据库

2. xlwt 操作Excel

3. requests 请求接口数据进行数据解析筛选

#!/usr/bin/env python3
# -*- coding:utf-8 -*-

import pymysql
import requests
import time
import xlwt

book = xlwt.Workbook()
sheet = book.add_sheet("weather")

header = {"user-agent":'{*******}',
          }
conn = pymysql.connect(
    host='hostAddress',
    user = 'test',
    password = 'pw',
    db = 'testdb',
    charset = 'utf8'
)
cur = conn.cursor()

select_sqli = "select Code from map group by Code;"
result = cur.execute(select_sqli)

print(result)
# info = cur.fetchall()
# info = cur.fetchmany(20)
weather=[]
row = 0
cur.scroll(600, mode="absolute")
for i in range(0,10):
    info = cur.fetchone()
    for element in info:
        url = "https://*******?city=" + element
        header.update(city = element)
        header.update(timestamp = str(int(time.time())))
        rd = dict(requests.get(headers = header, url=url).json()["weather"])

    if "weather" in rd.keys():
        weather.append(rd["weather"])
        rd.update(code = element)

        if i == 0:
            j = 0
            for k in rd:
                sheet.write(0, j, k)
                sheet.write(1, j, rd[k])
                j+=1
            row+=2

        else:
            if weather[i] in weather[0:i]:
                pass
            else:
                j =  0
                for k in rd:
                    sheet.write(row, j, rd[k])
                    j+=1
                row+=1
    else:
        weather.append("Null")


cur.close()
conn.close()
book.save("test.xls")