Incorrect string value error on inserting Unicode value alchemy

Question

I am using SQL alchemy in python to connect with the SQL database. I am inserting a value "சிறார்கள்" in the form.

i receive this value as

editedProduct = dbsession.query(Product).filter_by(id=product_id).one()
othertitle = request.form.get('othertitle') 
editedProduct.othertitle = othertitle
dbsession.commit()

I receive this error .

sqlalchemy.exc.InternalError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (pymysql.err.InternalError) (1366, "Incorrect string value: '\xE0\xAE\x9A\xE0\xAE\xBF...' for column 'othertitle' at row 1") [SQL: 'UPDATE product SET edition=%(edition)s, available_quantity=%(available_quantity)s, othertitle=%(othertitle)s, language=%(language)s, image_url=%(image_url)s, description=%(description)s, price=%(price)s, weight=%(weight)s WHERE product.id = %(product_id)s'] [parameters: {'price': '123.00', 'product_id': 1, 'weight': '1.0', 'edition': '121', 'available_quantity': '2', 'othertitle': 'சிறார்கள்', 'description': ' 9780132354615', 'image_url': '7e73c42e-82c5-47b2-aafa-d02c4b84b07f.jpg', 'language': ['English']}] (Background on this error at: http://sqlalche.me/e/2j85)

My database url is

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://{}:{}@localhost:3306/bookstore?charset=utf8'.format(USR, PWD)

also tried charset as utf8mb4.

I found this type of question there. But it encodes and decodes, it gives unnecessary overhead. MySQL "incorrect string value" error when save unicode string in Django

Could you please suggest me a solution for this. When I try with the SQLite database I didn't get this error.

Did you actually alter the database/table/column charset to utf8mb4, or just change the connection string? — SuperShoot, Apr 09 '19 at 07:20
I alter database . ALTER DATABASE bookstore CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; — Sathiyakugan Balakirushnan, Apr 09 '19 at 11:17
You need to change the table too if it existed before you changed the db. See here: https://stackoverflow.com/a/1049958/6560549 to check table and column character sets (so you can confirm if you are actually writing to utf8mb4) and this blog post: https://mathiasbynens.be/notes/mysql-utf8mb4 is a good step by step guide to transitioning. — SuperShoot, Apr 09 '19 at 11:35
Thanks not only changing the database didn't work. + Changing the all tables worked. thanks . @SuperShoot — Sathiyakugan Balakirushnan, Apr 09 '19 at 15:48

Incorrect string value error on inserting Unicode value alchemy

0 Answers0