1

I have this code which currently lets me update a school's data in mySQL database, if the id already exists. However, what I am also trying to achieve, is that if the ID doesn't exist in the DB, it inserts those values. Here is the code I've got so far:

from flask import Flask 
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://username:password@localhost/dbName'
db = SQLAlchemy(app)

class Example(db.Model):
    __tablename__ = 'schools'
    _id = db.Column('_id', db.Unicode, primary_key=True)
    establishmentNumber = db.Column('establishmentNumber', db.Unicode)

    def __init__(self, id, establishmentNumber):
        self.id = id
        self.establishmentNumber = establishmentNumber

update_this = Example.query.filter_by(_id=school._id.iloc[0]).first()

update_this.establishmentNumber = 'bob'
db.session.commit()

examples = Example.query.all()
examples

for ex in examples:
    print (ex.establishmentNumber)

This will update correctly, however, when I try to add a completely new school to the db, with this code:

new_ex = Example('5c12345scl', 'fifth')
db.session.add(new_ex)
db.session.commit()

I get this error:

IntegrityError: (MySQLdb._exceptions.IntegrityError) (1364, "Field '_id' doesn't have a default value")
[SQL: INSERT INTO schools (`establishmentNumber`) VALUES (%s)]
[parameters: ('fifth',)]
(Background on this error at: http://sqlalche.me/e/gkpj)

Does anyone know how to get rid of this error?

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' UPDATE '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

The above is working now. I am now trying to combine the add and update functions so that it checks if the id exists in the table. If not, it inserts a new row. If it does, then it just updates the values. Below is my code so far, thanks to @James Summers:

class Example(db.Model):
    __tablename__ = 'schools'
    _id = db.Column('_id', db.Unicode, primary_key=True)
    establishmentNumber = db.Column('establishmentNumber', db.Unicode)
    laCode = db.Column('laCode', db.Unicode)
    urn = db.Column('urn', db.Unicode)
    name = db.Column('name', db.Unicode)
    phaseOfEducation = db.Column('phaseOfEducation', db.Unicode)
    wondeID = db.Column('wondeID', db.Unicode)

    def __init__(self, _id, establishmentNumber, laCode, urn, name, phaseOfEducation, wondeID):
        self._id = _id
        self.establishmentNumber = establishmentNumber
        self.laCode = laCode
        self.urn = urn
        self.name = name
        self.phaseOfEducation = phaseOfEducation
        self.wondeID = wondeID

        def add_or_update(self, _id, establishmentNumber, laCode, urn, name, phaseOfEducation, wondeID):
            update_this = Example.query.filter_by(_id=school._id.iloc[0]).first()

            if not update_this:
                new_ex = Example(school._id.iloc[0], school.establishmentNumber.iloc[0], school.laCode.iloc[0], school.urn.iloc[0], school.name.iloc[0], school.phaseOfEducation.iloc[0], school.wondeID.iloc[0])
                db.session.add(new_ex)
                db.session.commit()

            else:
                update_this.establishmentNumber = 'hello'
                db.session.commit()

            return new_ex

It is not producing any errors, but it's not adding or updating either. Can anyone see what I've done wrong?

Improve this question
3
  • Does anyone know how to get rid of this error? Simply alter the table and add default value (for example, zero) to the problematic field. Commented Feb 20, 2020 at 9:46
  • Thanks for the quick response @Akina. I'm sorry but I don't quite understand. I want the _id field to be set to '5c12345scl' and the establishmentNumber to 'fifth'. So, I don't want a default value. Commented Feb 20, 2020 at 9:53
  • You have asked "how to get rid of this error?". So you assume that everything another is correct. I give you the solution. Really - error message tells that your program tries to execute INSERT INTO schools (`establishmentNumber`) VALUES (%s) and does not set _id in this query - which means that you have some logical or algorythmical error in your code. Search for it (I cannot help you in solving this task, I don't know flask except the fact that it exists). Commented Feb 20, 2020 at 9:59

2 Answers 2

Reset to default
1

Thanks to @Akina's comments. I realised the problem was with this function's code:

def __init__(self, id, establishmentNumber):
    self.id = id
    self.establishmentNumber = establishmentNumber

I had written the id without the underscore, so this is the fixed version:

def __init__(self, _id, establishmentNumber):
    self._id = _id
    self.establishmentNumber = establishmentNumber
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

The error is perhaps with your constructor.

You have a class member that is: _id = db.Column('_id', db.Unicode, primary_key=True)

but in your constructor you're setting self.id = id

A fix for this problem would be to either update your model for the code to look like this:

class Example(db.Model):
    __tablename__ = 'schools'
    _id = db.Column('_id', db.Unicode, primary_key=True)
    establishmentNumber = db.Column('establishmentNumber', db.Unicode)

    def __init__(self, id, establishmentNumber):
        self._id = id
        self.establishmentNumber = establishmentNumber

or what someone in the comments suggested to do by adding a default value to your column. However, I would not suggest adding a default value to your primary key.

Another possible solution for this problem is to create an add_or_update method to your class, which is the better solution for the long run. You do it by adding a class method which gets a result by id, if it doesn't exist, your code will create one or update the existing retrieved result. This is how you would implement the following code:

class Example(db.Model):
    __tablename__ = 'schools'

    _id = db.Column('_id', db.Unicode, primary_key=True)
    establishmentNumber = db.Column('establishmentNumber', db.Unicode)

    def __init__(self, id, establishmentNumber):
        self._id = id
        self.establishmentNumber = establishmentNumber

    @classmethod
    def add_or_update(cls, example_id, establishmentNumber):
        entity = cls.query.filter_by(id=example_id).first()

        if not entity:
          entity = cls(example_id, establishmentNumber)

        entity.establishmentNumber = establishmentNumber

        db.session.add(entity)
        db.session.commit()

        return entity

Good luck and cheers!

Improve this answer

6 Comments

Thanks so much @James Summers for the detailed response. I have tried to implement your add_or_update function, however, it doesn't seem to be working. Could you possibly have a look and see what I'm doing wrong? It isn't producing any errors, but just not adding or updating either. See updated question for my code. Thanks
I see that in your example of the code, you're not using the @classmethod decorator. That's why your add_or_update method does not work as intended. Because you're not using that decorator, your method requires an instance of itself before you can call the method. By using the classmethod decorator, you can call your function without having to first get the instance of the class and then using it. I can also see that in the example code that you have provided, that you have defined that method in your constructor. Move it out. The way to use the method is Example.add_or_update(1, 2)
Thanks @James Summers. it is working now. Just a quick question - how could I change the iloc[0]'s in the add_or_update function to look through all the id's available instead of just doing the first one?
I am not sure what the iloc array is as I have not yet seen the code for it. And I can't seem to find where you get the school object from. You could probably pass the school object to the add_or_update method and then loop the iloc array. for school in school.iloc: Example.add_or_update(school), something like that. Alternatively, you could loop the school iloc array in your add_or_update method.
Thanks @James Summers. Just realised I didn't need iloc. Just add in the row._id etc and it loops it for me
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.