使用Pymysql存储数据触发NameError: productId未定义，求解决方案

Hey, let's fix this error you're hitting with your PyMySQL script. The issue is pretty straightforward—you're trying to use variables like productId that don't actually exist in your code scope.

错误原因分析 #

Looking at your loop, you correctly access product data from the product dictionary in your print() statement (using product['productId']), but when passing parameters to cursor.execute(), you referenced bare variable names instead of pulling values from the product dict. Python has no idea what productId is because you never defined it as a standalone variable.

修复后的完整代码 #

import pymysql.cursors

connection = pymysql.connect(host='localhost', 
                             user='root', 
                             password='Kradz579032!!', 
                             db='aliexpressapidb', 
                             charset='utf8mb4', 
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        sql_template ="""
        INSERT INTO producttable (productId, productTitle, salePrice, originalPrice )
        SELECT * FROM (SELECT %(productId)s, %(productTitle)s, %(salePrice)s, %(originalPrice)s) AS tmp
        WHERE NOT EXISTS (
            SELECT productId FROM producttable WHERE productId = %(productId)s
        ) LIMIT 1;
        """
        for product in data['products']:
            # Print statement remains correct as it uses the product dict
            print('%s %s %s %s' % ( 
                product['productId'], 
                product['productTitle'], 
                product['salePrice'], 
                product['originalPrice']
            ))
            # Fix: Pull values directly from the current product dictionary
            cursor.execute(sql_template, {
                "productId": product['productId'],
                "productTitle": product['productTitle'],
                "salePrice": product['salePrice'],
                "originalPrice": product['originalPrice']
            })
    # Optimized: Commit all inserts at once instead of per iteration
    connection.commit()
finally:
    connection.close()

关键修改说明 #

• 参数值来源修正: Changed productId to product['productId'] (and the same for other fields) in the execute() parameter dictionary. This pulls the actual value from the current product dict in your loop, which is where your data lives.
• 性能优化: Moved connection.commit() outside the loop. If you have a large number of products, committing once after all inserts is much more efficient than committing every single time. If you need immediate persistence for each entry, you can move it back inside the loop, but bulk commits are preferred for most cases.

额外注意事项 #

• Ensure every entry in data['products'] has all the required keys (productId, productTitle, etc.). If some entries might be missing keys, use product.get('productId', None) to set a default value and avoid KeyError.
• Double-check that the field names in your SQL template match exactly with the column names in your producttable (MySQL is case-insensitive by default, but consistency avoids unexpected issues).

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