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.
6
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
unfortunately my English is not very good but.
If you correctly declared the column auto incrementable?
This could be solved by specifying which value the increment would start at:
ALTER TABLE YourTableName MODIFY user_id INT NOT NULL AUTO_INCREMENT;
1