Why mySQL queries INSERT INTO does not show up with SELECT

Question

I am creating and filling a database with mySQL and python. I use MySQL Workbench to visualize my database in real time. One of my function is meant to add a new row to a table :

def addValue(name,time,active):
    print ("Adding a value : " + name + " at " + time + " is active ? " + str(active))
    try:
        mydb = mysql.connector.connect(
              host="localhost",
              user="username",
              password="password",
              database="alarm"
                )

        cursor = mydb.cursor()
        cursor.execute("INSERT INTO Alarm (Name, Time, Active) VALUES (%s,%s,%s)",(name,time,active))
        print ("1 Alarm added to the Table")

    except mysql.connector.Error as error:
        print("Failed to add data to Table Alarm : {}".format(error))

    finally:
        if (mydb.is_connected()):
            cursor.close()
            mydb.close()
            print("Connection closed\n")

This function does not output any error, but the new row does not show in my Table (even with hardcoded values instead of variables). The reason I know this query is taken into account is because when I check the table with MySQL Workbench, and I manually add a row with the same query, the primary key increases automatically.

On an other hand, my function which allows me to display the rows of this table does not work when I add the data with the previous function, while it works just fine when the row are added manually with MySQL Workbench.

Answer 1

The PEP 249 (Python Database API Specification v2.0) specifies that auto-commit must be initially be off (https://www.python.org/dev/peps/pep-0249/#commit).

But you can turn it on in the connect() method.

mydb = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="alarm",
    autocommit=True)