Pass query correctly to the MySQL database

I am using Python 3.8.10.

I am trying to update categories in my MySQL database:

def update_categories(automaton, df, engine):
    logging.info("Starting category update process...")
    update_list = []
    start_time = time.time()

    # tqdm is used for showing progress bar
    for index, row in tqdm(df.iterrows(), total=df.shape[0]):
        matches = list(automaton.iter(row['prompt'].lower()))
        if matches:
            categories = json.loads(row['categories'])  # Convert string back to list
            for match in matches:
                _, (keyword, category) = match
                if category not in categories:
                    categories.append(category)  # Now you can append to the list
            update_list.append((json.dumps(row['categories']), row['id']))
            
    if update_list:
        try:
            with engine.connect() as conn:
                for data in update_list:
                    query = f"UPDATE prompts SET categories = '{data[0]}' WHERE id = {data[1]};"
                    conn.execute(query) # <--- HERE I GET THE ERROR
            logging.info("Updated categories successfully.")
        except SQLAlchemyError as e:
            logging.error(f"An error occurred: {e}")
            print(e)
            
    end_time = time.time()
    time_taken = end_time - start_time
    logging.info(f"Category update process completed. Time taken: {time_taken} seconds")

I get the following error:

ObjectNotExecutableError('Not an executable object: \'UPDATE prompts SET categories = \\\'"[\\\\"fashion\\\\", \\\\"Painting\\\\"]"\\\' WHERE id = 1;\'')

Executing this query on my database directly works:

UPDATE prompts SET categories = "[\"fashion\", \"Painting\"]" WHERE id = 1;

I need to pass the above query in my python code onto my database.

How can I correctly pass my values to update my database?

I think it's better to use parameterized queries instead of directly formatting the values into the query string. in this case I'll use the text() function from sqlalchemy to create a parameterized query.

something like this

from sqlalchemy import text

if update_list:
    try:
        with engine.connect() as conn:
            for data in update_list:
                query = text("UPDATE prompts SET categories = :categories WHERE id = :id;")
                conn.execute(query, categories=data[0], id=data[1])
        logging.info("Updated categories successfully.")
    except SQLAlchemyError as e:
        logging.error(f"An error occurred: {e}")
        print(e)