py数据写进sql server

目录

python操作建表

全国疫情数据获取之SQL表

---

• python操作建表

代码：

"""
2022年

CSDN:抄代码抄错的小牛马
"""
import pymssql

con = pymssql.connect(server='LAPTOP-DUHL1TTU\YXHLHM',  # 服务器名或本地IP
                      user='sa',  # 账户
                      password='*****',  # 自己设置的密码
                      database='yxhlhm',  # 你要连接的数据库名
                      )

cur = con.cursor()
sql = '''
create table G
(
ID int null,
片名 varchar(1000) null,
评分 varchar(1000) null,
评分人数 varchar(1000) null,
影片详情页 varchar(1000) null,
)
'''

cur.execute(sql)
con.commit()
con.close()

• execute参数问题

如图：

解决：给定为元组

• 提示SQL语法错误

如图：还有其他的报错，这个最主要

• 全国疫情数据获取之SQL表

网址：新冠肺炎疫情最新动态，实时更新 - 腾讯网-QQ.COM  ，当然，分析网址后，找到了它动态加载的接口：

https://api.inews.qq.com/newsqa/v1/query/inner/publish/modules/list?modules=statisGradeCityDetail,diseaseh5Shelf

直接在浏览器中访问它：如下图

 对于此，就可以自己代码实现数据获取了。

"""
2022年
 
CSDN:抄代码抄错的小牛马
"""

import requests

headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.87 Safari/537.36',
}

url = 'https://api.inews.qq.com/newsqa/v1/query/inner/publish/modules/list?modules=statisGradeCityDetail,diseaseh5Shelf'
resp = requests.get(url=url, headers=headers).json()
statisGradeCityDetail = resp['data']['statisGradeCityDetail']
for i in statisGradeCityDetail:
    city = i['city']  # 城市
    new_add = i['confirmAdd']  # 新增
    date = i['date']  # 日期
    province = i['province']  # 省市
    grade = i['grade']  # 区域风险
    nowConfirm = i['nowConfirm']  # 现有确诊
    heal = i['heal']  # 治愈
    confirm = i['confirm']  # 累计确诊
    dead = i['dead']  # 死亡

    # print(city, new_add, date)

打印一些数据查看：

 数据正常获取了，就要想办法保存嘛。因为刚学 SQL server，那就试试保存在SQL中。

 直接上代码：

"""
2022年

CSDN:抄代码抄错的小牛马
"""

import pymssql
import requests


def get_data():
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.87 Safari/537.36',
    }

    url = 'https://api.inews.qq.com/newsqa/v1/query/inner/publish/modules/list?modules=statisGradeCityDetail,diseaseh5Shelf'
    resp = requests.get(url=url, headers=headers).json()

    statisGradeCityDetail = resp['data']['statisGradeCityDetail']

    city = [city['city'] for city in statisGradeCityDetail]  # 城市
    new_add = [new_add['confirmAdd'] for new_add in statisGradeCityDetail]  # 新镇
    date = [date['date'] for date in statisGradeCityDetail]  # 日期
    nowConfirm = [nowConfirm['nowConfirm'] for nowConfirm in statisGradeCityDetail]  # 现有确诊
    heal = [heal['heal'] for heal in statisGradeCityDetail]  # 治愈
    confirm = [confirm['confirm'] for confirm in statisGradeCityDetail]  # 累计确诊
    dead = [dead['dead'] for dead in statisGradeCityDetail]  # 病逝

    # 调用函数
    to_sql(new_add, city, date, nowConfirm, heal, confirm, dead)


def to_sql(new_add, city, date, nowConfirm, heal, confirm, dead):
    con = pymssql.connect(server='LAPTOP-DUHL1TTU\YXHLHM',
                          user='sa',
                          password='****',  
                          database='yxhlhm',
                          )

    cur = con.cursor()

    for i in range(0, len(city)):
        # 注意参数对应
        sql = "insert into A(ID,newadd,city,date,nowConfirm,heal,confirm,dead) VALUES (%d,%s,%s,%s,%s,%s,%s,%s)"
        # execute，仅接收两个位置
        cur.execute(sql, ((i + 1), new_add[i], city[i], date[i], nowConfirm[i], heal[i], confirm[i], dead[i]))

    con.commit()
    con.close()


def main():
    get_data()


if __name__ == '__main__':
    main()

结果查看：

 关键的SQL语句：

    for i in range(0, len(city)):
        # 注意参数对应
        sql = "insert into A(ID,newadd,city,date,nowConfirm,heal,confirm,dead) VALUES (%d,%s,%s,%s,%s,%s,%s,%s)"
        # execute，仅接收两个位置
        cur.execute(sql, ((i + 1), new_add[i], city[i], date[i], nowConfirm[i], heal[i], confirm[i], dead[i]))

Come on, small cow horse，拜~~~~