This is not wotking at the moment and with the insert of the month and year I need to get the best 10 product sales of a certain month. How can i improve this and work on it?This is an API for a supermarket.
@app.route('/report/month_product/<year-month>', methods = ['GET'])
is {} or <>??
def product_results(year, month):
logger.info('GET /report/month_product/')
conn = db_connection()
c = conn.cursor()
try:
c.execute('''
SELECT count(product_id), PRODUCT_ID, SUM(product_purchase.price) as "valor total"
FROM product_purchase, purchase
WHERE product_purchase.purchase_id = purchase.id
AND data between '%s-%s-01' AND date '%s-%s-01' + interval '30 days'
GROUP by PRODUCT_ID
ORDER BY COUNT(PRODUCT_ID) DESC
LIMIT 10;;''')
rows = c.fetchall()
row = rows[0]
logger.debug('GET /report/month_product/<year-month> - parse')
logger.debug(row)
content = {'count': int(row[0]), 'product_id': row[1], 'valor_total': row[2]}
response = {'status': StatusCodes['success'], 'results': content}
except (Exception, psycopg2.DatabaseError) as error:
logger.error(f'GET /report/month_product/<year-month> - error: {error}')
response = {'status': StatusCodes['internal_error'], 'results': str(error)}
finally:
if conn is not None:
conn.close()
return flask.jsonify(response)
I'm new on stack but I'm glad if you can help me, I'm learning how to build api's and databases with pSQL and python