Skip to content
Advertisement

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

I Try to save a hashed Password in postgresql database using SQL Alchemy. table script is:

Create Table "User"(
Id serial Primary key,
UserName varchar(50) unique not null,
Nikname 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 this 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('nikname', 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 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': 'hoseinyeganloo@gmail.com', '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 see data is exactly fit too field and there is no error when i execute this query inside pgadmin! I think problem is in my mapping. i Changed String to Text but error resists :|

Any idea?

I don’t know if it help. when all characters are digits, code work’s with no error.

I try to insert some digits instead of hashed password and it works!

Update

I found that problem is character encoding! Some how SQLAlchemy increase size of passed string! Now i’am trying to prevent it!

Advertisement

Answer

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”)

Advertisement