[SOLVED] How to INSERT to a table with AUTO

How to INSERT to a table with AUTO_INCREMENT?

I'm trying to insert new users to a table in MySQL when they register. I am using a FlaskApp on PythonAnywhere.

Here is my query:

INSERT INTO user_profile (email, user_name, first_foo) VALUES (%s, %s, 0);

This is run from my flask_app code:

def connect_db(query, params):

    db_connection= MySQLdb.connect("<username>.mysql.eu.pythonanywhere-services.com","<username>","<password","<db_name>", cursorclass=MySQLdb.cursors.DictCursor)

    cursor=db_connection.cursor()
    cursor.execute(query, params)
    result = cursor.fetchone()

    return result

connect_db(query, (email, username,))

Here is my table structure:

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| user_id      | int         | NO   | PRI | NULL    | auto_increment |
| email        | varchar(50) | YES  | UNI | NULL    |                |
| user_name    | varchar(15) | YES  | UNI | NULL    |                |
| first_foo    | tinyint(1)  | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

Unfortunately, I keep getting:

MySQLdb._exceptions.OperationalError: (1048, "Column 'user_id' cannot be null")

I have tried several queries including:

INSERT INTO user_profile (user_id, email, user_name, first_foo) VALUES (NULL, %s, %s, 0);
INSERT INTO user_profile (user_id, email, user_name, first_foo) VALUES (DEFAULT, %s, %s, 0);
INSERT INTO user_profile (user_id, email, user_name, first_foo) VALUES (0, %s, %s, 0);

but all return the same error.

If I run the first query in the MySQL console on Python Anywhere, the query is successful.

Thanks in advance for any help.

Solution

From the OP's comment this worked, after each transaction such as INSERT, UPDATE, DELETE queries, adding commit() was pivotal after execute().

cursor = db_connection.cursor()
cursor.execute(query, params)
db_connection.commit()  #  <- add this