Why does SQLAlchemy return a new rownum but not insert my new row into MySQL?

Question

I am debugging a strange phenomenon rising from this bit of Python code:

def insert_book(name):
   with engine.connect() as connection:
        insert_statement = sqlalchemy.text(
            """
            INSERT INTO books (title_id) 
            SELECT 
                title_id
            FROM titles
            WHERE name = :name;
            """
        ).bindparams(name=name)

        result = connection.execute(insert_statement)
       
        return result.lastrowid

The MySQL database tables that I'm working with are structured like so:

CREATE TABLE `titles` (
  `title_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(36),
  PRIMARY KEY (`title_id`),
  UNIQUE KEY `name` (`name`)
);

CREATE TABLE `books` (
  `book_id` int NOT NULL AUTO_INCREMENT,
  `title_id` int DEFAULT NULL,
  PRIMARY KEY (`book_id`),
  KEY `client_id` (`title_id`),
  CONSTRAINT `titles_fk` FOREIGN KEY (`title_id`) REFERENCES `titles` (`title_id`)
);

Both tables have several rows already inserted to them.

When I run the Python code (with a name taken from the titles table) inside a Flask app and point SQLAlchemy to my MySQL DB, two things happen:

1. result.lastrowid contains a new auto-incremented int value.
2. A new row is not inserted into the table.

As I repeat this, the returned int value is incremented properly but no rows are created.

My question is: Why is this happening and how can I fix it?

Here are the main packages used:

• Python 3.9.13
• Flask==2.2.2
• PyMySQL==1.1.0
• SQLAlchemy==2.0.19

PLEASE: Kindly refrain from pointing in the direction of other, admittedly better, SQLAlchemy APIs. I would like to understand why this specific bit of code is misbehaving. Thank you.

UPDATE: I've examined the logs for my cloud instance of MySQL and I'm seeing errors such as these which correspond to failed inserts:

Aborted connection ... to db: '...' user: '...' host: '...' (Got an error reading communication packets)."

Answer 1

with engine.connect() as conn:
    conn.exec_driver_sql("INSERT INTO my_table …")
print("Context manager exited.")

will automatically roll back the changes because conn.commit() was not called. In the SQLAlchemy tutorial this is known as the "commit as you go" style of working.

By contrast,

with engine.begin() as conn:
    conn.exec_driver_sql("INSERT INTO my_table …")
print("Context manager exited.")

will automatically commit the changes when the context manager exits (unless an error has occurred). This is called the "begin once" style.