python - 插入错误 Python MySQL 连接器：执行操作失败

Question

我正在使用 products.json 页面抓取 shopify 商店。尝试使用 Python 连接器将抓取的产品插入我的 MySQL 数据库，但遇到以下错误：

出了点问题：执行操作失败；b' Name'

代码如下：

import json
import pandas as pd
import mysql.connector
import ScraperConfig as conf

class myScraper():

    def __init__(self, baseurl):
        self.baseurl = baseurl

    def downloadjson(self, page):
        r = requests.get(self.baseurl + f'products.json?limit=250&page={page}', timeout=5)
        if r.status_code != 200:
            print('Bad status code', r.status_code)
        if len(r.json()['products']) > 0:
            data = r.json()['products']
            return data
        else:
            return

    def parsejson(self, jsondata):
        products = []

        for prod in jsondata:
            vendor = prod['vendor']
            name = prod['title']
            handle = prod['handle']
            createdDateTime = prod['created_at']
            description = prod['body_html']
            productType = prod['product_type']
            for images in prod['images']:
                vendorProductId = images['product_id']
                try:
                    imageURL = images['src']
                except:
                    imageURL = 'None'
            for variant in prod['variants']:
                item = {
                    'name': name,
                    'handle': handle,
                    'description': description,
                    'productVariantId': variant['id'],
                    'createdDateTime': createdDateTime,
                    'productType': productType,
                    'vendorProductId': vendorProductId,
                    'imageURL': imageURL,
                    'price': variant['price'],
                    'salePrice': variant['compare_at_price'],
                    'available': variant['available'],
                    'updatedDateTime': variant['updated_at'],
                    'vendor': vendor
                }
                products.append(item)
        return products
        
def main():
    scrape = Scraper('https://www.someshopifysite.com/')
    results = []
    for page in range(1,2):
        data = scrape.downloadjson(page)
        print('Getting page: ', page)
        try:
            results.append(scrape.parsejson(data))
        except:
            print(f'Completed, total pages = {page - 1}')
            break
    return results

if __name__ == '__main__':
    db = mysql.connector.connect(
        user=conf.user,
        host=conf.host,
        passwd=conf.passwd,
        database=conf.database)
    cursor = db.cursor()
    products = main()
    totals = [item for i in products for item in i]
    for p in totals:
        sql = """INSERT INTO `table` (`Name`, `Handle`, `Descritpion`, `VariantId`, `CreatedDateTime`, `ProductType`, `VendorProductId`, `ImageURL`, `Price`, `SalePrice`, `Available`, `UpdatedDateTime`, `Vendor`)          
        VALUES (%(`Name`)s, %(`Handle`)s, %(`Descritpion`)s, %(`VariantId`)s, %(`CreatedDateTime`)s, %(`ProductType`)s, %(`VendorProductId`)s, %(`ImageURL`)s, %(`Price`)s, %(`SalePrice`)s, %(`Available`)s, %(`UpdatedDateTime`)s, %(`Vendor`)s)"""
            
        try:
            cursor.executemany(sql, totals)
            
            print('Committed to DB')
        except mysql.connector.Error as err:
                    print("Something went wrong: {}".format(err))
        db.commit() ```

标签： pythonmysqlmysql-pythonmysql-connector

Answer 1

从查询的以下和所有类似部分中删除反引号：

%(`Name`)s

一般来说，除了引用映射到关键字的列名外，我会删除反引号。