How do you save api calls into mysql database?

python
mysql

#1

I’m trying to save an API call into mysql database but something wrong with my code not sure what it is.

from aliexpress_api_client import AliExpress
import pymysql.cursors

aliexpress = AliExpress('9420', 'bazaarmaya')

data = aliexpress.get_product_list(['productId', 'productTitle', 'salePrice', 'originalPrice'], 'drones')

#print(data)

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:
            print('%s %s %s %s' % (product['productId'], product['productTitle'], product['salePrice'], product['originalPrice']))

            cursor.execute(sql_template, {product['productId'], product['productTitle'],
                                          product['salePrice'], product['originalPrice']})


        connection.commit()

finally:
    connection.close()

Error:
/Users/reezalaq/PycharmProjects/Aliexpress/venv/bin/python /Users/reezalaq/Downloads/newali/script.py
Traceback (most recent call last):
File “/Users/reezalaq/Downloads/newali/script.py”, line 28, in
print(’%s %s %s %s’ % (product[‘productId’], product[‘productTitle’], product[‘salePrice’], product[‘originalPrice’]))
TypeError: string indices must be integers

Process finished with exit code 1

Strings and Console Output


#2

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.