Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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.

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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()  #  <- by adding it work
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search