如何从API拉取分页数据存入sqlite3并自动获取下一页

问题1：数据库写入失败修复 #

你使用executemany方法时参数格式错误：executemany要求传入的第二个参数是由多行数据组成的可迭代对象，每一行数据对应一个包含占位符所需值的序列。你当前只传入了单个商品的字段列表，不符合方法要求。
修改方案：遍历生成的prodlist，把每个字典的字段按SQL占位符的顺序提取为元组，组成新的列表传给executemany即可。

问题2：特殊标识分页循环逻辑实现 #

你可以用一个变量存储当前的分页标识after，每次请求完成后提取返回的next_is_after：

• 如果next_is_after有有效值，就用这个值构造新的请求端点，进入下一轮循环
• 如果next_is_after为空/None，直接终止循环

import requests
import sqlite3

# 初始化数据库
con = sqlite3.connect('takealot.db')
cur = con.cursor()
cur.execute('''CREATE TABLE IF NOT EXISTS products
                    (sku text PRIMARY KEY, title text, slug text, reviews integer, star_rating real, listing_price real, pretty_price real)''')

baseurl = 'https://api.takealot.com/rest/v-1-10-0/searches/products,filters,facets,sort_options,breadcrumbs,slots_audience,context,seo'
# 初始分页标识，首次请求可以用这个值，不需要初始参数的话可以设为空字符串
current_after = "WzExNDEuMTYsNTYwNTkzNTFd"
# 加请求头避免被接口拦截
headers = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36"
}

def main_request(baseurl, after_val, headers):
    endpoint = f"?after={after_val}" if after_val else ""
    res = requests.get(baseurl + endpoint, headers=headers)
    return res.json()

# 循环拉取所有页
while True:
    # 请求当前页数据
    daten = main_request(baseurl, current_after, headers)
    paging_info = daten['sections']['products']['paging']
    # 处理当前页数据
    prod_rows = []
    for data in daten['sections']['products']['results']:
        core_info = data['product_views']['core']
        buybox_info = data['product_views']['buybox_summary']
        prod_row = (
            core_info['id'],
            core_info['title'],
            core_info['slug'],
            core_info['reviews'],
            core_info['star_rating'],
            buybox_info['listing_price'],
            buybox_info['pretty_price']
        )
        prod_rows.append(prod_row)
    # 批量写入数据库
    cur.executemany("INSERT OR IGNORE INTO products VALUES (?, ?, ?, ?, ?, ?, ?)", prod_rows)
    con.commit()
    print(f"已写入{len(prod_rows)}条数据")
    # 判断是否有下一页
    next_is_after = paging_info.get('next_is_after')
    if not next_is_after:
        print("所有页拉取完成")
        break
    # 更新分页标识进入下一轮
    current_after = next_is_after

# 验证数据
for row in cur.execute('''SELECT * FROM products'''):
    print(row)

# 关闭数据库连接
cur.close()
con.close()

内容的提问来源于stack exchange，提问作者Jan