How to fix two instances of a User being inserted into mysql table with db.session.add() for flask?

Question

I am following along in this tutorial for creating a Flask application. In the tutorial the author uses sqlite and I am using mysql+pymysql:somepassword@localhost/somedatabase for my sqlalchemy_database_uri config. I've run into a problem where my session adds two instances of a my User class to my user table after only calling db.session.add(User) once since the creation of the User table. This is a newly created user table created by using class User(db.Model).

I could go back into the table to alter it and drop the duplicate user but when I set up a unique key for the user.username or user.email i would just run into a duplicate integrity error 1062 and my Flask Debugger would shut down. So at the moment I am just trying to solve this problem with out unique usernames or unique emails. I have also tried looking up more information on this error from this website and others. I have db.drop_all() to delete my tables and then creating different class under different table names to see if maybe the error was being produced because my database was reading primary keys from mysql dump files but I dont think thats the case.

OS: Linux Mint 19.2 Cinnamon

Python : Python 3.6.8

mysql : 5.7.27-0ubuntu0.18.04.1

Flask==1.1.1

Flask-Session==0.3.1

Flask-SQLAlchemy==2.4.0

Flask-WTF==0.14.2

ERROR- START:

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.6/dist-packages/pymysql/cursors.py", 
line 170, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.6/dist-packages/pymysql/cursors.py", 
line 328, in _query
    conn.query(q)
  File "/usr/local/lib/python3.6/dist- 
packages/pymysql/connections.py", line 517, in query
    self._affected_rows = 
self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.6/dist- 
packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.6/dist- 
packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.6/dist- 
packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python3.6/dist-packages/pymysql/protocol.py", 
line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.6/dist-packages/pymysql/err.py", line 
109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.IntegrityError: (1062, "Duplicate entry 'dwdwfwdw' for 
key 'username'")

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

Traceback (most recent call last):
  File "/home/terrorbyte/Desktop/Flask_Website/flaskblog.py", line 
49, in <module>
    db.session.commit()
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/orm/scoping.py", line 162, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/orm/session.py", line 1027, in commit
    self.transaction.commit()
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/orm/session.py", line 494, in commit
    self._prepare_impl()
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/orm/session.py", line 473, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/orm/session.py", line 2459, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/orm/session.py", line 2597, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/util/compat.py", line 153, in reraise
    raise value
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/orm/session.py", line 2557, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/orm/unitofwork.py", line 589, in execute
    uow,
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
    insert,
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/orm/persistence.py", line 1138, in 
 _emit_insert_statements
statement, params
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/sql/elements.py", line 287, in 
_execute_on_connection
    return connection._execute_clauseelement(self, multiparams, 
params)
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/engine/base.py", line 1107, in 
_execute_clauseelement
    distilled_params,
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    e, statement, parameters, cursor, context
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/engine/base.py", line 1466, in 
_handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.6/dist- 
packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.6/dist-packages/pymysql/cursors.py", 
line 170, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.6/dist-packages/pymysql/cursors.py", 
line 328, in _query
    conn.query(q)
  File "/usr/local/lib/python3.6/dist- 
packages/pymysql/connections.py", line 517, in query
    self._affected_rows = 
self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.6/dist- 
packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.6/dist- 
packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.6/dist- 
packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python3.6/dist-packages/pymysql/protocol.py", 
line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.6/dist-packages/pymysql/err.py", line 
109, in raise_mysql_exception
    raise errorclass(errno, errval)
sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1062, 
"Duplicate entry 'dwdwfwdw' for key 'username'")
[SQL: INSERT INTO user (username, email, image_file, password) 
VALUES (%(username)s, %(email)s, %(image_file)s, %(password)s)]
[parameters: {'username': 'dwdwfwdw', 'email': 'gdw@gmail.com', 
'image_file': 'default.jpg', 'password': 'somepassword'}]

ERROR - END

Code Snippet:

from datetime import datetime
from flask import Flask, render_template, url_for, flash, redirect
from flask_sqlalchemy import SQLAlchemy
from forms import RegistrationForm, LoginForm
app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 
'mysql+pymysql://root:somepassword@localhost/somedatabase'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), nullable=False)
    email = db.Column(db.String(100), nullable=False)
    image_file = db.Column(db.String(50), nullable=False, 
default='default.jpg')
    password = db.Column(db.String(60), nullable=False)
    #posts  = db.relationship('Post', backref='author', lazy=True)

    def __init__(self, username, email, image_file, password):
        self.username = username
        self.email = email
        self.image_file = image_file
        self.password  = password

    def __repr__(self):
        return f"User('{self.username}', '{self.email}', 
'{self.image_file}')"

#db.drop_all()
#will create a completely new schema for user table in somedatabase
db.create_all()
#at the moment the table user is empty

user_1 = User(username='dwdwfwdw', email='gdw@gmail.com', 
image_file='default.jpg', password='Sdwfgvermfeghahdwdwf')
db.session.add(user_1)
db.session.commit()

Expected Results in mysql > somedatabase > table user;

Actual Results:

if i try user_1 = User.query.filter_by(username='dwdwfwdw').first() db.sesstion.delete(user_1) db.session.commit() it deletes both instances from the user table — Justin Coleman, Aug 27 '19 at 22:26
flask has a good error handling to catch such errors like unique keys https://stackoverflow.com/questions/52075642/how-to-handle-unique-data-in-sqlalchemy-flask-pyhon — nbk, Aug 27 '19 at 23:01
@nbk the problem isnt that im trying to catch the input of unique keys. The problem is that somehow db.session.add(user) is inputing two of the same instance at the same time. — Justin Coleman, Aug 27 '19 at 23:16
Solved the problem by running the app outside of debug mode. — Justin Coleman, Aug 28 '19 at 00:19
or you can do it inside debug mode but you have to set use_reloader=False within your app.run() call — Justin Coleman, Aug 28 '19 at 00:49
See https://stackoverflow.com/questions/25504149/why-does-running-the-flask-dev-server-run-itself-twice — Justin Coleman, Aug 28 '19 at 00:59

How to fix two instances of a User being inserted into mysql table with db.session.add() for flask?

0 Answers0