天猫拿到的数据存入到sqlite中

import re
import sqlite3
import requests
import json
import os

path = './bra.sqlite'

if os.path.exists(path):
    os.remove(path)

create_sql = '''
    create table sales(
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    color TEXT NOT NULL ,
    size TEXT NOT NULL ,
    source TEXT NOT NULL ,
    comment TEXT NOT NULL ,
    data  TEXT NOT NULL
    );
'''

##如果没有数据库就创建,如果有就连接数据库
conn = sqlite3.connect(path)

# 执行sql语句   """ Return a cursor for the connection. """
cursor = conn.cursor()

# 执行建表动作，   execute： 增加，删除，修改，提交事务    """ Executes a SQL statement. """
cursor.execute(create_sql)

conn.commit() 


# 某一个商品的，某一页的数据
def get_rateDetail(itemId, currentPage):
    try:
        url = "https://rate.tmall.com/list_detail_rate.htm?itemId=" + str(
            itemId) + "&spuId=687158212&sellerId=2917184910&order=3&currentPage=" + str(
            currentPage) + "&append=0&content=1&tagId=&posi=&picture=&ua=098%23E1hvHvvLvxZvUpCkvvvvvjiPPsdygjrbRLc9zjnEPmPh1jnjPLqUgj3nRFqp6jnmROhCvCB476czVr147DdE2KNGTDuM7K2No86CvvDvpLGp5pCvbagtvpvhphvvvUhCvCn37ejSDY147Du8jrYUAwFRSG3qRphvChCvvvvCvpvVphhvvvvvKphv8hCvvvvvvhCvphvZ7pvvp%2BnvpCBXvvC2p6CvHHyvvh89phvZ7pvvpumEvpvVpyUUCC%2BwuphvmhCvCEcrhGO9mphvLhUTN9mFdeZvVAdhaB4AVAi1bPoxdX3Qb7gnIOZtIoYbE7LBIb8rwZ9anbmxdX3Qbc6OfwoOdexBfvDr08TJEct1pc7QrEtlp7Q4S4ZPvpvhvv2MMTwCvvBvpvpZ3QhvChCCvvmrvpvBohyf2jOvphep05lB%2B2hmOs8rvpvEphVk28pvpjn5dphvHhrE6C2bvhv26i3YmLeQbyICaajECQhvCli4zYMwEfGtvpvhphvvv2yCvvBvpvvv&needFold=0&_ksTS=1536980088890_1373&callback=jsonp1374"

        # 请求
        response = requests.get(url)

        # JSON类型的str数据
        data = response.text.replace("jsonp1374(",   "").replace(")",    "")

        data_dict = json.loads(data, encoding="utf-8")

        rateDetail = data_dict["rateDetail"]

        # 总页数
        total_page = rateDetail["paginator"]["lastPage"]

        print("itemId===", itemId, "currentPage==", currentPage, "总页数==", total_page)

        # 得到当前评论也的胸罩的颜色，尺寸   
        for item in rateDetail["rateList"]:
            try:
                print("-----------------------------------")
                auctionSku = item["auctionSku"]
                auctionSku = re.split(r'[:;]', auctionSku)
                color = auctionSku[1]
                size = auctionSku[3]
                content = item["rateContent"]
                # 评论的时间
                rate_date = item["rateDate"]


                # 插入数据到表中
                insert_sql = "INSERT INTO sales(color,size,source,comment,data) VALUES('%s','%s','%s','%s','%s')" % (
                    color, size, "天猫", content, rate_date)
                print("insert_sql==", insert_sql)
                # 执行sql    """ Executes a SQL statement. """
                cursor.execute(insert_sql)
                # 提交事务      """ Commit the current transaction. """
                conn.commit()


            except Exception as e:
                print("出错了==", e, "item==", item)

    except Exception as e:
        print("出错了===", e, "url===", url)


if __name__ == "__main__":
    for page in range(1, 100):
        get_rateDetail(547746378793, page)

建数据库，表

path = './bra.sqlite'

if os.path.exists(path):
    os.remove(path)

create_sql = '''
    create table sales(
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    color TEXT NOT NULL ,
    size TEXT NOT NULL ,
    source TEXT NOT NULL ,
    comment TEXT NOT NULL ,
    data  TEXT NOT NULL
    );
'''

##如果没有数据库就创建,如果有就连接数据库
conn = sqlite3.connect(path)

# 执行sql语句   """ Return a cursor for the connection. """
cursor = conn.cursor()

# 执行建表动作，   execute： 增加，删除，修改，提交事务    """ Executes a SQL statement. """
cursor.execute(create_sql)

conn.commit() 

数据入表

                # 插入数据到表中
                insert_sql = "INSERT INTO sales(color,size,source,comment,data) VALUES('%s','%s','%s','%s','%s')" % (
                    color, size, "天猫", content, rate_date)
                print("insert_sql==", insert_sql)
                # 执行sql    """ Executes a SQL statement. """
                cursor.execute(insert_sql)
                # 提交事务      """ Commit the current transaction. """
                conn.commit()