CODEWITHSUNDEEP
pythonpostgresqlsqlalchemypsycopg2
Hosein Yeganloo
Hosein Yeganloo

Reputation: 504

sqlalchemy.exc.DataError: (psycopg2.DataError) value too long for type character varying

I tried to save a hashed password in a PostgreSQL database using SQL Alchemy. The create table script is as follows:

Create Table "User"(
Id serial Primary key,
UserName varchar(50) unique not null,
Nickname varchar(50) not null,
"password" varchar(172) not null,
FirstName varchar(75) not null,
LastName varchar(75) not null,
BirthDate date not null,
CreateDate date not null,
Status smallint Not null
)

and here is the mapping:

user = Table('User', metadata,
         Column('id', Sequence(name='User_id_seq'), primary_key=True),
         Column('username', String(50), unique=True, nullable=False),
         Column('nickname', String(50), nullable=False),
         Column('firstname', String(75), nullable=False),
         Column('lastname', String(75), nullable=False),
         Column('password', String(172), nullable=False),
         Column('status', Integer, nullable=False),
         Column('birthdate', Date, nullable=False),
         Column('createdate', Date, nullable=False)
    )

When I try to insert data, this exception is raised:

sqlalchemy.exc.DataError: (psycopg2.DataError) value too long for type character varying(172)
[SQL: 'INSERT INTO "User" (id, username, nikname, firstname, lastname, password, status, birthdate, createdate) VALUES (nextval(\'"User_id_seq"\'), %(username)s, %(nikname)s, %(firstname)s, %(lastname)s, %(password)s, %(status)s, %(birthdate)s, %(createdate)s) RETURNING "User".id'] [parameters: {'username': '[email protected]', 'nikname': 'Laughing Death', 'firstname': 'Hosein', 'lastname': 'Yegnloo', 'password': b'i1SlFeDkCZ0BJYanhINGCZC80rqVYABHAS/Ot2AWDgzPZCtshMNRZGHeosx3PvLqsCWzZfPZpsT+UZZLShmQxfbO5VJ4xJbLNjbb0n8HuazQy+0u5Ws2DCtmdDh+HFBTKCAiNuzUGueurP9d2VE3tHwHpX+hCMS1RB4KIOUORKw=', 'status': 1, 'birthdate': datetime.datetime(1990, 3, 1, 0, 0), 'createdate': datetime.datetime(2017, 6, 23, 0, 0)}]

But as you can see, the data fits exactly into the fields and there is no error when I execute this query inside pgadmin! I think the problem is with my mapping. I changed String to Text but the error persists.

Any ideas?

I don't know if it helps, but when all characters are digits, the code works with no errors.

I tried to insert some digits instead of a hashed password and that works!

Update

I found that the problem is character encoding! Somehow SQLAlchemy increased the allowed size of a passed string! Now I'm trying to prevent it!

Upvotes: 5

Views: 9116

Answers (3)

HienPham
HienPham

Reputation: 1

string supports 255 characters, while text supports 30000 characters - string vs text. I changed data type to db.Text but I still got the same error, so I dropped the database and created it again. Then run flask db init flask db migrate flask db upgrade

Upvotes: -1

mayor
mayor

Reputation: 49

This issue arises because of postgresql and other ORM data structure definition which is strictly type and not loosely like SQL. I have identified the problems in two fold

  1. db.Column definition
  2. Posting Data by method should follow the model method args order

Where as the above may not affect SQLAlchemy db interactions migrating to other ORM requires you conform to the above.

  1. Table Definition

    __tablename__ = 'test'

id = db.Column(db.Integer, primary_key=True, autoincrement=True)
accountNumber = db.Column(db.String(255))
description = db.Column(db.String(255), nullable=False)
accountType = db.Column(db.String(255), nullable=False)

  2. Posting data by column role order

    createAccount(accountNumber, description, accountType)

Upvotes: 0

Hosein Yeganloo
Hosein Yeganloo

Reputation: 504

Problem is not about mapping or charset or any things like that in sql Alchemy! it is my code! when i try to convert hashing result to base64 string, result will be a BinaryString! not a String.

'password': b'i1SlFeDkCZ0BJYanhING....

So to solve this problem i need to decode base64 result to unicode string befor save it to database!

u.password = u.password.decode("utf-8", "ignore")

Upvotes: 4

Related Questions