'SQLAlchemy DataError gives me 500 Internal Server Error

So I have built a Flask App which takes client feedback and sends it to a PostgreSQL database. The app runs. But when submitting the data, it gives me this DataError:

Traceback (most recent call last):
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1247, in _execute_context
    self.dialect.do_execute(
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\engine\default.py", line 590, in do_execute    
    cursor.execute(statement, parameters)
psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type integer: "Bouche à oreille"
LINE 1: ...1'::timestamp, 'Jonathan Test', 'Léon Di Caprio', 'Bouche à ...
                                                             ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\flask\app.py", line 2077, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\flask\app.py", line 1525, in full_dispatch_request        
    rv = self.handle_user_exception(e)
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\flask\app.py", line 1523, in full_dispatch_request        
    rv = self.dispatch_request()
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\flask\app.py", line 1509, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\app.py", line 66, in submit
    db.session.commit()
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\orm\scoping.py", line 162, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\orm\session.py", line 1036, in commit
    self.transaction.commit()
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\orm\session.py", line 503, in commit
    self._prepare_impl()
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\orm\session.py", line 482, in _prepare_impl    
    self.session.flush()
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\orm\session.py", line 2496, in flush
    self._flush(objects)
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\orm\session.py", line 2636, in _flush
    with util.safe_reraise():
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\util\langhelpers.py", line 68, in __exit__      
    compat.raise_(
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
    raise exception
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\orm\session.py", line 2597, in _flush
    flush_context.execute()
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 422, in execute        
    rec.execute(self)
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 586, in execute        
    persistence.save_obj(
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\orm\persistence.py", line 239, in save_obj      
    _emit_insert_statements(
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\orm\persistence.py", line 1135, in _emit_insert_statements
    result = cached_connections[connection].execute(
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\engine\base.py", line 984, in execute
    return meth(self, multiparams, params)
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\sql\elements.py", line 293, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1097, in _execute_clauseelement
    ret = self._execute_context(
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1287, in _execute_context 
    self._handle_dbapi_exception(
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1481, in _handle_dbapi_exception
    util.raise_(
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
    raise exception
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1247, in _execute_context 
    self.dialect.do_execute(
  File "C:\Users\kenny\programming\orizon-sarl\ORIZON-Feedback\venv\lib\site-packages\sqlalchemy\engine\default.py", line 590, in do_execute     
    cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type integer: "Bouche à oreille"
LINE 1: ...1'::timestamp, 'Jonathan Test', 'Léon Di Caprio', 'Bouche à ...
                                                             ^

[SQL: INSERT INTO feedback (created_at, customer, advisor, rating, plaisir, choisi, "trouvés", comments) VALUES (%(created_at)s, %(customer)s, %(advisor)s, %(rating)s, %(plaisir)s, %(choisi)s, %(trouvés)s, %(comments)s) RETURNING feedback.id]
[parameters: {'created_at': datetime.datetime(2022, 5, 3, 10, 55, 51), 'customer': 'Jonathan Test', 'advisor': 'Léon Di Caprio', 'rating': 'Bouche à oreille', 'plaisir': '6', 'choisi': 'Prix', 'trouvés': 'Service', 'comments': 'test'}]
(Background on this error at: http://sqlalche.me/e/9h9h)
127.0.0.1 - - [03/May/2022 10:56:23] "POST /submit HTTP/1.1" 500 -

And this is the database model:

d = datetime.datetime.now()

class Feedback(db.Model):
    __tablename__ = 'feedback'
    id = db.Column(db.Integer, primary_key=True)
    created_at = db.Column(db.DateTime(timezone=True), default=(d - datetime.timedelta(microseconds=d.microsecond)))
    customer = db.Column(db.String(200), unique=True)
    advisor = db.Column(db.String(200), index=True)
    rating = db.Column(db.Integer)
    plaisir = db.Column(db.String(200))
    choisi  = db.Column(db.String(200))
    trouvés = db.Column(db.String(200))
    comments = db.Column(db.Text(500))

    def __init__(self, customer, advisor, plaisir, choisi, trouvés, rating, comments):
        self.customer = customer
        self.advisor = advisor
        self.rating = rating
        self.plaisir = plaisir
        self.choisi = choisi
        self.trouvés = trouvés
        self.comments = comments

And here you have the routes:

@app.route('/')
def index():
    return render_template('index.html')


@app.route('/submit', methods=['POST'])
def submit():
    if request.method == 'POST':
        customer = request.form['customer']
        advisor = request.form['advisor']
        rating = request.form['rating']
        plaisir = request.form['plaisir']
        choisi = request.form['choisi']
        trouvés = request.form['trouvés']
        comments = request.form['comments']
        print(customer, advisor, rating, plaisir, choisi, trouvés, comments)
        if customer == '' or advisor == '':
            return render_template('index.html', message='Veuillez saisir les champs obligatoires')
        if db.session.query(Feedback).filter(Feedback.customer == customer).count() == 0:
            data = Feedback(customer, advisor, rating, plaisir, choisi, trouvés, comments)
            db.session.add(data)
            db.session.commit()
            return render_template('success.html')
        return render_template('index.html', message='Vous avez déjà soumis un commentaire')

It seems that the order of how the input is set, is wrong. But I don't get why.

Solution 1:^[1]

Your rating column is designed as integer value:

rating = db.Column(db.Integer)

but the actual value from form:

'rating': 'Bouche à oreille'

As you can see Bouche à oreille is not an integer value it is a string, not a number. I think you have some errors in your HTML form, and the submitted data is wrong.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	var211

'SQLAlchemy DataError gives me 500 Internal Server Error

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]