MySQL Insert query does not insert, but does not throw exception

Question

I have an issue where a python implemented sql query executes without raising any error but does not actually insert into db. I've tried reviewing other similar errors reported on SO but without any solution.

This is the python script connecting to db through pymysql:

def update_saturated_content(content_selection):
    conn = open_connection()
    conn.autocommit(True)
    with conn.cursor() as cursor:
        for content in content_selection:
            text = [content[0]["text"]]
            row = tuple([content[0]["list"], content[0]["link"], content[0]["promo"], content[1], float(0.2),
                         datetime.strptime(datetime.now().isoformat(), '%Y-%m-%dT%H:%M:%S.%f'), 1], )
            insert_query = ('''INSERT INTO creative_tester (''' +
                            '''list, ''' +
                            '''link, ''' +
                            '''promo, ''' +
                            '''text, ''' +
                            '''weighting, ''' +
                            '''date_added, ''' +
                            '''in_use) ''' +
                            '''VALUES (%s, %s, %s, %s, %s, %s, %s); ''')
            cursor.execute = (insert_query, row)
            conn.commit()
        cursor.close()
        conn.close()
        return

Here is a sample input for the row variable above:

row = ('My list', 'https://mylink.com', 'My promo', "My really really really long text", 0.2, datetime.datetime(2024, 1, 7, 8, 4, 53, 429153), 1)

When I run the code, it completes successfully but the table does not update. If I run the exact same query via console/workebench in Google Cloud Platform, the query completes successfully and inserts the row correctly.

The cursor object during debugging shows rowcount = -1.

I've tried re-implementing with row as a dict, also no luck. This is the result of SHOW CREATE TABLE creative_tester;

| Table           | Create Table                                                                                                                                                                                                                                                                                                                       |
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| creative_tester | CREATE TABLE `creative_tester` (
  `list` varchar(255) DEFAULT NULL,
  `link` varchar(255) DEFAULT NULL,
  `promo` varchar(255) DEFAULT NULL,
  `text` varchar(1027) DEFAULT NULL,
  `weighting` float DEFAULT NULL,
  `date_added` datetime DEFAULT NULL,
  `in_use` tinyint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |

I've tried many, many variations of trying to ensure the query string is written correctly, reformatting the datetime, using tuples vs using dictionaries, including the autocommit option, removing it, etc., without luck. One other note -- when I connect to creative_tester table via Pycharm cloud-sql-proxy and try to remove a row from this table, I get this error;

Unexpected update count received (Actual: 0, Expected: 1). All changes will be rolled back. com.intellij.database.console.JdbcEngineUtils$UnsafeUpdateRolledBackException: Unexpected update count received (Actual: 0, Expected: 1). All changes will be rolled back. in JdbcEngine$UpdateOperation$UnexpectedUpdatesGuard.reportChangesWillBeRolledBack(JdbcEngine.java:1927) 

If instead, I go to the workbench and query TRUNCATE TABLE creative_tester; then the table will clear.

Appreciate any help.

EDIT 1:

def open_connection():
    unix_socket = '/cloudsql/{}'.format(db_connection_name)
    try:
        if os.environ.get('FLASK_ENV') == 'development':
            conn = pymysql.connect(user=db_user, password=db_password, host="127.0.0.1", port=3306, db=db_name,
                                   cursorclass=pymysql.cursors.DictCursor)
        elif os.environ.get('GAE_ENV') == 'standard':
            conn = pymysql.connect(user=db_user, password=db_password, unix_socket=unix_socket, db=db_name,
                                   cursorclass=pymysql.cursors.DictCursor)
    except pymysql.MySQLError as e:
        print(e)

    return conn

EDIT 2 (final code):

def update_saturated_content(content_selection):
    conn = open_connection()
    conn.begin()
    conn.autocommit(True)
    creative_update = []
    with conn.cursor() as cursor:
        for content in content_selection:
            text = [content[0]["text"]]
            creative_update.append(tuple([content[0]["list"], content[0]["link"], content[0]["promo"], content[1], float(0.2),
                         datetime.now().isoformat(), 1], ))
            query = ('UPDATE creative_tester '
                     'SET in_use = 0 WHERE text LIKE (%s) '
                     'AND in_use = 1; ')
            cursor.execute(query, tuple(text, ))
        insert_query = ('INSERT INTO creative_tester ('
                        'list, '
                        'link, '
                        'promo, '
                        'text, '
                        'weighting, '
                        'date_added, '
                        'in_use) '
                        'VALUES (%s, %s, %s, %s, %s, %s, %s)')
        cursor.executemany(insert_query, creative_update)
        conn.commit()
        cursor.close()
        conn.close()
        return