sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type integer: "B1"

Question

I am tryng to login the admin on my flask app using ID, when i signup it is taking inputs perfectly, but while getting values from database(postgresql) it showing "DataError" I have taken input as alphanumeric in HTML form

here are my code:

in model.py i have written code for databese to make columns

model.py

class Admin(UserMixin, db.Model):
    id = db.Column(db.String(1000), primary_key=True )
    name = db.Column(db.String(1000))
    email = db.Column(db.String(100), unique=True)
    password = db.Column(db.String(100))
    timestamp = db.Column(db.DateTime)

in this auth.py I writen code for POST methods for signup and login

auth.py

@auth.route('/admin')
def admin():
    return render_template('admin.html')


@auth.route('/admin', methods=['POST'])
def admin_post():
    id = request.form.get('id')
    password = request.form.get('password')
    remember = True if request.form.get('remember') else False

admins = Admin.query.filter_by(id=id).first()

# check if the user actually exists
# take the user-supplied password, hash it, and compare it to the hashed password in the database
if not admins or not check_password_hash(admins.password, password):
    flash('Please check your login details and try again.')
    # if the user doesn't exist or password is wrong, reload the page
    return redirect(url_for('auth.admin'))

# if the above check passes, then we know the user has the right credentials
login_user(admins, remember=remember)
return redirect(url_for('main.adminOP'))


 @auth.route('/admin-signup')
 def adminsignup():
     return render_template('admin-signup.html')


 @auth.route('/admin-signup', methods=['POST'])
 def adminsignup_post():
     id = request.form.get('id')
     email = request.form.get('email')
     name = request.form.get('name')
     password = request.form.get('password')
     timestamp = datetime.now()

# if this returns a admin, then the email already exists in database
admins = Admin.query.filter_by(email=email).first()

if admins:  # if a user is found, we want to redirect back to signup page so user can try again
    flash('Email address already exists')
    return redirect(url_for('auth.adminsignup'))

# create a new user with the form data. Hash the password so the plaintext version isn't saved.
new_admin = Admin(id=id, email=email, name=name,
                  password=generate_password_hash(password, method='sha256'), timestamp=timestamp)

# add the new user to the database
db.session.add(new_admin)
db.session.commit()

return redirect(url_for('auth.admin'))

The error caught in trace back is:

sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type 
integer: "B1"
LINE 3: WHERE person.id = 'B1'
                          ^

[SQL: SELECT person.id AS person_id, person.email AS person_email, person.password AS person_password, 
person.name AS person_name, person.timestamp AS person_timestamp
FROM person
WHERE person.id = %(pk_1)s]
[parameters: {'pk_1': 'B1'}]
(Background on this error at: http://sqlalche.me/e/14/9h9h)

Here B1 is id

admin-signup.html

As I said earlier I have used alhapanumeric in input field

{% extends "base.html" %}

{% block content %}

    Sign Up
    
        {% with messages = get_flashed_messages() %}
        {% if messages %}
        
            {{ messages[0] }}. Go to login page.
        
        {% endif %}
        {% endwith %}
        
            
                
                    
                        🆔
                    
                     Remember: ID is you employee number 
                
            
            
                
                    
                        📧
                    
                
            

            
                
                    
                        👱
                    
                
            

            
                
                    
                        🔑
                    
                
            

            
        
    

{% endblock %}

admin.html

{% extends "base.html" %}

{% block content %}

    Admin Login
    
        {% with messages = get_flashed_messages() %}
        {% if messages %}
        
            {{ messages[0] }}
        
        {% endif %}
        {% endwith %}
        
            
                
                    
                        🆔
                    
                     HINT: ID is you employee number 
                
            

            
                
                    
                        🔑
                    
                
            
            
                
                    
                    Remember me
                
            
            
        
    

{% endblock %}

After editing code i got this same error caught in debugging

this is person model

class person(UserMixin, db.Model):
    # primary keys are required by SQLAlchemy
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(100), unique=True)
    password = db.Column(db.String(100))
    name = db.Column(db.String(1000))
    timestamp = db.Column(db.DateTime)

Magnun Leno · Accepted Answer

The issue is that the column id from your person model is an Integer, not a string. Your load_user function queries the person model providing a key B1, which can not be converted to Integer.

If you intend to query your Admin model and also query your person model depending on who is logged, you should add an identifier in your user session during login and retrieve it in the load_user function. Check out this question for more details.

But if you need to treat your users differently (User#1 is an admin but User#2 is not), you should really consider using a boolean flag in your model, something like this:

class User(UserMixin, db.Model):
    id = db.Column(db.String(1000), primary_key=True )
    name = db.Column(db.String(1000))
    # ...
    is_admin = db.Column(db.Boolean)

You should really avoid having two user's models with different types of keys, it can get really messy over time.

If your Flask app tend to grow and have multiple kind of users, you should consider adopt the concept of 'user roles'. Check out Flask-Principal for this.

Old Answer

The issue is probably because your model and your database aren't synchronized. In some point the development processes you probably had id = db.Column(db.Integer, primary_key=True) then you ran db.create_all() and then altered the id to id = db.Column(db.String(1000), primary_key=True).

In simple words, Flask believes that id is a string, but PostgreSQL is sure that it is an int. Just to be sure, you could check the DDL of the table person with:

pg_dump -U your_user your_database -t person --schema-only

Note that flask/flask-sqlalchemy does not do automatic migrations, after changing your model you should recreate the database or apply the changes manually (the choice is up to you).

sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type integer: "B1"

Answers (1)

Old Answer

Related Questions

sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type integer: &quot;B1&quot;

Answers (1)

Old Answer

Related Questions

sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type integer: "B1"