How to separate SQL Select operation from Category value (Python & MySQL)-CodePudding

I am currently learning how to work with Python and MySQL, so apologies if my question is a basic one. I just haven't been able to find specific feedback for this question.

Regarding the following insert() function below, I like that I can compartmentalize the operation and the values.

def insert(item, price, category):
    sql = "INSERT INTO Exp1 (item, price, category, date) VALUES (%s, %s, %s, %s)"
    val = (item, price, category, datetime.now())
    mycursor.execute(sql, val)

Is there a way to do the same for this get category function? As in I'd be able to pass a specific category to the function, store it in val and execute it using both sql and val like before?

# Get a given category from the database
def get_category():
    sql = "SELECT * FROM Exp1 WHERE category = 'Groceries' ORDER BY date DESC"
    mycursor.execute(sql)
    for x in mycursor:
        print(x)

CodePudding user response：

The mysql docs show this example:

select_stmt = "SELECT * FROM employees WHERE emp_no = %(emp_no)s"
cursor.execute(select_stmt, { 'emp_no': 2 })

Extending to your case, something like this should work:

def get_category(category):
    sql = "SELECT * FROM Exp1 WHERE category = %(category)s ORDER BY date DESC"
    mycursor.execute(sql, {'category': category})
    for x in mycursor:
        print(x)