Python-MySQL : How to insert multiple data into MySQL database?

Question

当我运行该脚本时，它不会产生任何错误，但它只将单个项目插入数据库。如果我执行 print(imageUrl)，则超过 12 个项目。

    from aliexpress_api_client import AliExpress
from datetime import date, datetime, timedelta
import mysql.connector

cnx = mysql.connector.connect(user='root', password='Kradz579032!!',
                              host='127.0.0.1',
                              database='aliexpressapidb')
cursor = cnx.cursor()

add_data = ("INSERT INTO producttable "
               "(productId, productTitle, salePrice, originalPrice, imageUrl) "
               "VALUES (%s, %s, %s, %s, %s)")

aliexpress = AliExpress('9420', 'bazaarmaya')
data = aliexpress.get_product_list(['productId', 'productTitle', 'salePrice', 'originalPrice', 'imageUrl'], 'pillow')
for product in data['products']:
    productId = product['productId']
    productTitle = product['productTitle']
    salePrice = product['salePrice']
    originalPrice = product['originalPrice']
    imageUrl = product['imageUrl']


data_insert = (productId, productTitle, salePrice, originalPrice, imageUrl)

cursor.execute(add_data, data_insert)





cnx.commit()

cursor.close()
cnx.close()

Answer 1

您并未执行 forEach 循环的每次迭代。

尝试将 data_insert = (productId, ProductTitle, salePrice, OriginalPrice, imageUrl) 和 cursor.execute(add_data, data_insert) 移动到循环内，如下所示:

from aliexpress_api_client import AliExpress
from datetime import date, datetime, timedelta
import mysql.connector

cnx = mysql.connector.connect(user='root', password='Kradz579032!!',
                              host='127.0.0.1',
                              database='aliexpressapidb')
cursor = cnx.cursor()

add_data = ("INSERT INTO producttable "
               "(productId, productTitle, salePrice, originalPrice, imageUrl) "
               "VALUES (%s, %s, %s, %s, %s)")

aliexpress = AliExpress('9420', 'bazaarmaya')
data = aliexpress.get_product_list(['productId', 'productTitle', 'salePrice', 'originalPrice', 'imageUrl'], 'pillow')
for product in data['products']:
    productId = product['productId']
    productTitle = product['productTitle']
    salePrice = product['salePrice']
    originalPrice = product['originalPrice']
    imageUrl = product['imageUrl']
    data_insert = (productId, productTitle, salePrice, originalPrice, imageUrl)
    cursor.execute(add_data, data_insert)




cnx.commit()

cursor.close()
cnx.close()

这应该可以解决您的问题。