MySQL remote query works, but remote insert fails via Python

Question

I'm running Ubuntu 16.04 with MySQL. I've opened the MySQL server for remote connections, and my remote Python script can query my database, but all attempts to INSERT fail without any error log entry.

It also looks like my remote INSERTs are being seen, because my AUTO_INCREMENT ID increases without entries being made when I run the Python INSERT code.

Any insight is appreciated!

Simple table schema:

CREATE TABLE test (
    ID int NOT NULL AUTO_INCREMENT,
    x INT,
    PRIMARY KEY (ID)
);

This works directly on the server:

INSERT INTO test (x) VALUES (10);

This is the Python query that's working:

try:
    connection = db.Connection(host=HOST, port=PORT, user=USER, passwd=PASSWORD, db=DB)
    cursor = connection.cursor()
    print("Connected to Server")

    cursor.execute("SELECT * FROM test")
    result = cursor.fetchall()
    for item in result:
        print(item)

except Exception as e:
    print('exception connecting to server db: ' + str(e))

finally:
    print('closing connection...')
    connection.close()

And the Python INSERT that's not working:

try:
    connection = db.Connection(host=HOST, port=PORT, user=USER, passwd=PASSWORD, db=DB)
    cursor = connection.cursor()
    print("Connected to Server")

    cursor.execute("INSERT INTO test (x) VALUES (10);")

except Exception as e:
    print('exception connecting to server db: ' + str(e))

finally:
    print('closing connection...')
    connection.close()

Thanks

Answer 1

Add this line after the execute() call:

cursor.execute("INSERT INTO test (x) VALUES (10)")
connection.commit()

When making changes to the db, it is required that you commit your changes, no change(s) would take effect.