sqlalchemy.exc.IntegrityError - mySql - Error with primary key in auto-increment and storage in database

Question

I am developing a Flask-sqlalchemy application and using mySql database. I have a form from where I receive data to be stored in database table named "intent". The table has 2 columns: intent_id & intent_name.

Here is code for my SqlAlchemy Model:

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Intent(db.Model):
  __tablename__ = 'intent'
  intent_id = db.Column('intent_id', db.Integer, primary_key=True)
  intent_name = db.Column(db.String(250))

Here is code for my Flask Routes file:

from flask import Flask, render_template, request, session, redirect, url_for
from models import db, Intent, Sentence

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:@localhost/test'

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db.init_app(app)


@app.route("/", methods = ['POST', 'GET'])
def index():
    if request.method == 'POST':
        intentobj = Intent(intent_name = request.form['intent'])

        db.session.add(intentobj)
        db.session.commit()

        return render_template("index.html", intent_data = Intent.query.all())
    elif request.method == 'GET':
        return render_template("index.html", intent_data = Intent.query.all())

I am getting this error :

sqlalchemy.exc.IntegrityError: (_mysql_exceptions.IntegrityError) (1364, "Field 'intent_id' doesn't have a default value") [SQL: 'INSERT INTO intent (intent_name) VALUES (%s)'] [parameters: ('def',)]

When I create an object of my Database Table's Class, I only store intent_name in it (as given above). I expect the intent_id to auto-increment and store in database as it's a primary key but it isn't working that way, I guess.

Note that I created the table using phpmyadmin and not through python.

Please help me remove this error. Thanks.

score 0 · Accepted Answer · edited May 04 '18 at 22:11

0

I don't think mysql automatically incrrements a primary key. Add autoincrement=True to the column statement

intent_id = db.Column('intent_id', db.Integer, primary_key=True, autoincrement=True)

Or, since you are generating the code with sql directly, create the table with

intent_id int NOT NULL PRIMARY_KEY AUTO_INCREMENT

Update The problem was with the database table; the primary key was not set to auto-increment. So, trying this command actually worked:

    ALTER TABLE document MODIFY COLUMN document_id INT auto_increment

edited May 04 '18 at 22:11

Khan

27
1
8

answered Apr 22 '18 at 19:33

ilmarinen

4,557
3
16
12

Thanks. I have tried this before. Here is the link which explains why it doesn't work: http://docs.sqlalchemy.org/en/latest/dialects/mysql.html#auto-increment-behavior Please let me know if you have any other clue – Khan Apr 22 '18 at 19:38
Ah, I wasn't aware of that behaviour in sqlalchemy. However, I don't think it matters in your case since the table was created outside of sqlalchemy. There's a related stackoverflow post here which could help you altering your existing table (or just drop it and recreate it if you are on a dev db) https://stackoverflow.com/questions/2169080/mysql-alter-a-column-to-be-auto-increment – ilmarinen Apr 22 '18 at 19:44
Hurrah! It worked. I ran the sql query you provided in link in phpmyadmin. Thanks a lot! :) I wonder how this can be updated in your answer given above so it can be helpful for other users as well. Thanks again. – Khan Apr 22 '18 at 19:51

sqlalchemy.exc.IntegrityError - mySql - Error with primary key in auto-increment and storage in database

1 Answers1