CODEWITHSUNDEEP
pythonpostgresqlflasksqlalchemyflask-sqlalchemy
kirillwolkow
kirillwolkow

Reputation: 13

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.

Upvotes: 0

Views: 1101

Answers (1)

var211
var211

Reputation: 606

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.

Upvotes: 1

Related Questions